topic Re: How to stats count but get all values? in Splunk Search

How to stats count but get all values?

secphilomath1 — Thu, 01 Jun 2023 18:13:15 GMT

Here is the search I am trying to do and I hope I can explain this correctly....I am searching for dlp events where there are x events within a period of time for my testing I am using 1 hour...

index=epp "content threat" Policy="Content Aware Protection - Content Threat Detected" `comment("Creating buckets of 10 minutes")` | bin _time span=1h | stats count values(MatchedItem) by _time ClientUser, DestinationDetails, MatchedItem | eval PotentialLeak=if(count >= 10, 1, 0) | search PotentialLeak = 1

What I am trying to get out of this is a table of the following;

_time, ClientUser, DestinationDetails, MatchedItem etc

However, I only see one MatchedItem, not all of them for one user I know there is 12 but only see one of them.

Hope that explains it well enough and appreciate your help.

Re: How to stats count but get all values?

seemanshu — Thu, 01 Jun 2023 18:41:33 GMT

Hi @secphilomath1 ,

As per your search mentioned in the question, after the by clause, you are using the following terms,

_time ClientUser, DestinationDetails, MatchedItem

Here, since "MatchedItem" has been used after the by clause, its only looking for the values(MatchedItem) unique to every "MatchedItem", thus you only see one MatchedItem.

The correct search should be like following,

index=epp "content threat" Policy="Content Aware Protection - Content Threat Detected" `comment("Creating buckets of 10 minutes")` | bin _time span=1h | stats count values(MatchedItem) by _time ClientUser, DestinationDetails | eval PotentialLeak=if(count >= 10, 1, 0) | search PotentialLeak = 1

Hope this helps.

Kindly support the answer, if found helpful!

Re: How to stats count but get all values?

richgalloway — Thu, 01 Jun 2023 19:24:14 GMT

I'm surprised this works at all since the same field name is used in both the values function and in the by clause.

Note that values removes duplicates so if all 12 are the same then you will see only one. Consider using list or this alternative search.

index=epp "content threat" Policy="Content Aware Protection - Content Threat Detected" ```Creating buckets of 10 minutes``` | bin _time span=1h | stats count values(MatchedItem) by _time ClientUser, DestinationDetails | eval PotentialLeak=if(count >= 10, 1, 0) | search PotentialLeak = 1

Re: How to stats count but get all values?

secphilomath1 — Thu, 01 Jun 2023 19:56:44 GMT

Oh yeah, that is exactly what I am seeing now, when I try your search, I still only see the unique values. I will take a look at list

Re: How to stats count but get all values?

secphilomath1 — Thu, 01 Jun 2023 19:59:05 GMT

That has me much closer, thanks!