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"
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"
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"
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)
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.
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.
@ashabc - We've converted the comment to an answer so you can accept it.