Create a Database View to Display Licensed Roles Against Active Users

appstorm
Tera Contributor

Trying to create a database view to display active users within the system who have logged-in within the last 3 months and are assigned licensed roles.

 

Screenshot 2025-10-02 140936.png

 

I would think the where clause should use the 'name' field from the license_role table to join with the 'role' field on the sys_user_has_role table.  Then, it would just be a matter of setting the filters on the report to show where active = true && logged-in months?

4 REPLIES 4

Rafael Batistot
Mega Patron

Hi @appstorm 

Database view with 3 tables

  1. sys_user_has_role
  2. license_role
  3. sys_user

Joins:

  1. sys_user_has_role.role = license_role.name

  2. sys_user_has_role.user = sys_user.sys_id

 

 

Thank you!  So, the prefixes defined in the database view would be included, also?Screenshot 2025-10-02 151349.png

pareeer
Tera Contributor

Hello,

Here's what I did on my environment: 

pareeer_0-1761295530568.png


Hope this helps.

MaxMixali
Mega Sage

Creating a Database View to Display Active Users with Licensed Roles Logged-in Within Last 3 Months
=========================================================================================

Problem:
---------
Need to create a database view showing active users who have logged in within the last 3 months and are assigned licensed roles.
The confusion arises around which fields to join — `license_role.name` or `sys_user_has_role.role`.

-----------------------------------------------------------------
Correct Join Logic:
-----------------------------------------------------------------
Do NOT join `license_role.name` directly to `sys_user_has_role.role`.
`sys_user_has_role.role` is a reference to `sys_user_role.sys_id`.
The correct relationship chain is:

sys_user → sys_user_has_role → sys_user_role → license_role

Thus, you join **`sys_user_role.name` = `license_role.name`**.

-----------------------------------------------------------------
Database View Structure
-----------------------------------------------------------------
Primary Table: sys_user (alias: u)
Join 1: sys_user_has_role (alias: uhr)
- Join condition: u.sys_id = uhr.user
Join 2: sys_user_role (alias: r)
- Join condition: uhr.role = r.sys_id
Join 3: license_role (alias: lr)
- Join condition: r.name = lr.name

Filters to apply later in your report:
- u.active = true
- u.last_login_time >= javascript:gs.monthsAgoStart(3)

-----------------------------------------------------------------
Example Conceptual SQL (for understanding only)
-----------------------------------------------------------------
SELECT
u.sys_id AS user_id,
u.name AS user_name,
u.email,
r.name AS role_name,
lr.display_name AS licensed_role,
u.last_login_time
FROM sys_user u
JOIN sys_user_has_role uhr
ON u.sys_id = uhr.user
AND uhr.state = 'active'
JOIN sys_user_role r
ON uhr.role = r.sys_id
JOIN license_role lr
ON r.name = lr.name
WHERE u.active = true
AND u.last_login_time >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP)
AND lr.active = true;

-----------------------------------------------------------------
Steps to Create the Database View in ServiceNow
-----------------------------------------------------------------
1. Navigate to System Definition → Database Views → New.
2. Name: vw_active_users_licensed_roles.
3. Add tables and aliases:
- sys_user as u (primary)
- sys_user_has_role as uhr
- sys_user_role as r
- license_role as lr
4. Define joins:
- u.sys_id = uhr.user
- uhr.role = r.sys_id
- r.name = lr.name
5. Expose columns:
- u.sys_id, u.name, u.email, u.active, u.last_login_time
- r.name (role)
- lr.display_name (licensed role name)
6. Save and Create the View.

-----------------------------------------------------------------
Report Filters (applied in report builder)
-----------------------------------------------------------------
- Active = true
- Last login time >= javascript:gs.monthsAgoStart(3)

Optional: Use Count Distinct (u.sys_id) to get unique user count.

-----------------------------------------------------------------
Additional Tips
-----------------------------------------------------------------
- Ensure `uhr.state = active` to exclude inactive role grants.
- You can filter `license_role.product` to target a specific licensed module (e.g., ITSM Pro).
- Exclude users who never logged in with `u.last_login_time != NULL`.
- For performance, apply report-level filters to reduce the dataset.

-----------------------------------------------------------------
Summary:
-----------------------------------------------------------------
✅ Use `sys_user_role.name = license_role.name` for join.
✅ Filter by `u.active` and `u.last_login_time`.
✅ The view shows only active users with licensed roles who logged in during the last 3 months.