Database view to identify users who is actively utilizing ITIL license

[Vikram3]

I need to do database join on syslog_transaction, sys_user & sys_user_has_role tables to find out the users who are actively using ITIL licenses in servicenow.

This is my join and I'm strugging to do it. Can anyone help here.

[J Siva]

Hi @Vikram3 

First thing, syslog_transaction table is a huge(size) table, creating data base view on this table may impact performance.

Still if you want to build db view, please go through the below prod doc to get good understanding on db view.

https://www.servicenow.com/docs/bundle/yokohama-platform-administration/page/use/reporting/task/c_Cr...

[Dr Atul G- LNG]

Hi @Vikram3 

What do you mean by "actively using"? You can go to the role table and search for ITIL. In that table, you'll find the users assigned to that role. You can also set a condition in the user table, such as "last login within the past 2-3 days," to identify who is actively using it or not

[Vikram3]

Hi @Dr Atul G- LNG I can do with last login but users who has snc_internal role can login to the portal for raising requests which will show false values if I use it.

My requirement is I wanted to identify the users who is actively using the ITIL licenses viz., creating incidents, requests, problems.

[Dr Atul G- LNG]

Hi @Vikram3 

Users with the snc_internal role are considered END USERS. According to the Out-Of-The-Box (OOTB) setup, no role other than snc_user is required to log an incident through the portal. So, there’s no need to worry about this.

As suggested by @AndersBGS  look into this table as well - sys_user_has_lincense