Splunk Search

How do I make a Splunk query that generates stats grouped by account name?

bm1391
New Member

Here is my current query:

index=wineventlog sourcetype=WinEventLog:Security EventCode=4625 | rex ".*Account\sName:\s+(?<account>\S+)" | eval Date=strftime(_time, "%Y/%m/%d")|stats count by Date,account,host|eventstats median(count) as median, p30(count) as p30,  p70(count) as p70,mean(count) as mean  | eval iqr=p70-p30 | eval xplier=2 | eval low_lim=median-(iqr*xplier) | eval high_lim=median + (iqr*xplier) | eval anamoly = if(count<low_lim OR count>high_lim, count,0)

I am trying to get all failed logons grouped by account name on a daily basis, and generate statistics so that future behavior can be identified as anomalous. This query "works", but this part of the query...

|eventstats median(count) as median, p30(count) as p30,  p70(count) as p70,mean(count) as mean  | eval iqr=p70-p30 | eval xplier=2 | eval low_lim=median-(iqr*xplier) | eval high_lim=median + (iqr*xplier) | eval anamoly = if(count<low_lim OR count>high_lim, count,0)

...generates the stats on all the accounts and not only on the specific account.

Early on in the query, I group it by account name, Date and host, but after eventstats, it generates statistics on all the accounts as if they are the same. I think this is very easy to fix but I can't seem to figure it out.

Thanks!

0 Karma
1 Solution

kmaron
Motivator

you should just be able to add by account at the end of your eventstats

index=wineventlog sourcetype=WinEventLog:Security EventCode=4625 
| rex ".*Account\sName:\s+(?<account>\S+)" 
| eval Date=strftime(_time, "%Y/%m/%d") 
| stats count by Date,account,host 
| eventstats median(count) as median, p30(count) as p30, p70(count) as p70,mean(count) as mean by account 
| eval iqr=p70-p30 
| eval xplier=2 
| eval low_lim=median-(iqr*xplier) 
| eval high_lim=median + (iqr*xplier) 
| eval anamoly = if(count<low_lim OR count>high_lim, count,0)

View solution in original post

0 Karma

kmaron
Motivator

you should just be able to add by account at the end of your eventstats

index=wineventlog sourcetype=WinEventLog:Security EventCode=4625 
| rex ".*Account\sName:\s+(?<account>\S+)" 
| eval Date=strftime(_time, "%Y/%m/%d") 
| stats count by Date,account,host 
| eventstats median(count) as median, p30(count) as p30, p70(count) as p70,mean(count) as mean by account 
| eval iqr=p70-p30 
| eval xplier=2 
| eval low_lim=median-(iqr*xplier) 
| eval high_lim=median + (iqr*xplier) 
| eval anamoly = if(count<low_lim OR count>high_lim, count,0)
0 Karma

bm1391
New Member

Yup that worked! Thanks

0 Karma

bm1391
New Member

Switch the query to:

index=wineventlog sourcetype=WinEventLog:Security EventCode=4625 | rex ".*Account\sName:\s+(?\S+)" | eval Date=strftime(_time, "%Y/%m/%d")|stats count by Date,account,host|eventstats median(count) as median, p30(count) as p30,  p70(count) as p70,mean(count) as mean **by account**  | eval iqr=p70-p30 | eval xplier=2 | eval low_lim=median-(iqr*xplier) | eval high_lim=median + (iqr*xplier) | eval anamoly = if(counthigh_lim, count,0)

and It works...

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...