"We need this information for each system"
With an increasing system landscape you might loose the details for each system: is the system still in use, is someone really working on it? Do we really still need this system or could it be closed to save capacity / effort / money?
Recently this question came up at an SAP customer, who has a quite huge SAP system landscape. The amount of test systems raised up to a number of ~300. Wow, that's really a lot! There are quality systems, sandboxes, project specific systems, systems built up as temporary replacements of others etc. etc. Many of them with more than one client (beside 000), so the number of clients affected is therefore much higher, maybe ~500.
Of course the operation of this huge landscape consumes a lot of hardware, time, people, effort... and consequently money. So the simple demand was: let's try to reduce the amount of systems and check whether the systems are still in use. We are sure, that there are some that can be closed!
Easily said, sounds not like a challenging task. Having a look into the last login dates would give the information whether the system is in use. The user information system in SAP (transaction SUIM) offers this information. But doing this for ~500 clients? This makes this supposedly simple task quite time-consuming and expensive. Delegating this task to a single person is not conducive to making that person happy .
Apart from just executing a SUIM report it would cause additional effort to enable the person to do this job - it is unlikely that the user and the authorizations are given in all systems and clients. So, it will become necessary to request a user and suitable authorizations on lots of systems. Possibly the whole task would take a month or more to be completed.
Let's think about another approach: forward the request for information to the system responsibles. Create a small manual what has to be done, determine the system responsibles and ask them to complete the task and return the result. There is a list of system responsibles, so the amount of people to be bothered with it is known. Finally ~60 people would be harassed. Hmm, some are on holiday or ill, others have to ask colleagues or project leaders, some would have questions, ... boah, also this approach would take time and causes a lot of effort.
Perhaps a more central approach after all: can we somehow schedule the background execution of the SUIM report in all systems? There is a job scheduling system in use, there is also a team responsible for this, ...but scheduling the job in ~500 clients and managing the result of the report seems to save neither time nor effort compared to the other solutions.
And how about the program to be scheduled - or executed manually, in case the scheduling option is dropped out?
There is a report in SUIM with a title promising to offer exactly what we need.
Transaction SUIM, report "User by Logon Data and Password Change" (RSUSR200)
Ok, this program offers a list of the users with their last login date. We can reduce the selection to user type "Dialog". However, this program is designed to get the people that have not logged in for a long time. For our purpose it would be better to have a list of users that have recently logged in, for example within the last 90 days, and containing only these users and not the others haven't logged in for a long time. Too bad, this is not possible with this program. Of course we could use the program, but finally we would get a list of all dialog users with their login date. We would have to execute this program for all ~500 clients and then analyze each and every list. Some of the clients have huge amounts of users (more than 40000), so some of the lists would be quite big.
Output of program RSUSR200
To summarize: possible, but not an ideal approach for our purpose. Finally there is no program available working cross-client, and all result lists of all users with their login dates would end in big lists. It would be better to get a result fitting more to our needs. Thus, let's think about an own developed, new program. In fact, our requirement is quite simple and can be met with a small ~60 line ABAP program.
*&---------------------------------------------------------------------*
*& Report z_get_last_login_dates *
*&---------------------------------------------------------------------*
*& Get some information about last login dates of users, cross-client. *
*&---------------------------------------------------------------------*
report z_get_last_login_dates no standard page heading line-size 1023.
parameters: pDays type i default 90.
types: begin of TLastLoginData,
client type mandt,
lastLogin type usr02-trdat,
userIds type string,
end of TLastLoginData.
data: lastLoginDataTab type standard table of TLastLoginData,
lastLoginData type TLastLoginData,
userId type usr02-bname.
start-of-selection.
" Get last login dates of each client (except 000),
" excluding DDIC and myself.
select mandt as Client, max( trdat ) as LastLogin from USR02
client specified
where mandt <> '000' and ustyp = 'A'
and bname <> 'DDIC' and bname <> @sy-uname
group by mandt order by mandt
into table @data(loginDates).
" Get users which have logged in on that date.
loop at loginDates assigning field-symbol(<login>).
clear lastLoginData.
lastLoginData-client = <login>-client.
lastLoginData-lastLogin = <login>-lastlogin.
select bname from usr02
client specified
where mandt = @<login>-client
and ustyp = 'A'
and trdat = @<login>-lastLogin
into table @data(userIds).
loop at userIds into userId.
if ( sy-tabix = 1 ).
lastLoginData-userIds = userId.
else.
concatenate lastLoginData-userIds ',' userId
into lastLoginData-userIds.
endif.
endloop.
append lastLoginData to lastLoginDataTab.
endloop.
" Now print the result.
write:/ 'SID', 'Client', 'Last login', 'In use?', 'User Ids'.
data: inUseLimit type syst-datum.
inUseLimit = sy-datum - pDays.
data: inUse type char1.
loop at lastLoginDataTab into lastLoginData.
clear inUse.
if ( lastLoginData-lastLogin >= inUseLimit ).
inUse = 'X'.
endif.
write:/ sy-sysid under 'SID',
lastLoginData-client under 'Client',
lastLoginData-lastLogin under 'Last login',
inUse under 'In use?',
lastLoginData-userIds under 'User Ids'.
endloop.
delete report sy-repid.
It determines the last login dates of all dialog users (except DDIC and the user executing the program) in each client of the system, gets the user ID's that have logged in on that date and check whether the usage was within the last x (default: 90) days. Notice the last line: after execution the program deletes itself.
Ok, works. Due to the fact that the program works cross-client, we can get our information with a single execution of the program in any client of the system. Now the question is how to get this program into any system.
The ~300 systems, that have to be analyzed, are in lots of different transport domains. We can create a transport request for our small program, but again it seems that a lot of people have to be contacted to import the transport request into their systems. Data and cofile are to be copied into the transport directory, the transport request has to be added into the importqueue and finally the import has to be done. Sounds again like a task bringing a lot of effort with it and needs a lot of time.
Tired of reading why all these approaches are slow, costly and overall annoying?
With "Shortcut for SAP systems" you can perform queries across systems, quickly, very flexibly, on short notice and completely on your own, without bothering many people.
Take a look at the function "Insert / modify program".
Function "Insert / modify program"
Having the source code of a program in a text file on your computer, you can insert it and execute it directly after it has been inserted. In combination with the command line tool, you can run a script that goes through all systems and gets a single, final list of required information!
After inserting and immediately executing a program also the output of the executed program is available.
We can see that the system S22 is in use, but there are 2 clients 200 and 300 which are obviously not in use anymore. So, despite we have to keep the system, it is likely that these 2 clients can be deleted, which would save us database space.
The insertion of a program works independently of client settings or the system changeability. We can use it in every system and do not have to take preparations in the SAP systems. What I have to do for this is to create a variant (meaning a variant in "Shortcut for SAP systems", not in the SAP systems) for each system and create a single script, combining the calls of the command line tool for each variant. Having a look at the previous screenshot you will notice that program name and title are not specific for the current request (of getting last login dates of the users). Instead I used a generally applicable name and title. This allows us to use the variants and script for other requirements for cross-system queries.
For example:
- Our colleague Laurel Leaving will be moving to one of our competitors. We need to know on which SAP systems he has a user master record and authorizations that we need to delete on short notice. (ABAP program)
- We will change one of our central print servers and need to know on which systems there is an RFC connection to it that has to be adjusted. (ABAP program)
- We have received a request from the antitrust authorities for information on all business transactions with our supplier Otis. In which systems do we manage this supplier and have business data with him?
For each of these requests, a small ABAP program could provide the desired information. If a suitable SAP standard program already exists, the following procedure can be applied to the "Execute a program" function in the same way.
Let's proceed with the creation of the variants. For each system we save a variant - quickly done by changing the connection, click on the "Save variant..." button, clicking on one of the already created variants (which puts the name and description into the input fields) and just adjusting the SID in the name of the variant.
Now let's move to the command line tool. For executing the function with the command line tool and writing the result into a file, it has to be called like this:
sc4sapcmd insertprogram <variant> <output file>
Using this command
sc4sapcmd insertprogram -listvariants > c:\temp\insertpgmvariants.txt
we can get a list of all variants, written into file "C:\temp\insertpgmvariants.txt".
With this file in a text editor, we can easily create a single command file that calls the command line tool for all variants.
For each insertion / execution the output will be stored in a separate file. To get one big list with all the results, we add a simple copy command at the end:
Now let's execute the script:
...
Tataaa! That's it - an ad hoc query with the desired information across all systems, in a single list:
Edit: With version >= 1.2.1 of the command line tool there is the possibility to use XML files so that a separate Variant does not have to be created for each system. Just create one Variant only and name it "Cross system query". In the XML file (let's name it "Cross system query.xml") place a task for each system and override the connection and the output file from the Variant:
<ExecutionChain>
<Comment>Cross system query: get users with last login in the last 90 days
in every client in the sandbox systems to find systems that can be
(possibly) deleted.</Comment>
<Task>
<InsertProgram StopOnError="false">
<Variant>Cross system query</Variant>
<Connection>S22/100, SYS_BC_01</Connection>
<OutputFile>C:\temp\Query S22.txt</OutputFile>
</InsertProgram>
</Task>
<Task>
<InsertProgram StopOnError="false">
<Variant>Cross system query</Variant>
<Connection>S23/100, SYS_BC_01</Connection>
<OutputFile>C:\temp\Query S23.txt</OutputFile>
</InsertProgram>
</Task>
<!-- and repeat the <Task> for each system ... -->
</ExecutionChain>
Then call the command line tool with the XML file as argument:
c:\Programs\Sc4SAP\Sc4SAPCmd.exe "C:\Programs\Sc4SAP\scripts\Cross system query.xml"
Offering the whole thing in an Excel file is a piece of cake. I could do it alone, not have to bother many colleagues or delegate the task to someone else (who would then be an enemy for life if he had to do it manually for each system / client).
Do we need an update of the information after 2 weeks? No problem, this would not create any effort at all. Doing the same with using 180 days in the past as the limit for the last login? Also no problem, just replace the '90' by '180' in the text file containing the ABAP program and start the script again. If another question across systems comes up, I can think about another small ABAP program and still use the same script and the same variants. By having the script available at the OS level, I can even schedule it to run regularly. What a saving of capacity, time, ultimately money, and what a gain in speed to achieve the desired result!
Es gibt noch keine Rezension.