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
Legend

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

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...