how to group events based on specific conditions.

[gndivya]

Hi,

I want to group few events based on the success and failure action for a particular user and dest as below. Kindly help in writing a query like this.

Using streamstats I got things like below. Query which I have used here

index=wineventlog_sec* tag=authentication (action=success OR action=failure)
| table _time user dest EventCode action
| sort 0 _time user dest
| streamstats count as attempts by action user dest reset_on_change=true

Time User Dest action attempts
T1 U1 D1 success 1
T2 U1 D1 success 2
T3 U1 D1 failure 1
T4 U1 D1 failure 2
T5 U1 D1 failure 3
T6 U1 D1 success 1
T7 U1 D1 success 2
T8 U1 D1 success 3

How to get the max attempts performed for a particular group of user dest and action (all 3 should be present) like below.

Time User Dest action attempts max_attempts
T1 U1 D1 success 1 2
T2 U1 D1 success 2 2
T3 U1 D1 failure 1 3
T4 U1 D1 failure 2 3
T5 U1 D1 failure 3 3
T6 U1 D1 success 1 3
T7 U1 D1 success 2 3
T8 U1 D1 success 3 3

[to4kawa · Accepted solution]

index=wineventlog_sec* tag=authentication (action=success OR action=failure)
| table _time user dest EventCode action
| sort 0 _time user dest
| streamstats count as attempts by action user dest reset_on_change=true
| streamstats count(eval(attempts=1)) as sessions by user dest
| eventstats count as max_attempts by sessions user dest

[gndivya]

Hi to4kawa,

The above code worked. When the same query is being modified using a tstats search, I am not able to display all the events that happened at a specific point of time, hence the above code is not giving me the actual maximum attempts, instead, it is giving me the maximum attempts of the data coming.

At time T1, 2 events has occurred but I am able to see only one when the tstats query is used.
How to get the max_attempts in this case.

Query used.

| tstats summariesonly count values(Authentication.action) as Authentication.action values(Authentication.src) as Authentication.src values(Authentication.signature_id) as Authentication.signature_id values(Authentication.signature) as Authentication.signature from datamodel=Authentication where (Authentication.action=success OR Authentication.action=failure ) by _time Authentication.user Authentication.dest span=1s
| drop_dm_object_name("Authentication")
| sort 0 user _time dest
| streamstats count as attempts sum(count) as total_count min(count) as min_count by user dest action reset_on_change=true

Data

Time User Dest Count action attempts max_attempts total_count min_count
T1 U1 D1 2 success 1 5 2 2
T2 U1 D1 3 success 2 5 5 2
T3 U1 D1 2 failure 1 7 2 2
T4 U1 D1 3 failure 2 7 5 2
T5 U1 D1 2 failure 3 7 7 2
T6 U1 D1 3 success 1 6 3 3
T7 U1 D1 2 success 2 6 5 2
T8 U1 D1 1 success 3 6 6 1

I hope my question is clear.

[shivanshu1593]

Could you please elaborate as to what "Maximum attempts" is? I believe the SPL that you wrote already is giving you the count of all the attempts made for authentication by a src to a dest. Do you want to compare the attempts made in the time frame that you're running the SPL in vs the whole day's time frame or something else.

[gndivya]

In the second table which I have provided, max_attempts gives the maximum of attempts field wrt success or failure. If there is a failure attempt present after n number of success attempts, then max_attempts for the first success group is n and similarly next group which is failure should have the maximum number of attempts.
I hope my explanation is clear