Here is how to login to Oracle Staging server internally, connection string see below

You can also login externally, from internet, to Oracle EM: https://fandezhi.com:5500/em

When we compare the audit log file sample versus the tcode SM20 output from SAP, it would be possible to figure out the text strings

Audit log file sample

Audit log review from SAP tcode SM20

A more detailed analysis on the audit log format

First of all we take 3 records for example:

2AU120161009194142000203600005D5ThinkPadZJU         SESSION_MANAGER     SAPMSYST                                1001A&0&P                                                           ThinkPad            
2AUW20161009203525000203600005D5T430s   TOMMY       SE38                RSABAPPROGRAM                           1003RSABAPPROGRAM&                                                  T430s               
2DU920161009183657000511600002D2Adam-W54ALI         SE16                SAPLSETB                                1001USR02&02&passed                                                 Adam-W540

1. Each record has 200 characters
2. [1-1] -- Seems always "2"
3. [2-4] -- type of activities, for example, AU1 means login activities, AUW means start a report, AU3 means start a transaction
4. [5-12] -- YYMMDD, for example, 20161009, means year 2016, month 10, date 09
5. [13-18] -- HHMMSS, for example, 203525, means 20:35:25
6. [19-25] -- OS process number, for example, 0005116, means the OS process is 5116, which can be seen in SM50 as well
7. [26-28] -- Seems always be "000", I think it's reversed.
8. [29-30] -- Work process number. which can be seen in SM50
9. [31-31] -- Type of work process. For example, D for DIA, B for BTC
10. [32-32] -- Work process number(again?). There is a trick: 1~9, then a-b-c, meanshi 10-11-12. I think this was a design limitation from SAP. Since in early version of SAP, they think the servers shall not have more than 36 work processes? So later when they realize that some servers have 50+ work processes, and this 1 character cannot fullfill the requirement so that they have to use [29-30] to replace this [32]
11. [33-40] -- Terminal name. However it could be trimmed so these 8 char seem to be useless
12. [41-52] -- SAP User ID. This is extremely important
13. [53-72] -- Tcode
14. [73-112] -- Program name
15. [113-115] -- SAP Client number, such as 100
16. [116-116] -- value "1", or "2", or "3". Not sure what this is, but seems it shows how many times the tcodes or programs been used
17. [117-180] -- Login results or tcode/transactions result. For example, "B&0&P" means Batch login with Password was Successful. "A&1&P" means Dialog login with Password was Failed. The "&" comes with the tcode or transactions means "Started and that's it"
18. [181-200] -- Terminal information. Such as laptop or workstation names

So here I have studied for a few hours for the open source ETL tool Talend for these fixed size file. Eventually I've been able to load this file into Oracle database.

Below is some screens for the Talend ETL tool. I have been facing some challenges such as the return-line is LRLF which actually is "\r". The normal LF is "\n". Also some of the alignment issues within the file. Overall Talend is an easy tool to use which enable someone like myself can load the file easily

Records have been loaded into Oracle staging server

After loading about 380,000 audit records, we realized that we were facing the following issues:

1. The audit files that we retrieved from an older IDES system which contains a lot of batch sessions and no values for geo graphic analysis

2. The data masking for those real audit logs, is very time consuming and no open source ETL tool will provide data masking / data scrambling features

So that we're thinking of using some sort of QA automation tools to produce some 'real' data. This strategy contains 2 steps

1. Create 1000 users, with address information

2. Group these 1000 users into 10 category, each group will run ramdomly a list of transactions. For example, HR group users will mostly be running transactions with 'PAxx', Purchasing group users run 'MExx', 'MKxx', Basis users run 'SMxx, STxx'

To provide a template for automation tools to create users, we have to provide a template telling the tool 'how to create new users'

Go to link http://fandezhi.com:8083/sap/bc/gui/sap/its/webgui?sap-client=800&sap-language=EN

Reporting requirements