TM1 Audit Log reports

Post Reply
gabi
Posts: 23
Joined: Thu Jun 19, 2008 12:44 pm

TM1 Audit Log reports

Post by gabi » Thu Oct 03, 2013 2:20 pm

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 Cohen
Biconix International

gabi
Posts: 23
Joined: Thu Jun 19, 2008 12:44 pm

Re: TM1 Audit Log reports

Post by gabi » Thu Oct 10, 2013 9:47 am

Anyone ? :-)
Gabi Cohen
Biconix International

TheEnforcer
Posts: 7
Joined: Thu Oct 27, 2011 4:31 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: TM1 Audit Log reports

Post by TheEnforcer » Thu Oct 10, 2013 11:39 am

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.

User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: TM1 Audit Log reports

Post by rkaif » Sat Oct 12, 2013 6:03 pm

gabi wrote: 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.
Attachments
tm1_auditLogs.jpg
TM1 Audit Logs
tm1_auditLogs.jpg (159.1 KiB) Viewed 7660 times
Cheers!
Rizwan Kaif

gabi
Posts: 23
Joined: Thu Jun 19, 2008 12:44 pm

Re: TM1 Audit Log reports

Post by gabi » Thu Oct 17, 2013 10:24 am

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
Gabi Cohen
Biconix International

User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: TM1 Audit Log reports

Post by rkaif » Sun Oct 20, 2013 8:05 pm

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.
Cheers!
Rizwan Kaif

dumbom
Posts: 16
Joined: Thu Jun 27, 2013 9:07 am
OLAP Product: TM1
Version: CX 10.1 and TM1 10.0
Excel Version: 2007 and 2010
Location: South Africa

Re: TM1 Audit Log reports

Post by dumbom » Thu Jan 16, 2014 5:42 am

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
Community Contributor
Posts: 107
Joined: Thu Feb 26, 2009 8:44 am
OLAP Product: TM1
Version: 9 + 10
Excel Version: 2007 Professional
Location: Isle of Wight, UK

Re: TM1 Audit Log reports

Post by iansdigby » Wed Apr 30, 2014 2:47 pm

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.
Attachments
Audit Log Processor.xlsm
Use at your own peril
(70.19 KiB) Downloaded 342 times
"the earth is but one country, and mankind its citizens" - Baha'u'llah

Post Reply