How to join sys_history_line with hr case table

[Prateek07]

I want to join sys_history_line with hr case table with lable = state. I am trying below DB view solution but it is not working. Please suggest.

Thanks in advance!

[Vasu ch]

Hi @Prateek07 

As per the ServiceNow docs, it is not recommended to use history lines or history sets for reporting. You can use Audit (sys_audit) table which has the same data as history lines and history sets tables.

Refer the below links for better understanding:

https://docs.servicenow.com/bundle/vancouver-platform-security/page/administer/security/concept/c_Hi....

https://docs.servicenow.com/bundle/vancouver-platform-security/page/administer/security/concept/c_Di...

Check this old thread as well:

https://www.servicenow.com/community/developer-forum/database-view-for-history-and-record-history-ta...

Regards,

Vasu Cheeli

[Prateek07]

Hi @Vasu ch 

i checked the sys_audit table but it doesnt have proper data like case number and the state field old and new values are backend numbers. Any recommendation over it?

[Prateek07]

Hi @Vasu ch 

I created a database view on combining hr case table and sys_audit table with the where clause attached in the screenshot. I am able to access hr case and audit table as i am admin.

Problem - when i joined the tables it is showing me acl error 'Number of rows removed from this list by Security constraints: 20' and i am not able to see any data on DB View. Idealy when i am able to access tables then those should be accessible in db view also.

Thanks in advance.

[Vasu ch]

Hi @Prateek07 

It is the ACL issue. Check if the non admin user has relevant access to the HR case table and sys_audit table. Also as per the doc, if the system property "glide.security.expander.view.legacy" is set to true, then it seems you need to define the Read ACL explicitly to the database view.

Refer: https://docs.servicenow.com/bundle/vancouver-platform-administration/page/use/reporting/concept/c_Da...

Regards,

Vasu Ch