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.
- 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
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:|
||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.|
||2. Create a text file with the SAP server names, SID database names and database owners.|
||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.
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:
- 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
- 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 "|".
- 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:
- 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
Basis challenge #1: Reporting logon information
Basis challenge #2: Gathering SQL Server information
Basis challenge #3: Getting system profile parameters
Review: Conclusion and more resources