IQ 16.0 SP08 - Login (failed/success) and logout audit

[stefano_bolli]

Hi all,

       I need to track all login attempt, login success and logout events in my SAP IQ 16.0 SP08 engine.

The requirements is to collect these data and send them to an audit monitoring server.

I tried to use the "audit option", but the problem were:

1. Audit infos are written in transaction log files. I cannot access it (using dbtran) until the IQ engine is up. But in our PROD enivronment is not feasible any stop to extract that data. Do you have any suggestions? How can I use dbtran when IQ engine is running?

2. Audit infos are written in a very verbose format. Is it possible to specify a simpler format?

I tried: CALL sa_audit_string('DBA LOGIN AUDIT');

but what I want is something like:

#timestamp; user; action; result

2015-08-10 15:50:00,909; DBA; LOGIN; SUCCESSFULL

Do you have any suggestion about the above issues?

Best regards,

Stefano

[saroj_bagai · Accepted solution]

You should be able tor run dbtran against transaction log

start IQ server

dbtran iqdemo.log iqdemo.sql

SQL Anywhere Log Translation Utility Version 16.0.0.809

Transaction log "iqdemo.log" starts at offset 0001257152

100% complete

Transaction log ends at offset 0001804227

2056686 Aug 10 11:31 iqdemo.sql

or in production, you can make a copy of .db and .log and then copy to different directory and run dbran against copied tran log

[demo0836]$ mkdir save

[demo0836]$ cd save

[I825601@JYOL00547447A save]$ pwd

/work/demo0836/save

[save]$ cp ../iqdemo.db .

[save]$ cp ../iqdemo.log .

[save]$ dbtran iqdemo.log iqdemo.sql

SQL Anywhere Log Translation Utility Version 16.0.0.809

Transaction log "iqdemo.log" starts at offset 0001257152

100% complete

Transaction log ends at offset 0001804389

[stefano_bolli · Accepted solution]

Hi Saroj,

        looking at your example I note that you are using a linux/unix machine.

In my case all our environments are on Windows 2012 R2 platform so that transaction log file is not accesible for any operation while it is in use by IQ engine.

Due to the above limitation I cannot make a copy neither *.log nor *.db file because an operating system error is returned.

Thank you for the fast reply.

Do you have any other suggestions?

What about the availability of a specific audit format?

#timestamp; user; action; result

2015-08-10 15:50:00,909; DBA; LOGIN; SUCCESSFULL

Regards,

Stefano

[saroj_bagai · Accepted solution]

I tested on windows, you can run dbtran against IQ server and it will translate transaction log

dbtran  -g  -c "uid=DBA;pwd=sql;eng=winiqdemo16" -nogui -n iqdemo.sql

and from docs:

Action auditing outside the database server

To provide auditing of actions, under Windows or Unix, any use of dbtran or dblog generates a text file in the same directory as the database file, with the extension .alg.

Some database utilities act on the database file directly. In a secure environment, only trusted users should have access to the database files.

To provide auditing of actions, under Windows or Unix, any use of dbtran or dblog generates a text file in the same directory as the database file, with the extension .alg. For example, for iqdemo.db, the file is called iqdemo.alg. Records containing the tool name, Windows or Unix user name, and date/time are appended to this file. Records are only added to the .alg file if the auditing option is set to On.

For audit format , you can create table in the database and insert auditing records in the auditing table and select in the desired format

[stefano_bolli · Accepted solution]

Hi Saroj,

       I tried using the syntax that you suggested me and it works !!!

In my previous tests I used dbtran directly to *.log file not passing through the IQ engine connection, this was my mistake.

Best regards,

Stefano

[stefano_bolli]

Hi all,

       another custom solution that can be use in this case is described here:

Regards,

Stefano