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.
