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?

[emma_hunt]

Hi Carl, 

I tried this out for a field on the cmdb_ci table. I had to copy the BR "metrics events" for it to process changes and so far it looks like it is working pretty nicely for new updates. I am wondering if there is a way to track all changes ever made to that particular field and not just new ones since implementing that metric? Do you know of any scripts I can utilize for this type of behavior? 

Thanks!

Emma

[Carl Fransen1]

Hi Emma,

Nice work - looks like you've got it sorted for all future events - I hadn't realised you'd need to copy the BR, but that makes sense as it was only on the Task table.

In regards to creating historical 'metric_instance' records I'm afraid I can't help with scripting.  However the below script was provided by @Mike Patel and this looks for existing 'metric_instance' records and updates the 'business_duration' field (the syst_id is my base Schedule for all Service Level Definitions).  This worked a treat for me and I ran it on production as a scheduled job so it ran after hours, as it took over 1.5 hours to run on UAT (as a background script).

var gr = new GlideRecord("metric_instance");
gr.addEncodedQuery('end!=NULL^business_duration=NULL');
gr.autoSysFields(false); // so that the records don't have system updates
gr.query();
while(gr.next()) {
	var gsBusiness =new GlideSchedule('dcf6c8fedb5d7e007d1bfa0dbf9619db');
	// Get duration based on schedule
	gr.business_duration = gsBusiness.duration(gr.start.getGlideObject(), gr.end.getGlideObject());
	gr.setWorkflow(false); 
	gr.update();
}

So maybe you could contact @mikepatel or even @dennisr as they have both helped me out with Metrics and scripts.

Hope this helps

Carl.

[emma_hunt]

Hi Carl,

Thanks for that! That helps me understand a little better what to do with the script field. I will try to reach out to them to get their input.

Thanks!

[Carl Fransen1]

All good - hope you can find what you need...