Reporting on History

[emma_hunt]

Hi there,

I am wondering if it is possible to run a report on different CI attributes that have changed over time. For example, I want to have a report that shows me how many CIs had an empty support group 6 months ago, 5 months ago, 4... etc. I would like this so that I can report on how our CMDB Process is improving the data in the CMDB.

Does anyone have any clue how to do this?

[Chris M3]

You 'may' be able to get this data out of the audit tables, but it would be a painstaking process, and it's not directly supported (and certainly not suggested) to do anything against those tables.

Your best bet is to configure metrics of some sort that run and gather this info.  If you have Performance Analytics, then it would probably be perfect for this.  What my group does though is simply gather those metrics once a month, and keep history outside the tool (Excel spreadsheets).  

The Compliance module would be another way to gather this data/show history.  If you have a desired state audit running once monthly, and just creating audit success/failure records, you can get a report based off those.

[Ankit P]

Hi, All you need is stored in History(sys_history_line) table, but that is not Reportable by default. Please go to UI Properties and add the table name at the end of below field

While creating your report you please note that the Set field contains the record on which change was made.

[Chris M3]

The sys_history_line table only contains history of records where history has been viewed.  It is a temporary cache of data pulled from sys_audit and sys_audit_relation, it does not build as updates are made (unless history is being viewed as the updates are made), and will disappear after 30 days(OOB setting)

[Carl Fransen1]

Hi Emma,

I've setup Metrics for the HR Module and these work really well, you could setup something like the below for say the 'Computer' table.  For HR a matric on the 'base' table also captures all changes to extended tables, so I would 'assume' that this would be the case for the CMDB tables as well, but you'd have to try this out.

You could then also merge the metric with the CI for reporting through a data base view.  I'd copy the one that's already there for Incident and this will allow you to report on the CI and Metric information in one report, so it allow access to both tables data.  Let me know if you need an example for this as well.

Hope this helps

Carl.