How do I aggregate individual searches into a single search?

[jasperlee27]

Hi.

My organization is looking at identifying individual users (UserID) who have failed authentication(logon) >5 times per day for >3 consecutive days straight.

I am able to get the individual days failed logins for a particular user, but how do I aggregate them in one search to create an alert?

I am currently using this search for a particular day.

index="myindex" | lookup agentlookup agent_ID as Agent | search Application=*  Reponse= Reject |stats count by UserID | where count > 5

How do I extrapolate it to find 3 consecutive days straight?

I have also tried to use

index="myindex"| lookup agentlookup agent_ID as Agent | search Application=* Response= Reject | stats count by UserID | where count > 3 | span=1d count by UserID 

to no avail. Have tried reading the documentation, but do not get it.
Thanks in advance for the help!

[somesoni2 · Accepted solution]

Give this a try. Remove lookup (field agent was not used) and move search filters in base search. This will list all the UserID for the day where there where more than 5 login failures.

index="myindex" Application=* Response=Reject | bucket _time span=1d 
| stats count by UserID _time | eval flag=if(count>5,1,0) 
| streamstats window=3 sum(flag) as flag by UserID | where flag=3

[jasperlee27]

Thank you. This worked perfectly! Also, is it possible to display more fields beside the data? (e.g Department, Head-of-Department involved).

The fields are already defined, but In which part do I insert the fields into the search?

Thanks in advance

[cmerriman]

index="myindex" | lookup agentlookup agent_ID as Agent | search Application=* Reponse= Reject|bucket _time span=1d |stats count by UserID _time|where count>5|bucket _time span=3d|stats count as aggCount by UserID _time|where aggCount>3

try something like this.

[jasperlee27]

Thank you! This works but it does not capture repeated occurences