Tridion CM Usage reporting

Question

My client would like to pull together some metrics regarding our Tridion CM usage. We're interested in the following information.

1. Create – New (Images, Pages, Components)
2. Edit/Update (Images, Pages, Components)
3. Delete (Images, Pages, Components)
4. Roll-back to previous version

If it's possible to provide a SQL script we could run to generate this information, that would be great. I was told by Tridion support that this information isn't stored in the Tridion CM DB and that I would need to roll my own event system to capture the data, is this correct?

Are there any metrics we can gleam from the CM DB about overall usage of the product?

Answer 1

The Alchemy Webstore of Tridion plug-ins has a plug-in that does just this. It captures this event data into Google Analytics which provides the reporting visualization. Here is the link: http://alchemywebstore.com/plugins/GoogleAnalyticsReporting

Answer 2

As per support - there's nothing out of the box and I don't recall seeing any recent CM-Side extensions to store this data.

I guess the 3 starting points you have are:

• you could analyse the IIS log files and see if there's anything useful in there
• You could look at the Tridion 2011 project created by Nuno to "log everything" - bring this up to date
• You could possibly use the fact that Tridion manages versioning on most things to get some sort of history on frequency of updates**

** You could make this much more detailed and drill down to look at the types of content that have been updated etc. - bear in mind that any changes to the CM database structure (and depending on how deep you drill - you could be very sensitive to any changes) would be unsupported and probably require re-work on your part; also, catching this data through complex relationships in the DB is likely to be heavy processing.

I'd consider catching the events (it's in the word there ;) ) as they occur to minimize the hit on the system.

UPDATE

I see Nick posted a useful one on looking at the IIS logs: https://stackoverflow.com/questions/12503734/retrieving-user-login-time-in-sdl-tridion-2011

Answer 3

Another idea could be: creating trigger(s) in the CM DB to write to your custom table(s) capturing whatever you need and once the data are captured, you can create your own reporting metrics to your liking.

Answer 4

You can get some information from the Content Manager database directly, but using the Core Service API and the Event System as needed is a better approach in terms of official Support, community support, and upgrades.

The APIs are built to be backwards compatible whereas the Content Manager database structure isn't public, meaning it can change between versions, breaking any reporting scripts you make.

That being said, Miguel Miguelez shared some example scripts to describe an existing content model. This can be useful in an upgrade scan or to better understand how your schemas, templates, and content relate to each other.

For example, you might use the ITEMS and PUBLICATION tables to get counts.

http://miguelmiguelez.blogspot.nl/2014/01/analyzing-sdl-tridion-cms-data.html

I solicited example reports in this question on Tridion StackExchange Meta: Example Tridion Reports. This can give you an idea of what types of reports might be interesting to your users as well as what's been done before or implemented.

I would still recommend starting with a Core Service client to look at your create, edit/update, and roll-back scenarios. Delete will indeed need Event System code or another solution to be able to report and that. For example, you could record deletes in a separate database or compare separate reports to show which items are no longer in the system.