How to report on history table for incident and configuration items

[mitzaka]

Hey SNC,

I have the following case - on the Configuration Item form, we have a field for the CI version, called 'version'. As you ca guess, this field is being auto-populated by discovery (custom) and then updated often, when the discovery finds a new version. So one CI can move between different versions over time.

I want to be able to report on the number of related incidents for certain version of a configuration item. Simple database view between incident and configuration item tables would not suffice because one CI can move between different versions in time like I mentioned above. So I need something which will search through the incidents and then search for the ones which have a CI related for a particular CI version (which would be some period in time, when the version of the specified CI has been the one in questions).

Any good ideas on how to manage that?

Thanks in advance!

[Valentina6]

Hi Dimitar,

this should work:

1. Add a new field in your incident form, where populate the version of the related configuration item through a scheduled job

2. CI Version updates are tracked in the sys_audit table

3. Build a query in the scheduled job script: get all records about CI version update where 'sys_audit creation date < incident creation date' and order the results from the most recent.

4. The first result is the version you need and you can retrieve it with if(query.next()). Save the result in the new field and create a report on the incident table

5. If there are no results, use the else instruction to get the current CI version and push it to the new field.

For future incidents:

1. Add a new field in your incident form, where save the current version of the related configuration item through a business rule

Hope it helps!

Regards,

Valentina

[mitzaka]

I like that approach. I do have a couple of questions though:

a. why you recommend a scheduled job, why not do it with a BR instead which basically checks the version of the CI and sets it on the incident form field?

b. also, I figured that the 'version' field is not on my parent cmdb_ci table, which is a bit annoying because I have to embed some IFs in the script where I check specific classes of CIs and if the incident is related to such a CI, then I would need to go and fetch the CI version of that CI and then paste it in the incident field. Does that make sense to you, or you could think of a more general method (because this one will be hard to maintain for the future with addition of new CI classes)

[Valentina6]

Hi Dimitar,

a. because it's the faster way to update existing records

b. why do you need the CI class/table? The document key (sys_audit table) matches with the CI sys_id, the field name (sys_audit table) should contain something like 'version'. I think it make sense, you have only to maintain the version field name in the different CMDB classes.

Regards,

Valentina

[mitzaka]

OK, makes sense. I tried to do what you recommended. I have the following script in my scheduled job, but it does not seem to work.

var gr = new GlideRecord('incident'); // I list the incidents

gr.addQuery('u_ci_version','');

gr.query();

gs.log("row count is " + gr.getRowCount());

var ci = new GlideRecord('task_ci'); // I find the CI related to the incident

ci.addQuery('task_number',gr.sys_id);

ci.query();

while(gr.next()) {

  var grVcenAudit = new GlideRecord('sys_audit');

  grvCenAudit.addQuery('tablename','cmdb_ci_vcenter'); //I tried with this table, as I know it holds the Version field

  grVcenAudit.addQuery('fieldname','version');

  grVcenAudit.addQuery('documentkey',ci.sys_id); // I link the sys id of the CI in the incident to the document key in the sys_audit table

  grVcenAudit.addQuery('sys_created_on','<',gr.opened_at);

  grVcenAudit.orderByDesc('sys_created_on');

  grvCenAudit.query();

  while (vCenAudit.next()) {

  gr.setValue('u_ci_version',grVcenAudit.version); // I assign to the incident field

  gr.update();

  }

}

Any obvious reasons why this does not update the incidents?

feels wrong to me, as of the whole usage of the CI table (the one which has the field Version is not the CMDB_CI one, which is actually referenced in the incident reference field for CI, this is the odd thing to me.