Combine some of the results in a field and count the total

[mungerc]

Hi all,

I am trying to get a count of all users signed into our VPN. While this is easy, i need it broken out based on the users role into Sysadmin, Students, and Employees. The catch is management wants the Employees number to be a sum total of the Employees, Research, and Administrators, but NOT to include Sysadmins and students. All of these roles are results from the (you guessed it) "roles" field extraction.

index=pulsesecure vendor_action=Closed OR vendor_action=ended OR vendor_action=succeeded OR "Logout" OR "Max session timeout" OR vendor_action=started
| eval user = user. " " . src_ip
| sort -_time
| table user, roles, vendor_action, action, _time, src_ip
| dedup user
| search vendor_action=succeeded OR vendor_action=started
| stats count(user) by roles

This query gives us all the information that is being asked. I just need to get the 3 specific entries all added together from roles.

[asneed_eu]

If the roles field is a multivalue field, which is what it looks like, one solution would be to add another stats command at the end of your search. This example takes your original "stats count" results and sums them up with the roles you mentioned evaled to the Employee role (of course you should double check the names of the roles in this example).

 index=pulsesecure vendor_action=Closed OR vendor_action=ended OR vendor_action=succeeded OR "Logout" OR "Max session timeout" OR vendor_action=started
 | eval user = user. " " . src_ip
 | sort -_time
 | table user, roles, vendor_action, action, _time, src_ip
 | dedup user
 | search vendor_action=succeeded OR vendor_action=started
 | stats count(user) as count by roles
 | rename roles as role
 | eval role = case(role=="Research","Employee",role=="Administrator","Employee",1==1,role)
 | stats sum(count) as count by role