https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517605#M145563 <P>Hi</P><P>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.</P><P>I have a very basic query (below) which outputs the following data from my index...</P><P>index=centredata (domain="*.create.com*" OR domain="*.puzzle.com*") sc_status=200<BR />| table centreid, domain, groupid, userid</P><P>centreid domain&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;groupid&nbsp; &nbsp; userid<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com&nbsp; &nbsp; &nbsp;Student&nbsp; &nbsp; tiyud0<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dbega0<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; oghjr6<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;puzzle.com&nbsp; &nbsp; &nbsp;Student&nbsp; &nbsp;qsadh2<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;puzzle.com&nbsp;&nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;srthg5<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Student&nbsp; &nbsp;klghg2<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;wrdv6<BR />0456 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Student&nbsp; &nbsp;xcgar4<BR />0123 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Student&nbsp; &nbsp;tiyud0<BR />0456 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;oghjr6<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Student&nbsp; &nbsp;klghg2<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ykszd9<BR />0123 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dbega0<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;oghjr6<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Student&nbsp; &nbsp;tiyud0<BR />0123 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sdgxc0</P><P>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...</P><P>centreid domain&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; staff&nbsp; &nbsp; &nbsp; &nbsp;student<BR />0123 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2<BR />0456 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com&nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com&nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0</P><P>Many thanks in advance <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 03 Sep 2020 01:12:49 GMT deton0 2020-09-03T01:12:49Z https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517605#M145563 <P>Hi</P><P>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.</P><P>I have a very basic query (below) which outputs the following data from my index...</P><P>index=centredata (domain="*.create.com*" OR domain="*.puzzle.com*") sc_status=200<BR />| table centreid, domain, groupid, userid</P><P>centreid domain&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;groupid&nbsp; &nbsp; userid<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com&nbsp; &nbsp; &nbsp;Student&nbsp; &nbsp; tiyud0<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dbega0<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; oghjr6<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;puzzle.com&nbsp; &nbsp; &nbsp;Student&nbsp; &nbsp;qsadh2<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;puzzle.com&nbsp;&nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;srthg5<BR />0123&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Student&nbsp; &nbsp;klghg2<BR />0456&nbsp; &nbsp; &nbsp; &nbsp;create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;wrdv6<BR />0456 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Student&nbsp; &nbsp;xcgar4<BR />0123 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Student&nbsp; &nbsp;tiyud0<BR />0456 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;oghjr6<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Student&nbsp; &nbsp;klghg2<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ykszd9<BR />0123 &nbsp; &nbsp; &nbsp; create.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dbega0<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;oghjr6<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com &nbsp; &nbsp; Student&nbsp; &nbsp;tiyud0<BR />0123 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;Staff&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sdgxc0</P><P>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...</P><P>centreid domain&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; staff&nbsp; &nbsp; &nbsp; &nbsp;student<BR />0123 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2<BR />0456 &nbsp; &nbsp; &nbsp; create.com&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1<BR />0123 &nbsp; &nbsp; &nbsp; puzzle.com&nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3<BR />0456 &nbsp; &nbsp; &nbsp; puzzle.com&nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0</P><P>Many thanks in advance <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 03 Sep 2020 01:12:49 GMT https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517605#M145563 deton0 2020-09-03T01:12:49Z https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517613#M145566 <P>If you paste this into a search window you can see how this works on your data</P><LI-CODE lang="markup">| 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</LI-CODE><P>All the code up to the table command is to recreate your data.</P><P>It's using the eval statement within the stats dc command to calculate what you need</P><P>&nbsp;</P> Thu, 03 Sep 2020 02:11:04 GMT https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517613#M145566 bowesmana 2020-09-03T02:11:04Z https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517637#M145574 <P>Thank you that worked well <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 03 Sep 2020 05:08:25 GMT https://community.splunk.com/t5/Splunk-Search/Get-distinct-count-of-users-per-user-type-for-each-domain/m-p/517637#M145574 deton0 2020-09-03T05:08:25Z