Tracking Login Information in SAP

DB Table Creation:
USE [PRD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ZLOGONINFO](
[MANDT] [varchar](3) NOT NULL,
[USER_ID] [varchar](12) NOT NULL,
[COMPUTER_NAME] [varchar](32) NOT NULL,
[USER_OS] [varchar](10) NULL,
[IP_ADDR] [varchar](15) NULL,
[GUI] [varchar](4) NULL,
[GUI_PATCH] [varchar](30) NULL,
[DUP_FLG] [varchar](1) NULL,
[LAST_LOGON_DT] [varchar](8) NULL,
[LAST_LOGON_TM] [varchar](6) NULL,
CONSTRAINT [ZLOGONINFO~0] PRIMARY KEY CLUSTERED
(
[MANDT] ASC,
[USER_ID] ASC,
[COMPUTER_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Report in SAP:

REPORT zlogoninfo MESSAGE-ID z1 LINE-SIZE 132 NO STANDARD PAGE HEADING
LINE-COUNT 65.
*--------------------------- T A B L E S ------------------------------*
TABLES: zlogoninfo.

*-----------------I N T E R N A L T A B L E S -----------------------*
DATA: BEGIN OF i_zlogoninfo OCCURS 0.
INCLUDE STRUCTURE zlogoninfo.
DATA: END OF i_zlogoninfo.

*-------------T E M P O R A R Y V A R I A B L E S--------------------*
DATA: highlight TYPE i. "used for formatting-global var.
*
*--------------- S E L E C T I O N S C R E E N----------------------*
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_user FOR zlogoninfo-user_id,
s_date FOR zlogoninfo-last_logon_dt.

SELECTION-SCREEN END OF BLOCK blk1.

INCLUDE: zsetinty. "Highlight print lines
*------ S T A R T - O F - S E L E C T I O N ---------------------------*
START-OF-SELECTION.

PERFORM get_data.

*------- E N D - O F - S E L E C T I O N ------------------------------*
END-OF-SELECTION.
PERFORM print_report.

TOP-OF-PAGE.
PERFORM head_lines.

*&---------------------------------------------------------------------*
*& Form HEAD_LINES
*&---------------------------------------------------------------------*
* Display header lines
*----------------------------------------------------------------------*
FORM head_lines.
PERFORM print_report_header.
PERFORM page_heading.
ENDFORM. " HEAD_LINES

*&---------------------------------------------------------------------*
*& Form PRINT_REPORT_HEADER
*&---------------------------------------------------------------------*
FORM print_report_header.
CALL FUNCTION 'Z_REPORT_HEADER'
EXPORTING
i_title = 'USER SAPGUI PATCH LEVEL'
EXCEPTIONS
OTHERS = 1.
ENDFORM. " PRINT_REPORT_HEADER

*&---------------------------------------------------------------------*
*& Form PAGE_HEADING
*&---------------------------------------------------------------------*
FORM page_heading.
FORMAT COLOR COL_HEADING INTENSIFIED ON.

WRITE:/ sy-vline NO-GAP,
(12) 'User Name' NO-GAP, sy-vline NO-GAP,
(32) 'Computer Name' NO-GAP, sy-vline NO-GAP,
(10) 'User OS' NO-GAP, sy-vline NO-GAP,
(15) 'IP Address' NO-GAP, sy-vline NO-GAP,
(04) 'GUI' NO-GAP, sy-vline NO-GAP,
(30) 'GUI PAtch' NO-GAP, sy-vline NO-GAP,
(01) ' ' NO-GAP, sy-vline NO-GAP,
(10) 'Last Date' NO-GAP, sy-vline NO-GAP,
(08) 'Last Time' NO-GAP, sy-vline NO-GAP.
ENDFORM. " PAGE_HEADING
*&---------------------------------------------------------------------*
*& Form Get_data
*&---------------------------------------------------------------------*
FORM get_data.
SELECT *
INTO TABLE i_zlogoninfo
FROM zlogoninfo
WHERE user_id IN s_user
AND last_logon_dt IN s_date.
IF sy-subrc = 0.
SORT i_zlogoninfo.
ENDIF.
ENDFORM. " Get_data
*&---------------------------------------------------------------------*
*& Form print_report
*&---------------------------------------------------------------------*
FORM print_report.
FORMAT COLOR COL_NORMAL.
LOOP AT i_zlogoninfo.
PERFORM set_intensity USING highlight.
WRITE:/ sy-vline NO-GAP,
(12) i_zlogoninfo-user_id NO-GAP, sy-vline NO-GAP,
(32) i_zlogoninfo-computer_name NO-GAP, sy-vline NO-GAP,
(10) i_zlogoninfo-user_os NO-GAP, sy-vline NO-GAP,
(15) i_zlogoninfo-ip_addr NO-GAP, sy-vline NO-GAP,
(04) i_zlogoninfo-gui NO-GAP, sy-vline NO-GAP,
(30) i_zlogoninfo-gui_patch NO-GAP, sy-vline NO-GAP,
(01) i_zlogoninfo-dup_flg NO-GAP, sy-vline NO-GAP,
(10) i_zlogoninfo-last_logon_dt NO-GAP, sy-vline NO-GAP,
(08) i_zlogoninfo-last_logon_tm NO-GAP, sy-vline NO-GAP.
ENDLOOP.
ULINE.ENDFORM. " print_report

Recreating Transport Data and Cofiles

If you no longer have the Data and Cofiles for Transports that still exist in your SAP system, there is a method to export those files back to the Transport Directory on the Transport Host.
This is done using the TP tools on the OS:

tp export

example:
tp export DEVK901234

Capturing SQL Agent Job Information

The following query will pull selected data from the sp_help_job stored procedure and put it into the requested table:

USE msdb

IF OBJECT_ID('tempdb..#TmpJobs') IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET('sqloledb'
, 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')

SELECT originating_server, name, enabled, owner, last_run_date, last_run_outcome, next_run_date
INTO Database..Table
FROM #TmpJobs

This comes in very handy if you want to use a DTS package to grab this data from multiple servers and populate one table that you can query. I am using this in correlation with a truncate command for the table so that a select * will get me only the current data, since historical information is already kept in each server's individual msdb database.

Note: In SQL2005 you will have to have the sp_configure parameter Ad Hoc Distributed Queries enabled.

Find Failed Jobs in MSDB Job Logs

This will get you a short list of the jobs that have failed and are still in sysjobhistory:

select a.server, b.name, a.message, a.run_date
from sysjobs b JOIN sysjobhistory a
on a.step_id = 0
and a.run_status = 0
order by b.name

You can run this query from a remote server via a DTS package against many servers to save some point-and-click time. Have all the data compiled into one monitoring database and you have a quick list you can scan through rather than touching every server in yoru network.

Insert Data Into One Table If Criteria Matches Data in a Different Table

I know this sounds very simplistic, but you'd be surprised how many people simply have no clue how to do this:

You want to update Column1 in TableA to match Column1 in TableB as long as Column2 in TableA matches Column2 in TableB:

Here's how the query should look:

UPDATE TableA
SET Column1 = B.Column1
FROM TableA A
INNER JOIN TableB B
ON A.Column2 = B.Column2

SQL Error 945 While Creating New Database

I ran across a problem creating a new database on a SQL Server that had recently been through a crash and rebuild. It had been running fine for months, but now whenever you tried to create a new database it would throw a SQL 945 error, referring to space or memory allocation issues and then referencing the data or log file of an unrelated database that had been previously deleted.
After some troubleshooting I came to the conclusion that there must be some orphaned data in the Master database. I first checked sysdatabases, and everything looked fine, 23 databases all in a row. Next I looked at sysaltfiles, and lo and behold there were entries for dbid 24, which did not exist. Since it was the next available dbid in sysdatabases, SQL Server was attempting to create the new database as dbid 24, but the data in sysaltfiles did not jive with it. Long story short, I removed the entries for dbid 24 in sysaltfiles and the problem was fixed.

Adding Leading Zero(s) to Field Entries

Adding Leading Zero(s) to field entries in a SQL table may be something you have to do at some point. Why? Beats me, I just know it has come up more than once for me, and the "customer is always right."

This simple query will update the data in TABLE1, field ORDER to include leading zeros for up to 10 total digits. ORDER is varchar(10)

UPDATE TABLE1
SET ORDER = right('0000000000' + ORDER, 10)

It's almost obscene how simple this is, but you will have to make sure that the queries used to add data to this table are updated to include the leading zeros in the INSERT, otherwise you will have to continuously add leading zeros to this field moving forward.