Audit tool for workbooks

Post Reply
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Audit tool for workbooks

Post by JamiseBondi »

Hi,

I'm looking for a method to audit if any workbooks within an instance have been changed/updated but the built in Audit Log viewer seems inadequate for this purpose. Here's the background:

We have a requirement to audit and make sure that no unauthorised transports or modifications are being made in the production environment. This can't be achieved with TM1 authorisations as the developers are entitled to and able to make these changes, so long as they have been approved and it's the right time to do them (not a critical time in the planning cycle) so the task is process controlled. The trouble is either someone who has the TM1 privileges and is being 'lazy' might update a production object (bypassing the other tiers) or else they might inadvertently be making a change in the wrong environment (having pointed perspectives to the production server) and not know he's making the change in prod directly (all instances have identical names across environments Dev, QA, Prod) so unless you double check the path in the properties window you wouldn't know you're in production.

In testing the audit log viewer, I made a change to a workbook, updated it and then used the audit log viewer to see if it logged the change that I had made.... nothing. I went through the tm1auditstore*.log file in notepad++ waiting for an update but saw nothing. The other log files also didn't show any evidence of logging an updated workbook. The only change I picked up was when a private workbook was made public (the blob file was published in a particular folder), or a workbook is added or deleted (blob file status is tracked) but no mention of an update to an existing workbook.
I noticed that dimensions and subsets are tracked nicely in the audit log, not workbooks.
Have any of you come up with a tool that can help to track when a workbook is updated?

Many thanks.
pandinus
Posts: 78
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Re: Audit tool for workbooks

Post by pandinus »

There are multiple ways to get this information.

Either way, the data should come from the date last modified attribute of the files in the }Externals folder in your TM1Data folder.

You can get this information by:
- Manually checking the files in the }External folder and see if any files have been modified. Big drawback is the manual part of this suggestion.
- Create a process which does the check for you and then reads the data info a cube.

You can run a cmd command like the following on the }Externals folder to get the date last modified from the files and store it in output.txt, or whatever file you want.

Code: Select all

for /f %a in ('dir /b') do @echo  %~ta %a >>output.txt
Now you can run a TI process that takes this file and reads it into a cube that you can use to do the reporting on.

You can make this extra fancy by looping through all .blb files in the }Applications folder and check for the ENTRYREFERENCE in the file to link the date modified of the file in the }Externals to the entry in the Applications.

Schedule the export/import processes and you're done!
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Audit tool for workbooks

Post by lotsaram »

-------------------
All the usual disclaimers about association with the product, etc.
-------------------
If you have source control enabled then Pulse will log the change in a file in the }Externals directory. That the change will be in the change log I'm certain. Whether an alert for such a change could be raised automatically I don't think so, at least no in the current feature set.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Re: Audit tool for workbooks

Post by JamiseBondi »

Thanks for the feedback Pandinus and Lotsaram.

Pandinus, while I appreciate the modified date will be updated on a change, using the operating systems logs will only tell me when it was changed, not who the offending party is to know who to talk to (I know I didn't specify this in the original post - sorry - thought it was implied)

Lotsaram, agree on that Pulse might log this, problem is that the client doesn't like an off-site solution - privacy, confidentiality, red tape issues and it's a large company to too many levels of politics to navigate through. I tried this product on them already and though I'm sure its capable, because they don't have an onsite option this isn't going to work for this client.
I am interested in the fact that you're sure there is local logging in the log files to indicate this update - if I get this I can run a query using file locator pro and pick up and exceptions in a few seconds. I just need to know what the log spits out but after making an update and looking through the auditlog I see no record of such a change being logged (for the workbook specifically).

appreciate the suggestions, thanks guys.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Audit tool for workbooks

Post by lotsaram »

JamiseBondi wrote:Lotsaram, agree on that Pulse might log this, problem is that the client doesn't like an off-site solution - privacy, confidentiality, red tape issues and it's a large company to too many levels of politics to navigate through. I tried this product on them already and though I'm sure its capable, because they don't have an onsite option this isn't going to work for this client.
I am interested in the fact that you're sure there is local logging in the log files to indicate this update - if I get this I can run a query using file locator pro and pick up and exceptions in a few seconds. I just need to know what the log spits out but after making an update and looking through the auditlog I see no record of such a change being logged (for the workbook specifically).
By "log" I was referring to the Pulse log of source control events, not the TM1 audit log.
Not sure what you mean by "the client doesn't like an off-site solution". Pulse is an on premise install, under the control of the local admin. So something fails the smell test there.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Re: Audit tool for workbooks

Post by JamiseBondi »

lotsaram wrote: By "log" I was referring to the Pulse log of source control events, not the TM1 audit log.
Not sure what you mean by "the client doesn't like an off-site solution". Pulse is an on premise install, under the control of the local admin. So something fails the smell test there.
Lotsa, I actually contacted Cubewise and they confirm with me that the server runs a daemon which sends stats out to cubewise servers and when the client wants to query Pulse they're using a browser which connects to the webserver outside their organisation.
this is Cubewise's reply to me in 2014 "There is a server daemon installed on the server and you communicate via a browser so I guess you can call it cloud!"

if this has changed then I'll entertain it for sure. Many thanks.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Audit tool for workbooks

Post by lotsaram »

No nothing has changed, Pulse is and has always been an on premise install. The pulse website runs on the server where Pulse is installed to and there is no connection to an external server. Nor is any data sent anywhere outside the organization. I have installed this at several customers and can assure you that's the way it works, I think your concerns on that point are unfounded.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Ben H
Posts: 16
Joined: Wed May 28, 2008 12:24 pm
OLAP Product: TM1
Version: All Versions - even 2.5
Excel Version: All Versions
Location: Sydney, Australia
Contact:

Re: Audit tool for workbooks

Post by Ben H »

I just would like to clarify something about Pulse (full disclosure - I DO work for Cubewise) to make sure the right information is out there.

Pulse is an on-premise install and does not send ANY data outside the organisation.

The only kind of data that might go outside your organisation if you had Pulse would be email alerts that you set up yourself if you want to proactively alert a third party support /consulting partner about specific events or thresholds being met on the server or within your TM1 or Cognos Express user community.
Ben Heinl, Cubewise
#DoGoodTM1
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Audit tool for workbooks

Post by Alan Kirk »

JamiseBondi wrote:Lotsaram, agree on that Pulse might log this, problem is that the client doesn't like an off-site solution - privacy, confidentiality, red tape issues and it's a large company to too many levels of politics to navigate through. I tried this product on them already and though I'm sure its capable, because they don't have an onsite option this isn't going to work for this client.
I'll throw my $0.02 in as an end user of the product only; I can attest to what Ben and Lotsa have said. I see no evidence of data flowing outside of my systems. There's both a thick client and web interface which do different things (with some overlap) but both still require you to be on the in house network (or access it via VPN). I think it's possible that you may have been confusing Pulse with one or two other monitoring tools, at least one of which is defunct, which as far as I can see did require a login to the vendor's server. (I'm not going to name names because looking at the posts about them there's a fair degree of ambiguity about how they worked.) However Pulse is not those products.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Re: Audit tool for workbooks

Post by JamiseBondi »

Thanks guys pointing that out. It seems the comment made previously was out of context so I will be chatting to Cubewise and finding out more about Pulse.
That being said, do I understand from this that there is no native TM1 logging that we could use to identify if a workbook has been updated?
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Audit tool for workbooks

Post by Alan Kirk »

JamiseBondi wrote:That being said, do I understand from this that there is no native TM1 logging that we could use to identify if a workbook has been updated?
What use would that be in a sales demo?

(That would be a "negative", of course. There are a lot of gaps like this which provide a happy hunting ground for third party utilities.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Audit tool for workbooks

Post by lotsaram »

I would have thought that if metadata logging is switched on then any update to an existing application file in the }Externals folder *should* be logged and if it isn't then that's an issue (and you could argue, a bug.) If you have done the test and verified that this is in fact not logged then no, it would appear that there is no native TM1 logging functionality to report this. (Note: it might be possible to add a Debug level logger key in tm1s-log.properties but you would need to 1/ find out from IBM which key to add and 2/ it is highly likely that this would result in your tm1server.log file filling up with all sorts of unwanted garbage in addition to the information that you actually wanted.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: Audit tool for workbooks

Post by BrianL »

lotsaram wrote: (Note: it might be possible to add a Debug level logger key in tm1s-log.properties but you would need to 1/ find out from IBM which key to add and 2/ it is highly likely that this would result in your tm1server.log file filling up with all sorts of unwanted garbage in addition to the information that you actually wanted.)
Yup. A quick check of enabling TM1=DEBUG and updating a workbook shows only two possible candidates for logging. TM1.API=DEBUG (massive amounts of data/noise since it includes data for every single API call) or TM1.System.File=DEBUG (still plenty of noise since it outputs a message every time a file is opened/closed by TM1).

If you need something better you'll have to try your luck with an RFE.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Audit tool for workbooks

Post by tomok »

There are several tools out there that can monitor the file system and let you know when anything has changed. Just Google "Software to Monitor File Changes" and several will pop up. Pick the one you want and put it on your TM1 server and set it to monitor the }Externals folder. Any time someone publishes or updates a workbook files in that folder will be changed and the tool should notify you of that.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply