Possible to view the Document Key field (Sys audit table) using the item's name rather than sys_id

[ravi_otpp]

While creating a report that references the Sys audit (sys_audit) table with the following parameters:

• Type: List
• Group by: Document Key

It seems that the result is being filtered by document key, however, in a sys_id format?

Taking a quick look it seems that there's no way around having it instead show up by the actual item's name and that this field may be hard-coded with the document key?

http://wiki.servicenow.com/index.php?title=Understanding_the_sys_audit_Table#gsc.tab=0

Is this correct? Or is there any way around this?

Thank you.

Ravi

[ginakuntz]

One use case my organization has for this very same thing is that we want to be able to view the Location changes of a certain group of assets over a period of time.   I'm seeing that querying against the Sys Audit table is not recommended for this info, can anyone recommend a method to serve the aforementioned use case?  

Any input appreciated, thanks!

*gina*

[Patrick Schult2]

I had a similar use case - I'd recommend setting up a table that contains an Asset reference, and a pair of columns for old/new value. This gets you a way to easily view the changes of one or more Asset's location over time.

You will populate that new table using a business rule on your Asset table that runs when location changes (or whatever condition you want) and writes a new row into the table containing the previous and new value.

[Sumanth16]

I would like to query the sys_audit table for the assigned date for the all assets because of assigned date is not present in the alm_asset table,i need the report for past and present information of all assets .What is the best way to do that report ?

[Patrick Schult2]

Sumanth,

You should check to see if you have alm_asset.assigned. Like I mentioned elsewhere in this post, you don't want to query sys_audit in a report because it can negatively affect your instance's performance while the query is running.

I'd recommend you start using Asset.Assigned (alm_asset.assigned) to track this information. If you want, you could even write a Fix Script to populate values for this field based on sys_audit records. For example, you could query for all the Assets that are missing an 'Assigned' value, then do a query on sys_audit to find the first update on the Asset's assigned_to field and copy that date into Asset.Assigned. You would then run this fix script during off-hours for your instance to avoid affecting a lot of users.