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!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...