Need suggestions to get the login and logout data in HANA server

[former_member598790]

Hi All,

I have the requirements to display a graph with HANA server which have the login/logout info based on daily login (i.e. in hour and minute) and weekly basis(i.e. day and hour) but I have gone through tables usr02, usho2 which have logged in time and date but not the previous day login details and gone through sm04, al08 these have details of only few recent activities of users. and also checked ST03N, SM20 which don't have the info which I need. Any help will be appreciated.

[former_member598790]

Hi Robert,

Can I store these data to any table so that i can fetch it in odata?

[Former Member]

Hi shruthi_karkera,

sure can you insert the data into a table. There are many possibilities.

1. You can manually export the data into excel or csv and then insert it into HANA table.

2. Write ABAP report to do this. I have very basic knowledge of ABAP so I can not help here. But should not be that difficult for an ABAP programmer. The logic is in Function module RSAU_READ_FILE.

3. Read the audit files directly and import the data into HANA. Below is an example in python. Be aware, it works but it is probably not everything covert. Should be enough to get started.

First set up python as described here.

Then create the table:

create table "USER_LOG"( "USER_NAME" VARCHAR (12) null,
     "DATE" VARCHAR (8) null,
     "TIME" VARCHAR (6) null,
     "ACTION" VARCHAR (100) null,
     "WP" VARCHAR (3) null)

On the application server login as <sid>adm and set the credentials. Chane hana.host:port HANA_user and password accordingly.

hdbuserstore set UserLogKey hana.host:3nn15 HANA_User password

Create ProcessUserLog.py file with the following content:

import platform
from hdbcli import dbapi
import sys

#Initialize your connection
conn = dbapi.connect(
    key='UserLogKey',
    encrypt=True,
    sslValidateCertificate=False
)

#If no errors, print connected
print('connected')

cursor = conn.cursor()
Action = ''
count = 0

# main loop
with open( sys.argv[1] , "rb") as f:
    LogEntry = f.read(400).decode('utf-16')
    while LogEntry:
        if LogEntry[0:4] == '2AUC':
            Action = 'User logoff'
        elif  LogEntry[0:4] == '2AU1':
            Action = 'Logon successful (type=%s, method=%s )' % ( LogEntry[116:117],LogEntry[120:121] )
        elif  LogEntry[0:4] == '2AU2':
            Action = 'Logon failed (reason=%s, type=%s, method=%s )' % ( LogEntry[118:119],LogEntry[116:117],LogEntry[120:121] )
        else :
            Action = ''

        if Action != '':
            cursor.execute("INSERT INTO USER_LOG ( USER_NAME, DATE, TIME , ACTION, WP ) VALUES (?,?,?,?,?)",
                       (   LogEntry[40:52].strip(), # User Name
                           LogEntry[4:12],          # Date
                           LogEntry[12:18],         # Time
                           Action,                  # Audit Log Msg. Text
                           LogEntry[27:30],         # Work Proces Nr.
                       )
            )
            count += 1

        LogEntry = f.read(400).decode('utf-16')

print( '%d rows inserted' % (count) )

cursor.close()
conn.close()

Run the python script passing the audit log file name as parameter:

python3 ProcessUserLog.py /usr/sap/<SID>/<Instance>/log/audit_20201011

Here is a screenshot from ta SM20

and the corresponding entries in HANA

Again. I do not pretend this is a complete and production ready solution.

Regards, Robert

[dvankempen]

Hi Shruti,

You can create an audit policy for this and save to a table for reporting

As documented in the platform Security Guide and the Cockpit Guide

• https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.05/en-US/ddcb6ed2bb5710148183db80e4...
• https://help.sap.com/viewer/afa922439b204e9caf22c78b6b69e4f2/2.12.0.0/en-US/a7b91b9ce5864056a6868ddf...