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?
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")
You use the 'bin' command to specify a time window then stats, i.e.
...
| bin _time span=1h
| stats xxx by _time
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
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")
Wow, You´re amazing!
Thank you!