Add a day counter to a list/table.

[akselsoeb]

Hello 
I am trying to add some logic/formatting to my list of failed authentications.
Heres my search query.

| tstats summariesonly=true count from datamodel="Authentication" WHERE Authentication.action="failure" AND Authentication.user="*" AND Authentication.src="*" AND Authentication.user!=*$ by Authentication.user
| `drop_dm_object_name("Authentication")`
| sort - count
| head 10

I want to make it so that it counts how many consecutive days a user has been on this list, is that possible?

[PickleRick]

It's possible but it's not possible to "add" this to an already done search because at each "pipe point" in the pipeline you lose all the data that's not passed to the next step in the pipeline so you can't "gather" additional data (unless you do some fancy and ineffective things like the map command).

So you'd have to first do | tstats not just as a general count but would have to do the additional clause of "BY _time span=1d" to get a separate data point for each day.  With few different user you probably could do timechart then (you could use prestats=t mode of tstats for that case) and do streamstats count resetting on zero count values for given day.

Otherwise you'd probably have to use streamstats to find last date for each user that showed the count and then do eval to mark consecutive days and another streamstats to count those consecutive days.

Kinda complicated but it's doable.

[akselsoeb]

Thanks for the reply @PickleRick 
It sounds rather complicated with my minimal knowledge, but i will give it a shot. 

[PickleRick]

You can do tstats from datamodel. That's not so minimal 🙂

You simply change your initial change to

| tstats [...] by Authentication.user _time span=1d

That's what gives you data with which you can work further.

| `drop_dm_object_name("Authentication")`

This doesn't hurt us 😉

I admit, the next step is a bit advanced. I won't yet give you a complete solution but I will point you in the right direction

You need to do streamstats to carry over the information when was the last occurrence of given user in those statistics.

| streamstats current=f last(_time) as last by user

This will give you last time the given user was included along with your "current" occurrence.

Now you can see whether the difference is just one day or more which will tell you whether the streak was continuous or not.

That's for start.

[akselsoeb]

I got some help from a co-worker which looks to solve my issue, here is the query that he provided me with. All the credit goes to him btw! 😄

| tstats summariesonly=true count from datamodel="Authentication" WHERE Authentication.action="failure" AND Authentication.user="*" AND Authentication.src="*" AND Authentication.user!=*$ by _time span=1d,Authentication.user
| `drop_dm_object_name("Authentication")`
| sort 0 - _time
| eval date=strftime(_time,"%Y-%m-%d %H:%M:%S")
| transaction user maxpause=24h mvlist=true
| stats max(eventcount) as maxeventcount by user
| where maxeventcount>5
| rename maxeventcount as DaysInRow

Using the transaction command instead (which i need to study abit to understand) and also works around the issue with the sort command limitation of 10000 events.  I will let him know about the approach you're suggesting and see what he thinks about that one. 

[PickleRick]

The sort is unnecessary. By default Splunk returns results in reverse chronological order so they are sorted (ok, the other way around but it's not that much of a problem).

Transaction might indeed work but you have to remember that transaction is a tricky command because it's resource-intensive and has its limitations.