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 to be honest.
History is a rotated table and normally those sys_history_lines are generated on the fly, meaning this table doesn't become another sys_audit, records are created on-demand 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 report to query those History lines and voila, we have CI Change reporting... 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:
answer = false;
var cis = new GlideRecord('cmdb_ci');
cis.addEncodedQuery('sys_updated_onRELATIVEGE@hour@ago@24');
cis.query();
if (cis.hasNext()){
answer = true;
while (cis.next()){
var hs = new GlideHistorySet(cis);
var sys_id = hs.generate();
}
}
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 here now if you have masses and masses of CI changes, 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, or 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 record.
Any questions please feel free to comment below.
Marc
