The CreatorCon Call for Content is officially open! Get started here.

how to get count of unique, active, itil-roled user accounts

Casey12
Kilo Expert

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?

1 ACCEPTED SOLUTION

staceybailey
ServiceNow Employee
ServiceNow Employee

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.

View solution in original post

5 REPLIES 5

Casey12
Kilo Expert

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.