Search output of a stats command

[ashabc]

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"

[worshamn · Accepted solution]

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"

[worshamn]

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)

[ashabc]

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.

[ashabc]

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]

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