How to count similar events per 5 minutes in a 60 minute search?

[ZacEsa]

Hi, I'm trying to have a table of failed login attempts. The table shows all failed login attempts for the last 60 minutes but, I want to group similar attempts by device, username used, attempt from and reason for failure.

I've already managed to group them but, I don't want the table to show the count for similar events for the last 60 minutes. Instead, I want it to group by similar events for last 5 minutes WHILE showing all the attempts for the last 60 minutes. I'm not even sure this is possible. I tried bucket _time span=5m but, it still groups by the whole 60 minutes. Here's what I have so far;

stats count, first(_time) as "_time" by acddev, acduser, acdfrom, acdreason
 | table _time acddev acduser acdfrom acdreason count
 | sort -_time

EDIT: I've managed to get the bucket to work by changing stats count, first(_time) as "_time" by acddev, acduser, acdfrom, acdreason to stats count by _time, acddev, acduser, acdfrom, acdreason but, I don't want to show the time in 5 minute intervals, I want to show the time of the latest attempt in that group of events. Is this possible?

[sundareshr · Accepted solution]

Try this

| bin _time span=5m 
| stats count, latest(_time) as "latest login" by _time acddev, acduser, acdfrom, acdreason
| table _time "latest login" acddev acduser acdfrom acdreason count
| sort -_time

[ZacEsa]

Doesn't work, both _time and "latest login" gives out the same value. I believe it's because of the bin/bucket.

[sundareshr]

Have you tried transaction?

... | transaction maxspan=5m acddev, acduser, acdfrom, acdreason | table _time  acddev acduser acdfrom acdreason count

[ZacEsa]

Transaction isn't showing the count. 😕

[sundareshr]

Transactio will create a event_count field that shows the number of events grouped together

[ZacEsa]

Thanks! It works! Can you edit your answer and I'll accept it.