How to build a chronological event list in Splunk

Question

I have an index that lists (among other things) a device, event date, and level (1-4). Devices change levels at random intervals. I need to build a search that shows how long a particular device has been at a certain level, but I can't do a simple count; if a device is at level 1 for three days, goes to level 2 for five days, then back to level 1 for two days, a count will show five days which is obviously incorrect. How can I generate a 'Consecutive days at current level' field?

I need a query that reports Device, Date, Level, and Days at Current Level. Thanks!

Answer 1

I suspect transaction is what you're looking for. This groups events with fields that match (and or other criteria), and reports on the number of events, and duration.

Unfortunately it's unclear as to what your data looks like.... At first I thought this might be an approximation of your data... devices checking in at random times, giving a report of what level they happen to be at at the time:

| makeresults count=100 
| eval decrementsecs=random()%10, decrementday=random()%2,state=random()%4+1,device=random()%10+1,device=case(device=1,"A",device=2,"B",device=3,"C",device=4,"D",device=5,"E",device=6,"F",device=7,"G",device=8,"H",device=9,"I",device=10,"J") 
| streamstats sum(decrementday) as days sum(decrementsecs) as secs 
| eval _time=_time-secs-(24*60*60*days) 
| fields - days secs decrement* 

In this sampling world, then you're looking for the amount of time that's passed since the device reported a state different from the most recent reported state... something like:

<base search>
| streamstats current=f global=f window=2 last(state) as last by device 
| where isnull(last) OR last!=state 
| dedup 2 device 
| transaction device
| eval days_at_current_level=if(eventcount=2,round(duration/(24*60*60)),"unknown")

If however these samples are not samples and are in fact the transitions themselves then:

<base search>
| stats max(_time) as _time latest(state) as level by device
| eval days_at_current_level=round((now()-_time)/(24*60*60))

But then from how you were describing things, it seems like it could be you have X number of devices, each of them report in daily with their state... in which case your data actually probably looks like this:

| makeresults count=10
| streamstats count
| eval _time=_time-(24*60*60*(count-1)), device=split("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P",",")
| mvexpand device
| eval level=random()%4+1

And in this case, as I'm not worried about changes within days, my search could look as simple as:

<base search>
| transaction device level

(A word of caution with transaction, be cognizant of the data you need, and limit the data going into the transaction for performance... if you only need _time, device and level, use fields to get rid of everything else prior to the transaction.... e.g. ... | fields - _raw | fields _time device level By limiting the data before the transaction, you limit the data coming back from the indexers, and you limit the amount of data that the transaction command needs to keep together / keep track of and thus you get better performance)