topic Re: Find unique person count in Splunk in Splunk Search

Find unique person count in Splunk

freephoneid — Sat, 02 Mar 2013 02:36:37 GMT

I've below line in my logs:

INFO #add-person# #new# personId=12 isGroupMember=true
INFO #remove-person# #remove# personId=1 isGroupMember=true
INFO #add-person# #new# personId=13 isGroupMember=false
INFO #add-person# #new# personId=12 isGroupMember=true
INFO #add-person# #new# personId=14 isGroupMember=true
INFO #add-person# #new# personId=15 isGroupMember=true

Log has all other lines as well which I did not show here.

I need to write the query which will give me the count of all persons who are added newly & who are group members. So basically I'm looking for "#add-person# #new# isGroupMember=true" for unique personIds.

For above input, the output should be 3 as there are 3 unique personIds with grupmembership set to true and who are having tag "#add-person# #new#"

How can I do it with Splunk query?

Thanks!

Re: Find unique person count in Splunk

mbenwell — Sat, 02 Mar 2013 04:55:16 GMT

The first part of your search should be to match just the log events you are interested in (isGroupMember=true, add-person new...).

Then look into the stats command

Re: Find unique person count in Splunk

lguinn2 — Sat, 02 Mar 2013 09:17:21 GMT

Try this

"#add-person#"  "#new#" isGroupMember=true
| stats dc(personId) as NewMembersCount

For more details, you could do this

"#add-person#"  "#new#" isGroupMember=true
| stats dc(personId) as NewMembersCount values(personId) as "New Member Ids"

Re: Find unique person count in Splunk

freephoneid — Sun, 03 Mar 2013 07:54:18 GMT

Hi, In your query, you're looking for "#add-person#" "#new#" isGroupMember=true but I believe this first part itself will not return any results as the personId= string is present in between. When we search in Splunk, it'll look for strings in order, right? If so, the first part itself won't return any results. Can you please comment?

Re: Find unique person count in Splunk

mbenwell — Sun, 03 Mar 2013 08:42:52 GMT

lguinn's search will return every record that contains #add-person#, #new# and has isGroupMember set to true.

If you surrounded the entire string in quotes (i.e. "#add-person# #new# isGroupMember-true") then no, it wouldn't match anything, because it would literally search for that string. But that is not what lguinn has given you.

In lguinn's example, both strings have been quoted, so it will match each individual string regardless of order.

If you're not sure if the search is right, run the search without the stats command and see what results you get back.

Re: Find unique person count in Splunk

lguinn2 — Sun, 03 Mar 2013 11:05:36 GMT

mbenwell is right - the order of terms is not important. The search in my answer has three different and independent terms.