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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...