Trying to see changes to a particular CI field for a subset of CI's

[Ken Berger]

Hello community,

I have a requirement to get the history of changes to a particular custom CI field (u_patch_window) for a subset of CMDB CI's (name contains WUSA and update within last 60 days).

I have learned that I can filter the sys_audit table to return the changes I am looking for and that the Document Key field is the sysId of the CI in the cmdb_ci_server table but I don't know how to link the results from the sys_audit table to the cmdb_ci_server table (and further filter the name field on the CI's) to give what I am looking for. 

I have read where another community member was able to create a custom database view and I think that may be what I need to do here, but there were no details as to how they did it.  I am researching further on this but in the meantime, if anyone can help with the missing pieces or if there are any better ideas, I am happy to entertain them 🙂

Thanks,

Ken

[susan789wolfe]

---

@Ken Berger wrote:

Hello community,

I have a requirement to get the history of changes to a particular custom CI field (u_patch_window) for a subset of CMDB CI's (name contains WUSA and update within last 60 days). StarbucksSecretMenu

I have learned that I can filter the sys_audit table to return the changes I am looking for and that the Document Key field is the sysId of the CI in the cmdb_ci_server table but I don't know how to link the results from the sys_audit table to the cmdb_ci_server table (and further filter the name field on the CI's) to give what I am looking for. 

I have read where another community member was able to create a custom database view and I think that may be what I need to do here, but there were no details as to how they did it.  I am researching further on this but in the meantime, if anyone can help with the missing pieces or if there are any better ideas, I am happy to entertain them 🙂

Thanks,

Ken

---

Hi Ken,

You’re on the right track with using the sys_audit table to track changes to your custom CI field. To link the sys_audit table to the cmdb_ci_server table, you can use a SQL query that joins these tables based on the Document Key and the sys_id of the CI.

Here's a basic outline of how you can construct your query:

Filter the sys_audit table for changes to the u_patch_window field within the last 60 days.
Join the sys_audit table with the cmdb_ci_server table using the Document Key field.
Filter the CIs whose names contain "WUSA".
Here's an example query you can use in ServiceNow:

SELECT a.*
FROM sys_audit a
JOIN cmdb_ci_server c ON a.documentkey = c.sys_id
WHERE a.field_name = 'u_patch_window'
AND a.sys_created_on >= DATEADD(day, -60, GETDATE())
AND c.name LIKE '%WUSA%'
ORDER BY a.sys_created_on DESC;

Explanation:
SELECT a.*: Retrieves all fields from the audit table.
JOIN cmdb_ci_server c ON a.documentkey = c.sys_id: Joins the audit records with the CI records based on the document key.
WHERE conditions: Filters for changes to the specific field, checks if the changes happened within the last 60 days, and filters CIs by name.
ORDER BY a.sys_created_on DESC: Orders the results by the date the change was made.
Creating a Database View:
If you prefer to create a database view for easier future access, you would follow similar steps but would encapsulate the SQL logic into the view definition. Here’s how you might define the view:

Go to System Definition > Database Views.
Create a New Database View and define the tables and join conditions.
Add the necessary filters to the view definition, similar to the query above.
Alternative Approach:
If you want to avoid custom database views, consider creating a Scripted REST API or a Scheduled Job that queries this information and stores it in a report or custom table for easier access.

Let me know if you need any more help with this!

Best Regards, 
Susan