TM1 Audit Log reports

[gabi]

Hello,

Please advice for automated method to create audit log reports for users login (Success/Fail).
Is there any best practice to generate reports from audit log files using TI or any other method?
I Found it a little bit problematic to automate this task as:
1. The audit log files format is not completely flat organized
2. The audit log file names are random (time stamp)

Thank you,
Gabi

[gabi]

Anyone ?

[TheEnforcer]

Silence says it all - there really isn't any.

Was with some IBMers yesterday & it was a question posed to them. They didn't know either, but have vowed to investigate.

[rkaif]

Please advice for automated method to create audit log reports for users login (Success/Fail).
Is there any best practice to generate reports from audit log files using TI or any other method?
I Found it a little bit problematic to automate this task as:
1. The audit log files format is not completely flat organized
2. The audit log file names are random (time stamp)

First make sure you have AuditLogon=T in the TM1S.CFG file. Then to view the audit logs you can look at the Audit Logs in the Server Explorer. This Audit log shows you the Successful and Unsuccessful logon attempts. See attached screen shot.

If you want to create automated Reports for Logon attempts, then you will have to load the TM1 Audit Store files into a cube. These audit logs are stored in files named tm1auditstore<datetime stamps>.LOG

Using TI you can first create a list of all the Audit Store.LOG files. In Windows you can use the '>' sign to list the files names to a file. Syntax is dir tm1auditstore*.log > AuditFiles.txt

See this URL for details: http://support.microsoft.com/kb/196158

Then use the AuditFiles.txt as the source of the another TI which will grab the file name and will parse through every file one by one. In the TI you can look for the Successful and Unsuccessful attempts. Successful attempt has the handler 144, while the unsuccessful handler is 145.

[gabi]

Thank you all for your replies.

Hi rkaif,

How do you suggest to control audit records exported to text file? How can you know what audit log files already loaed to text file and what line you already read from the last audit log file? (after 1st run)
Also, Audit log events are writen in 2 lines - see example below - Do you have a suggestion how to deal with it in TM1 TI?

"172","TM1_Fin","1","Property PersistSecurityFilteringOfDynamicSubsets was set to '1' for server 'TM1_Fin'."
"166","TM1_Fin","FALSE","Property ParallelInteraction was set to 'FALSE' for server 'TM1_Fin'."
</Details>
</Commit>
<Commit ts="20130709115417" client="Active Directory\CognosPRDAdm">
"144","Active Directory\CognosPRDAdm","","User 'Active Directory\CognosPRDAdm' successfully logged in from address ''172.21.10.203"
</Commit>

Thank you,
Gabi

[rkaif]

The Audit log file name has the Date/Time Stamp. You can store this value in a control/Lookup cube and each time you refresh the cube then you check this value in the Lookup cube. If the Log file name is less then the Date/Time in the control cube then you simply ignore that file into the cube.

Each line in the Audit log is has a Identifier Tag e.g for successful login the tag is 144 and for Unsuccessful login the tag is 145. When you are parsing the Audit file you will have to look at this Tag value and if it is 144 or 145 then you will load it into the else do ITEMSKIP.

[dumbom]

hi Guys

i have created an audit log cube that says when people were successfully logged in or not, but i am stuck when it comes to not repeating lines that were already captured. i created the look up cube, but dont know how to tell tm1 to ignore all the lines in the file until the date in the file is greater than the date in the cube. am i doing it the wrong way?

[iansdigby]

I find the TM1 audit log utility good for small queries but, shall we say, 'limited' for anything large.
You can use the attached Excel VBA code to get logical tuples out of an Audit Log file. (free but with no warranty implied or given as to its functionality).
TM1 Audit logs are structured mainly logically but with bits of pseudo-XML such as <Commit>...</Commit>.
The attached will resolve those and output a file with a fully logical structure containing for each row:

Client
Date/time as a string
Object affected
Up to 4 fields of variable data
The decription of the action.

In VBA you need the Microsoft Scripting Runtime dll loaded (from Excel, go Alt-F11, Tools, References, then pick it from the list)
Note it takes 7 minutes to process 1 million rows (approx 130Mb log file), so it ain't fast.
It creates an output file named the same as the log file plus the word "out". You need to be able to read this into Access or a SQL or other database, or if 1m rows or less, into Excel (2007 or greater). It's then much easier to query the log. You can of course accumulate them but they can be really large and the free SQL Server Express has a 10Gb database limit (about 40 million sudit log rows). I should think Access would break after about 4 million rows.