Splunk Search

How to use span with stats?

m0rt1f4g0
Explorer

I have a table with the next information:

Fecha
31/08/2022 16:16:43
31/08/2022 16:19:48
31/08/2022 16:16:34
31/08/2022 16:16:40

I now want to group these infor  by day and hour start and hour end,  for example:

31/08/2022 16:16:34 - 16:19:48

The query:

index=o365 sourcetype=o365:management:activity Operation=UserLoginFailed user=
|stats count, values(user) as Usuario by _time
|eval Fecha = strftime(max(_time), "%d/%m/%Y %H:%M:%S")
|rename count as Contador
|sort -Contador
|table Fecha, Usuario, Contador

Can you help me, please?

Labels (3)
Tags (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

If you are looking to get the counts/users within the 1h window, but also the min/max time of those events, then this will do it

 

... your search...
| eval t=_time
| bin _time span=5m
| stats min(t) as min max(t) as max count, values(user) as Usuario by _time
| eval Fecha=strftime(min, "%d/%m/%Y %T")." - ".strftime(max, "%T")

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

You use the 'bin' command to specify a time window then stats, i.e.

...
| bin _time span=1h
| stats xxx by _time

 

0 Karma

m0rt1f4g0
Explorer

Hi, I made the modifications:

The query:

index=o365 sourcetype=o365:management:activity Operation=UserLoginFailed user=esancheza*
|bin _time span=1h
|stats count, values(user) as Usuario by _time
|eval Fecha = strftime(max(_time), "%d/%m/%Y %H:%M:%S")
|rename count as Contador
|sort -Contador
|table Fecha, Usuario, Contador

 

But the result is not as expected

Result:

Fecha
31/08/2022 16:00:00

I would like the next result:

31/08/2022 16:16:34 - 16:19:48

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If you are looking to get the counts/users within the 1h window, but also the min/max time of those events, then this will do it

 

... your search...
| eval t=_time
| bin _time span=5m
| stats min(t) as min max(t) as max count, values(user) as Usuario by _time
| eval Fecha=strftime(min, "%d/%m/%Y %T")." - ".strftime(max, "%T")

 

m0rt1f4g0
Explorer

Wow, You´re amazing!

 

Thank you!

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...