Splunk Search

Search output of a stats command

ashabc
Contributor

I have a search like below

| stats values(EndPointMatchedProfile) by EndPointMACAddress

Where each EndPointMACAddress may have one or more EndPointMatchedProfile values.

How do I find out EndPointMACAddress that has only one EndPointMatchedProfile value and that value is "Unknown". I do not want to return EndPointMACAddress that has two or more EndPointMatchedProfile values and one of them is "Unknown"

0 Karma
1 Solution

worshamn
Contributor

Just add a count to it, then use a where clause to find the condition you are looking for.

| stats values(EndPointMatchedProfile) AS EndPointMatchedProfile count by EndPointMACAddress
|where count =="1" AND EndPointMatchedProfile=="Unknown"

View solution in original post

0 Karma

worshamn
Contributor

Just add a count to it, then use a where clause to find the condition you are looking for.

| stats values(EndPointMatchedProfile) AS EndPointMatchedProfile count by EndPointMACAddress
|where count =="1" AND EndPointMatchedProfile=="Unknown"

0 Karma

worshamn
Contributor

Actually I'm not sure that the stats count will be the right count, you may instead before the where statement do an eval:

|eval count = mvcount(EndPointMatchedProfile)

0 Karma

ashabc
Contributor

Thank you Worshamn for responding so quickly. I tried the command you suggested, I don't think where command is working correctly. If I put where==2 it does not return any result. However, I know for sure, that there are multiple values of EndPointMatchedProfile for many EndPointMACAddress with two values and one of EndPointMatchedProfile is unknown.

0 Karma

ashabc
Contributor

In fact, it worked. I had to use AS command in stats
https://answers.splunk.com/answers/154916/how-to-search-and-filter-based-on-fields-created-by-stats....

| stats values(EndPointMatchedProfile) AS profile by EndPointMACAddress | eval pcount=mvcount(profile) | where pcount <2 AND (profile=="Unknown" OR profile="")

Thank you worshamn, much appreciated.

DalJeanis
SplunkTrust
SplunkTrust

@ashabc - We've converted the comment to an answer so you can accept it.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...