topic Re: Search output of a stats command in Splunk Search

Search output of a stats command

ashabc — Mon, 28 Aug 2017 01:27:49 GMT

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"

Re: Search output of a stats command

worshamn — Mon, 28 Aug 2017 01:43:09 GMT

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"

Re: Search output of a stats command

worshamn — Mon, 28 Aug 2017 01:46:53 GMT

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)

Re: Search output of a stats command

ashabc — Mon, 28 Aug 2017 03:49:11 GMT

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.

Re: Search output of a stats command

ashabc — Mon, 28 Aug 2017 04:13:02 GMT

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.html

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

Thank you worshamn, much appreciated.

Re: Search output of a stats command

DalJeanis — Mon, 28 Aug 2017 05:02:48 GMT

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