Get distinct count of users, per user type, for each domain

[deton0]

Hi

I was hoping someone might be able to help me with what I'm trying to achieve. I've tried to work this out but with limited knowledge of queries I'm struggling.

I have a very basic query (below) which outputs the following data from my index...

index=centredata (domain="*.create.com*" OR domain="*.puzzle.com*") sc_status=200
| table centreid, domain, groupid, userid

centreid domain             groupid    userid
0123       create.com     Student    tiyud0
0123       create.com     Staff          dbega0
0456       create.com     Staff          oghjr6
0123       puzzle.com     Student   qsadh2
0456       puzzle.com     Staff         srthg5
0123       create.com     Student   klghg2
0456       create.com     Staff         wrdv6
0456       create.com     Student   xcgar4
0123       create.com     Student   tiyud0
0456       create.com     Staff         oghjr6
0123       puzzle.com     Student   klghg2
0456       puzzle.com     Staff         ykszd9
0123       create.com     Staff         dbega0
0456       puzzle.com     Staff         oghjr6
0123       puzzle.com     Student   tiyud0
0123       create.com     Staff         sdgxc0

The output I'm wanting is the count of distinct userids for both groupid types (staff and student) by domain and centreid. An example of how I need it to look is as follows...

centreid domain            staff       student
0123       create.com     2             2
0456       create.com     2             1
0123       puzzle.com     0             3
0456       puzzle.com     3             0

Many thanks in advance 🙂

[deton0]

Thank you that worked well 🙂

[bowesmana · Accepted solution]

If you paste this into a search window you can see how this works on your data

| makeresults
| eval _raw="centreid domain groupid userid
0123 create.com Student tiyud0
0123 create.com Staff dbega0
0456 create.com Staff oghjr6
0123 puzzle.com Student qsadh2
0456 puzzle.com Staff srthg5
0123 create.com Student klghg2
0456 create.com Staff wrdv6
0456 create.com Student xcgar4
0123 create.com Student tiyud0
0456 create.com Staff oghjr6
0123 puzzle.com Student klghg2
0456 puzzle.com Staff ykszd9
0123 create.com Staff dbega0
0456 puzzle.com Staff oghjr6
0123 puzzle.com Student tiyud0
0123 create.com Staff sdgxc0"
| multikv forceheader=1
| table centreid domain groupid userid
| stats dc(eval(if(groupid="Staff",userid,null()))) as Staff dc(eval(if(groupid="Student",userid,null()))) as Student by centreid domain

All the code up to the table command is to recreate your data.

It's using the eval statement within the stats dc command to calculate what you need