Pulling Run History directly from Content Store

[andy_mason_84]

Hi All,

I am using the following bit of SQL to pull out a list of reports from the Content Store.

I was wondering if anyone has had any joy in pulling out the run history times from the Content Store too?  Basically i would like a list of reports and be able to see which reports have not been ran in the last 6 months for example.

I thought dbo.CMOBJPROPS5.ACTEXECTIME may have given me this but I don't think that is right.

Answers on a postcard (first class  ;) )

Cheers,

Andy


SELECT      dbo.CMOBJNAMES.NAME AS ObjName, dbo.CMOBJECTS.PCMID, dbo.CMOBJECTS.CMID, dbo.CMCLASSES.NAME AS ClassName--, dbo.CMOBJPROPS5.ACTEXECTIME
FROM         dbo.CMOBJECTS

INNER JOIN  dbo.CMOBJNAMES ON dbo.CMOBJECTS.CMID = dbo.CMOBJNAMES.CMID

INNER JOIN  dbo.CMCLASSES ON dbo.CMOBJECTS.CLASSID = dbo.CMCLASSES.CLASSID

--dbo.CMOBJPROPS5 on dbo.CMOBJECTS.CMID = dbo.CMOBJPROPS5.CMID
                     
WHERE     (dbo.CMOBJECTS.CLASSID IN (10, 37))
ORDER BY dbo.CMOBJECTS.PCMID

[pumccg]

Run History is not a true picture of report usage as it only records the last X instances of run history, and a given report might have X set at zero.
Much better to use the Audit Tables as these do record complete history provided the auditing level is set to at least basic (from memory). The only weakness is if an author never runs a report at least once as it will not appear at all in the audit tables. Cognos use an XML method to detect these but I never worry about these.

[andy_mason_84]

Thanks for the intel.

[pumccg]

Hope it works out for you. The Cognos Audit tables sample reports are a good starting point, but lack any useful reporting of when users are using Analysis Studio. There's an article in the Cognos Knowledge Base somewhere which shows how to extract Analysis Studio usage from the Audit tables.

The other issue I had with the sample Audit Reports was "too much detail", and not enough consolidation of the auditing data to facilitate "analysis" of the auditing data. Hence I'd typically create a Transformer model and build a cube to consolidate the auditing data. Makes it much easier truly analyse the auditing tables!