Need help with a database view between CHG records and sys_history_line

[Matthew20]

Hello,

I am trying to create a data base view to combine the history of the changes(from the audit table sys_history_line from in the related tab screenshot attached) to a field from the CHG record with the rest of the CHG record for easier reporting.

The goals is to be able to let the user in a report search by anything from the CHG record itself or the associated updates to the field mentioned on the form which would be comming from the sys_history_line table.

My most recent database view attempt is attached but i dont really know what im doing. I am not able so far to get anything to show in the database view. Can anyone provide a sample for a solution.

Thanks in advance for your help. Screenshots of the information for more context attached

[Brian Lancaster]

The set field sys_history_line is actually pointing to another table sys_history_set. This is were the data you are looking for is actually stored. Try this as you database view instead.

Edit: ServiceNow does not recommend reporting of the sys tables as you could return thousands of records.

[Matthew20]

Awesome I can actually see stuff now! Thank you. However I don't see what i intended to. I can see everything from the CHG records but what I wanted to see as well is the information in the follow screenshot thats stored in the history. Can that be done?

[Ivan Betev]

Hello @Matthew20 ,

I'm afraid it won't work with sys_history_line. As Brian said it's a bad idea to try and report on system tables, moreover, sys_history_line is a rotation table which gets dropped once in a while.

I'd maybe look towards metrics if you want to collect this data in the future.

Regards, Ivan

[Matthew20]

I understand. I do have a concern about you said it gets dropped. I understand its  rotational but can you point me to the configuration for the rotation timing??? I chose to use this because it looks like in my instance the information in the audit table is as old as 2001 and wasnt apparnelty being dropped.. My assumption was it was not dropped.