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

[bm1391]

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!

[kmaron · Accepted solution]

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)

[bm1391]

Yup that worked! Thanks

[bm1391]

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...