Can we report on cmdb baseline to find all the deleted relationship CIs?

SR20 · ‎12-23-2015

peterraeves · ‎12-28-2015

This is what I see:

The first line is a deleted (and thus removed) CI relationship. The second line is a removed (but not deleted) CI relationship.

Edit: It looks like data older than 30 days is removed, so make sure the data you are looking for is recent. Or stop the table cleaner from cleaning the tables. More information can be found on the wiki; History Sets - ServiceNow Wiki

View solution in original post

peterraeves · ‎12-23-2015

You could use the "Deleted Records" table (sys_audit_delete) to get the deleted CI relationships. Filter for "Table name = cmdb_rel_type to get the deleted records. If you want to get more information from the cmdb_rel_type table, use Database views between sys_audit_delete and cmdb_rel_type to get a more specific report.

SR20 · ‎12-23-2015

Thanks Peter for quick response.

Using Deleted record table we wont be able to get the direct list of CI names where relationship was deleted and removed.

I am looking for something where I can get the direct list or affected CI where relationship is removed ?

For more clarity, currently we can get the relationship removal information from history for the specific CI, I am looking for some report or list where I can get list of CIs.

Thanks .

peterraeves · ‎12-24-2015

I haven't found it yet, but maybe we can get there together. So if you go to sys_history_line.list, you get a list of all changes, among which you can see "(relationship removed)" in the "New" column. So you could filter on "New=(relationship removed)" to see all records which had their relationship removed. Additionally you would have wanted to filter on "Label=<Relationship Name>", but the problem is that the relationship has been removed, so the label would just indicate as "(empty)". Filtering on both "New=(relationship removed)" and "Label=NULL", would show all CIs affected by all removed relationships, and not just those that were affected by the specific relationship that was removed. So if that was what you needed, great. If you wanted to be able to know all related CIs per specific removed Relationship, we need to do some more digging.

Edit: The CIs you are looking for would be in the "Old" column.

SR20 · ‎12-28-2015

Hi Peter,

Thanks for the detailed solution but when I tried New=(relationship removed) I did not find any result of rows. even though I have the CI's where relationship has removed.

I tried with New Value=(relationship removed) also but still no luck.

Does it work for you and showing results of rows?

Thanks

Swati