topic How to use span with stats? in Splunk Search

How to use span with stats?

m0rt1f4g0 — Tue, 06 Sep 2022 03:38:22 GMT

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?

Re: How to use span with stats?

bowesmana — Tue, 06 Sep 2022 03:54:30 GMT

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

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

Re: How to use span with stats?

m0rt1f4g0 — Tue, 06 Sep 2022 04:07:24 GMT

Hi, I made the modifications:

The query:

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

Re: How to use span with stats?

bowesmana — Tue, 06 Sep 2022 04:22:51 GMT

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")

Re: How to use span with stats?

m0rt1f4g0 — Tue, 06 Sep 2022 17:13:58 GMT

Wow, You´re amazing!

Thank you!