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?

[Richard78]

I understand that this is not the best place because of the limited nature of what should be in those tables and I am pursuing the audit table direction (thank you for the recommendation).

However, I am still wondering why I did not see records. By what is stated in those paragraphs I should still see records if I create a view that contains no criteria, but I am not and in one cause I can see there are results but it does not display them like it's an security issue but does not say they were not visible because of security. I know I am a stubborn guy, but I would really like to know why I can't seem to see record results in DB views that use these two tables.

[Kalaiarasan Pus]

Check the table and see if you have the records needed.

sys_history_line entries are created when you click the button and specific to the user. So, you might not have record.

[Richard78]

I literally am creating a view with no requirements just a table and some fields from the table to do a test to see the records.

when I go to sys_history_line.list (from the nav bar) I can see I have records. When I do a try from the db view I see none.

So unless the records were being created for me as an instance when I did the sys_history_line.list (which given the fast response and the way the table description is worded I doubt) I should see the same results when I do a db view try with no criteria against the same table. But I do not, I get no results from the sys_history_line and hidden results from sys_history_set... I am very frustrated at this.

[nadircavatorta]

I know this post is six months old, but I've had the same issue, and opened an incident to HI (INT3125954).

The support answered that this is a correct behaviour due to the participation of the sys_history_line table in Table Rotation: on those tables it isn't possible to query with dbviews.

From the incident:

"The sys_history_line table participates in Table Rotation. Ideally DB views should not be created on tables that participate in Table Rotation as it will not produce desired results. Please find below wiki article that explains on creating DB views:

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

"Also, you can see the list of tables that undergo Table Rotation under - System Definitions - Table Rotations. "

Nadir