Basis challenge #1: Reporting logon information

Learn Basis best practices and how to overcome common administrator problems in this new checklist from SAP Basis guru Giovanni Davila.

Report all SAP users and their logon information (last logon date, account status and incorrect logon attempts,

if any) from all SAP systems in all system landscapes.

A report per system is required, as this will be used by management to prepare for an audit.

Environment:
 

  • One R/3 landscape with three servers: DEV, QAS and PRD.
  • One BW landscape with two servers: BDV and BPR.
  • One CRM landscape with two servers: CDV and CPR.
  • Total # of central instances: 7
  • No Central User Administration implemented.
  • OS: Windows 2000 and Windows Server 2003
  • RDBMS: SQL Server 2000

Analysis/solution
Table USR02 stores the logon data. In it, we can see what users are locked (field UFLAG = '64' or '128'), their last logon date (field TRDAT) and if they have any bad logon attempts (field LOCNT).

Transaction SUIM (User Information System) provide reports with the information we need. With that said, we have these two scenarios:

 

Worst-case scenario Best-case scenario
Manually log in to each system and execute one of the following: Run a script that reaches inside each SAP database and extracts the user list. To achieve that we need to:
  • Tcode SUIM -> User Information System -> User -> With Unsuccessful Logons
1. Create a SQL script that can produce the output we need by querying database table USR02. This script will be later incorporated in a batch file.
  • Tcode SE16 and query table USR02.
2. Create a text file with the SAP server names, SID database names and database owners.
  • Run ABAP report RSUSR006 via SA38.
3. Create a batch file that loops through the text file entries and runs the appropriate SQL statements to produce reports.
Each report needs to be [manually] saved into a file.

Let's remember that these reports will go to management for reviewing. So, what does that tell us? That they will come back and request updated reports after making changes in all the systems. I don't think we want to log back into each server over and over again just for this.

Here, we will work on the best-case scenario and even throw some additional information into the reports: the number of days since the last logon.

See the presentation below.


1. SQL Script
The following SQL script queries table USR02 and it lists all clients, user IDs, last logon date, days since last logon, account status and bad logon counts. That means that we are interested in fields MANDT, BNAME, TRDAT, UFLAG and LOCNT.

Notes about fields in table USR02
 

  • Field TRDAT contains the last logon date in format YYYYMMDD. But, we will convert it to MM/DD/YYYY. Also, if the user has never logged in TRDAT will display a value of "00000000" but we will translate it to "-Never-".
  • We will calculate the number of days since the last logon by subtracting the current date from the last logon date. Since TRDAT is a character (varchar) field, we will convert it to "datetime" first to obtain the day difference. Then, we will convert it back to character. If the user has never logged in we will indicate that with a "-".
  • Field UFLAG has two special values to indicate the status of an account. Value "64" indicates that the user has been locked by the administrator. Value "128" indicates that the user got locked out due to many incorrect logon attempts. We will translate the values into more meaningful messages.
  • Field LOCNT keeps count of incorrect (bad) logons. Notice that a value of 0 is set when there has not been bad logons. However, we will display a value only when there have been bad logons. This field is numeric (tinyint) but we will convert it to character to handle the display format.

SQL code:
 

set nocount on 
select MANDT as [Client], BNAME as [User ID], 
case TRDAT 
    when '00000000' then '-Never-' 
    else substring(TRDAT,5,2) + '/' + substring(TRDAT,7,2)
+ '/' + substring(TRDAT,1,4) 
end as [Last Logon], 
case 
    when TRDAT <> '00000000' then convert(varchar(5), 
    datediff(day,convert(datetime,TRDAT), getdate())) 
    else '-' 
  end as [Days], 
case UFLAG 
    when '0'  then '' 
    when '64' then 'Locked by Admin' 
    when '128' then 'Many failed logons' 
end [Status], 
case convert(varchar(3), LOCNT) 
    when '0' then '' 
    else convert(varchar(2), LOCNT) 
end as [Bad Logons] 
from USR02 
order by BNAME, MANDT 
go

2. Text (input) file
We need a list of all the SAP database servers with their respective SID name and database owner. This information needs to be put in a text (or flat) file that we can later read one entry at a time. For demonstration purposes, I have called the file "sapsystems.txt".

We can simply separate each of the three values with a blank space. Notepad or any other text editor can be used to create the file. See example below:

Notes
 

  • The third value (database owner) in each entry plays an important role because in new R/3 releases (e.g. R/3 Enterprise) the database owner is named after the name.
  • Also, the SAP database name and the database owner are case sensitive! ("DEV" is not the same as "dev"). SID Database names are always in uppercase and database owner names in lower case.

3. Batch File
The batch file reads the text file (sapsystems.txt) containing all the SAP database servers, the database names and the database owners. It goes through each server entry and it generates a temporary SQL script based on the statements we discussed earlier. It runs the SQL script using the OSQL command-based utility (part of the SQL Server Management tools), which produces a report per server.

To provide a nicer output we will run OSQL with parameter "-S |" to separate the columns with the pipeline symbol "|".

See below:

Notes
 

  • The text file name is assigned to variable "infile". See below: set infile=sapsystems.txt
  • Each column in the text file is assigned to a variable. We read three columns and assign them to variables %%a, %%b and %%c. See below: for /f "tokens=1-3" %%a in (%infile%) do ( ....... )
  • Temporary SQL scripts will be created for each different server since the table owner may differ from system to system. In R/3 releases < = 4.6 the database owner is "dbo". But, in upgraded systems or systems based on Web AS 6.x the database owner is named after the name. The temporary SQL scripts will be named after the batch filename. See below:
    set SQLcmd=%~n0.sql
  • Since the SQL statements are within a batch file we need to use the "^" symbol before special characters such as "(" or "<". This is why we converted: datediff(day,convert(datetime,TRDAT), getdate())) into: datediff^(day,convert^(datetime,TRDAT^), getdate^(^)^)^)
  • We run OSQL.exe as follows:
    osql -S %%a -d %%b -b -n -E -s "|" -i %SQLcmd% -w 120 -o %%a.txt
    
    This is what the parameters mean:
    -S %%a SQL Server name, %%a = server name
    -d %%b Database name, %%b = SID database name
    -b  Abort if any errors in SQL statements
    -n  Remove numbering
    -E  Use trusted connection (otherwise supply –U user –P password)
    -s "|"  Use | as column separator
    -i %SQLcmd% Input SQL script
    -w 120  Change column width to 120 characters
    -o %%a.txt Output filename
    

Keep in mind that OSQL parameters are case sensitive.

 


Overcoming Basis challenges by reaching inside the database

 Home: Introduction
  Basis challenge #1: Reporting logon information
 Basis challenge #2: Gathering SQL Server information
 Basis challenge #3: Getting system profile parameters
  Troubleshooting
 Review: Conclusion and more resources
This was first published in December 2005

Dig deeper on SAP Basis administration and NetWeaver administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchManufacturingERP

SearchOracle

SearchDataManagement

SearchAWS

SearchBusinessAnalytics

SearchCRM

SearchContentManagement

SearchFinancialApplications

Close