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!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...