How to create a database view with below 3 tables

[Naveen Bagula]

Can you please help me on the below query

Can we create a database view with task & sys_history_line 

OR 

database view with task & sys_history_set & sys_history_line

I have created as below, however, it is not working due to where clause issue 

[AshishKM]

Hi @Naveen Bagula, 

Seems like you already created view, Is it working for you or not. Many of operational support team (itil users) is looking for all change/history data from logs, they have option to create metric to support the business need. 

Querying over sys_history_set & sys_history_line tables may lead performance issue or long running query without result or session time-out and it required admin access just in case you are planning something for itil users.

-Thanks,

AshishKMishra

[AshishKM]

Hi @Naveen Bagula ,

Thanks for marking this helpful, so what's final conclusion, are you creating view over system logs tables or not.

-Thanks

AshishKMishra

[Naveen Bagula]

Hi Ashish,

Thank you for the quick response and helping on this. I am going with sys_audit & task table for this as it gave me the required result. 

[Vasu ch · Accepted solution]

Hi @Naveen Bagula 

As per the ServiceNow docs, it is not recommended to use history sets for reporting. You can use Audit (sys_audit) table which has the same data as history 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...

If you want to join task table and Audit table, you can do like below:

Regards,

Vasu Ch