History line does not show results when trying to use it in a database view

[Richard78]

Request: To be able to see the description, close notes, and incident number of incidents that were transferred from Help Desk within a time period.

Background: Help desk had previously ask for a way to see tickets that were transferred out of their group, I created a module that looked at the History (sys_history_line) table and a custom list view.

My Solution: I am trying to do is   create a database view that joins History (sys_history_line) with Record History (sys_history_set), sys_history_line_set=sys_history_set_sys_id, and then joins Record History (sys_history_set) with Incident (incident), sys_history_set_id=incident_sys_id. Then once I have this database view I will modify the existing module to point to the new database view.

Issue: When attempting to join these tables I am not see any records in the database view.

Troubleshooting:

I attempted joins between just two tables at a time:

• History with Record History: no records
• Record History with Incident: one visible record and a number of records that are invisible that match with the record count in the Record History table (this is odd since I don't see any being prevented via security and I am admin and sec admin).

I attempted just setting a single table in the db view to validate:

• Incident: I see all records as I should.
• History: no records
• Record History: I see the same results as when I join it with Incident.

If I do a sys_history_set.list I can see all the records in the table, likewise when I do a sys_history_set.

The only conclusion I can make is that there is a rule somewhere that is preventing visibility of these records when used with a database view.

Can anyone explain this or has anyone else experienced this?

[Anurag Tripathi]

History line gets build for each user when they access the record for the first time. Counting on History line to implement logic(querying) is risky.

[Kalaiarasan Pus]

I didn't try history table but how about using sys_audit table?

Condition: audit_documentkey=inc_sys_id && audit_tablename='incident' && audit_fieldname='assignment_group'

Note: You need to be careful with history and audit table, since these are big tables and might start impacting performance.

[Richard78]

I like this but I noticed that the old and new values are sys_id's, I suppose I could do a join to the sys_user_group table on the new value and a filter on the old using the HD groups sys_id...

Is there a reason that the other tables are not showing results when I use them in DB views?

[Kalaiarasan Pus]

There might be no related record to show. The entires are created on fly.

The Audit [sys_audit] table is where the system stores historical information for all records. These records are intended to be kept forever so that administrators can always track the history of audited records. As the number of auditing records grows over time it becomes more and more inefficient to directly query the Audit table for historical information. It is much more efficient to run queries only on the smaller subset records you actually want to view historical information for.

The History Set [sys_history_set] table identifies which particular records from an audited table have historical information. The History [sys_history_line] table stores the actual changes to field values that occurred. The system automatically generates History Set and History records as needed from the Audit table when a user either creates a record or requests its history. Rather than containing a complete history of all changes in the system, History Set and History records only contain a recent subset of historical information for records where users have created or requested such information.

History Sets - ServiceNow Wiki