Reporting on CI Changes - Using History Sets

[marcguy]

One of the things that I've been asked for many years is 'How can we report on our CI changes?', i.e. a customer wants to know what is changing on their CIs without having to look at each one individually or create a list of updated CIs and then trawl through that, or trawl through sys_audit which as we know is not recommended.

So I decided to have a look into this and see whether we can use 'History' sys_history_line as a way of reporting those changes, it also looks much better than sys_audit as well as it displays CI names rather than sys_ids.

History is a rotated table and normally those sys_history_lines are generated on the fly when a user views history in a record, that means this table doesn't become another huge sys_audit table, the records are created on-demand and then rotated/dropped off after a set period of time.

So I created a scheduled job to generate History Sets for any CI updated in the last 24 hours and then created a scheduled report to run immediately after that history set generation to query those History lines and voila, we have a CI Change report... steps below

Step 1- update the sys_property 'glide'ui.permitted_tables' to INCLUDE sys_history_line, now this property controls which sys_ tables are reportable, i.e. you can see them as selectable tables in the table drop-down for reporting. (add a 'report_on' ACL if you wish this table to be reportable for anyone else but admin).

Step 2- create a report on History 'sys_history_line' as per screenshot below:

Now this report will initially be empty even if you've just gone and updated a few CIs thinking cool, I will see my updates straight away, but remember the beauty of sys_history_line is that it's generated on-demand, normally when someone goes and looks at history, so now we need to run a little script before we schedule this report to simulate that for the updated CIs...

Step 3 - Create a scheduled report from your report record and add some code in the condition script to a)check we have had some updates and b)to make it generate those history lines we need before actually sending the report empty

The code in that condition to save you some time:

1. answer = false;  
2. var cis = new GlideRecord('cmdb_ci');  
3. cis.addEncodedQuery('sys_updated_onRELATIVEGE@hour@ago@24');  
4. cis.query();  
5. if (cis.hasNext()){  
6.   answer = true;  
7. while (cis.next()){  
8.   var hs = new GlideHistorySet(cis);    
9. var sys_id = hs.generate();  
10. }  
11. }  

So now we have our report, plus the scheduling piece to generate the history lines, I can decide whom gets it, subject, intro, how I want my attachments and hit 'Execute Now'

And here is the report in PDF

Disclaimer time now   if you have masses and masses of CI changes coming in via Discovery or some other source, you may want to run this on specific tables only, or spread out the period of time, I guess it's horses for courses though, alternatively if you only want to capture manual changes, filter out the discovery username in the sys_history or in the scheduled job to find updated records by those users, so use the filters to be most effective and only target the things you want to know about.

Any questions please feel free to comment below.

Marc