Tridion publishing report query

Question

I'm working on building a report to help us audit all publishing changes over the last day. From the Tridion_CM database I've got the following:

SELECT ID,ITEM_TYPE,ITEM_REFERENCE_ID,PUBLICATION_ID,VERSION,TITLE,SCHEMA_REFERENCE_ID,TRUSTEE_ID,MODIFICATION_DATE,SYSTEM_COMMENT,FILE_NAME,PAGE_TEMPLATE_REF_ID FROM dbo.ITEMS WITH (NOLOCK) WHERE (MODIFICATION_DATE > dateadd(d,-1,getdate()))

This gives me almost everything I need, but it looks like some useful information is contained in databases other than Tridion_CM. I presume somewhere there's a Publication_Title and URI values stored. Any help is appreciated.

Answer 1

Firstly it should be noted that your question offers very little by way of context such that people can be specific in helping you;

a report to help us audit all publishing changes

is a very broad and generic statement indeed.

The title of {the Publication and} anything you see in the CMS will be available in same CMS instance but possibly in different tables.

At a quick glance - I'm not sure how useful the results from your query would be (granted - there's on indication of the actual requirements in terms of your actual reports). It wouldn't really provide simple data that springs to mind such as:

• how many pages did we publish on date x
• when was page x last published
• who published the most over the period x
• which pages got published the most over period x

Granted that a lot of these would be 'occasional' queries but once you declare you've reporting in place - wait for the influx of ponderings.

Joining tables to produce meaningful reports may well have implications on the impact (resource usage) of running queries in the first instance and resulting impact on current users and/or external systems.

This begs the question of running queries directly against the CM Database. From memory I don't think running Read Only queries impacts the service agreement but setting frequently running queries isn't really recommended process (at best).

Would it not make more sense to collate the information at publish time and push this out to an external DB such that you can query away until your heart is content without risk of impact to the important ones - the Editors?

May even be worth reaching out to this post and see if anything got planned towards a UI for reporting etc. (Please do add a note here if you find/use anything useful)

Answer 2

Monitoring publishing queue can really be challenging task, especially if you have non stop publishing activity. This is additionally complicated with GUI which becomes unresponsive whenever you have more than 5K items listed.

You can get publish data on multiple ways, all depending what you want to achieve and how. Also, resources you can reserve for this will define your future course of action. So, to sum up, you can get publish data:

1. via querying cm database. This can easily be done via store procedure. You can create store procedure that queries CM database for publish transactions for certain previous time interval. This is certainly the fastest way to get data (select can be done in seconds), but you must know all the internal structure of database and how to get correct data. You can even create windows schedule task to execute power shell script and send result via mail. (we did something similar).
2. You can use event system to send data to some external source and use it later. You can have some external database where you store data and later use it. This is somewhat more complicated solution and requires additional database. Also, event system can somewhat slow down publishing. This way you can have control over data you store and use.
3. Use core service. Why not just use core service to read data from CM? This is by far most logical solution from my point. You can have custom page (or web service) that can read data from CM in elegant way (using API) and show it to users. You can even send mails easily, or filter data the way you want. Sure, its more resource consuming than database querying, but you don't need to take care of database logic when you have API to do all work for you.

Answer 3

@Chris - Point that you made for creating publishing report because publishing queue is overwritten every time same item is publishing and you loose the information.

We had similar situation where we wanted to send weekly status mail of publishing transactions with details how many succeeded and how many failed. For this implementation we used Tridion event system extension and attached the event to publish transaction as given below:

EventSystem.Subscribe<PublishTransaction, SaveEventArgs>(OnPublishStatusChange, EventPhases.Initiated);

In the event system code we are checking if the transaction state is Successful or Failed then we are logging the information: like item name, id, author who published the item, date & time of publish and any other relevant in the database table.

Since data is available in database now you have multiple options to create the report. We choose to create report using sql reporting.