Unanswered: Reading from the sysaudits table - Sybase ASE 15.7
I am currently trying to collect the audit logs from the Sybase ASE 15.7 sysaudits table. A SIEM will be using a query to read the audit logs.
Since the sysaudits table can be read by users with SSO role , we had to find an alternative since we cannot give the privilege to the SIEM. Our DBA tried to create a view for this purpose but since the account used by the SIEM is not having a SSO role it failed.
So the DBA proposed we create another table with the same structure (event, eventmod, spid, eventtime, sequence, suid, dbid, objid, xactid, loginname, dbname, objname, objowner, extrainfo, nodeid) and he would write a script that would read from the sysaudits table and write to this new table every 5 minutes.
So we went ahead with the solution, however I ran across a problem which is why I need help
The SIEM solution will read using a query provided by the DBA, however the SIEM solution needs a unique column to keep track of which record it last read. I noticed there is no such unique column in sysaudits. Moreover, I noticed certain commands broken into multiple entries and to keep track of them the sequence ID is used.
I was thinking if I should add another auto incrementing column ID. That will help in keeping track of the audit logs.
For the sequence ID and commands broken in multiple entries, I was thinking perhaps to use a script which reads from the sysaudits and when it encounters a command broken in multiple entries, it could merge it as one entry and write it into our table rather the original multiple entries with sequence numbers.
How should I frame the query for this? Or is there a better way of going about this?
Also, I noticed we only get 92 in the event column event though sybase specified we can get different values for the event field. Am i supposed to get the other values also?