- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-14-2019 11:58 AM
How do I get a count of unique, active, itil-roled user accounts in Reporting? So far, my report is like the following:
table: User Role (sys_user_has_role)
filters:
State is Active
Role is itil
User.Active is true
To be expected, the User field has some identical values (e.g., there are multiple "John Smith"s in the organization, each with a different account). And, to be expected, some users can have multiple accounts with the same User value (e.g., user "John Doe" can have account A and account B). However, I have discovered that, at least in my ServiceNow instance, I have several User values (in this User Role table) that also have the same User ID value. For example, User "John Smith" appears twice in the table (with an itil role), and both instances have the same User ID. So, it seems that a given user account can appear in this table 2+ times. Is it possible that this table can have duplicate rows? If so, then how can I get a count of unique, active, itil-roled user accounts?
Solved! Go to Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-14-2019 01:58 PM
You can also report against the User table directly with filters of Active = true and Roles 'is' itil.
There is some extra "magic" behind the scenes that allows you do filter on roles from the user table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-14-2019 02:19 PM
Thank you both, Stacey and Adam. I have confirmed that both methods give me the same result. I agree that it would be easier to use the User table, rather than the User Role table. That way, it's a simple single-score to read off, instead of the additional filter (State is Active) and the slightly hackish solution (group by User.User ID, read off the count at the bottom of the screen). I had no idea that this "magic" existed on the User table.