stats return multiple columns where only one occurance exists

[jclemons7]

Hello,

I'm not really sure how to appropriately describe my query need, which is why I think I can't find what I'm looking for on the Google.. but hopefully one of you Splunksperts will be able to wade through the my rambling and give me an answer.. 🙂

Essentially, I need a query that returns me multiple columns where there is only one instance of a value in the data. So, for instance.. I have a query that tells me when a single (and only a single) instance of a value appears.. e.g..

bob,roberts,31,5'11
bob,johnson,26,6'2"
tom,obfuscation,42,6'1"
bill,payer,37,6'0"
rick,singleton,42,5'10"
tom,secondary,45,6'1"
bill,dodger,85,5'8"

the query I have now (stats distinct_count(firstname) as firstname_count by firstname | where firstname_count = 1) works, but only returns:

rick

because it's finding the single instance of that field.. and that's exactly what I want it to do.. but I want to extend it to return the values that accompany as well to get the full row..

bill,payer,37,6'0"

what I can't figure out is how to return ONLY the rows where there is a unique value in only that one column.. I don't want the query to consider the entire row to determine uniqueness.. only the column "of interest". Normally in SQL I would just do a nested join and query out unique values separately then join back to the same data set, but I can't figure out how to do that in Splunk.

Thanks in advance.

[yannK · Accepted solution]

add more functions to your stats command for your fields be passed to the next command.
if your other fields are lastname, age,size

 ... | stats distinct_count(firstname) as firstname_count  values(lastname) AS lastname  values(age) AS age values(size) AS size by firstname | where firstname_count = 1

[somesoni2]

This should do it

your base search | eventstats distinct_count(firstname) as firstname_count by firstname | where firstname_count = 1