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.
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
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
This should do it
your base search | eventstats distinct_count(firstname) as firstname_count by firstname | where firstname_count = 1