Splunk Search

how to group events based on specific conditions.

gndivya
Explorer

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

0 Karma
1 Solution

to4kawa
Ultra Champion
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

View solution in original post

0 Karma

to4kawa
Ultra Champion
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
0 Karma

gndivya
Explorer

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.

0 Karma

shivanshu1593
Builder

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.

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###

gndivya
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...