Splunk Search

stats return multiple columns where only one occurance exists

jclemons7
Path Finder

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.

0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

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

View solution in original post

yannK
Splunk Employee
Splunk Employee

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
SplunkTrust
SplunkTrust

This should do it

your base search | eventstats distinct_count(firstname) as firstname_count by firstname | where firstname_count = 1
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...