Splunk Search

Timechart count roll up

Builder

To the powers that be, here is my dilemma. I have a simple query that reviews data in 15 minute blocks and prints out the number of occasions that a “specific sequence” of field names appears. From what I have determined from the documentation, the splunk “dc()” function resets for each 15 minute time block. This means that if a “specific sequence” shows up twice in the first 15 minute block and once in the second, it will show up as one count in the first 15 minute entry in the table and one count in the second 15 minute block on the table. This is close but does not satisfy the requirement I have to implement. I have many 15 minute blocks and each instance of the “specific sequence” needs to be counted ~only~ once, basically the first time it is seen. But I still need to show the data in 15 minute blocks.

So here is the query so far;

index="indexName" source="sourceName" | eval uniqueString = field01.field02 | timechart span=15m dc(uniqueString)

Output for a 60 minute run;

1/29/14 7:30:00.000 AM  8737
1/29/14 7:45:00.000 AM  8721
1/29/14 8:00:00.000 AM  8797
1/29/14 8:15:00.000 AM  5436

Now I know that the “specific sequence” is showing up in multiple 15 minute groupings because I saw them while testing.

I am hoping that some of the brilliant individuals out there in Splunkland have come across this issue before and have a super simple solution that they would like to share with me. Thank you all in advance.

~Ed

0 Karma

Legend

Another approach:

index="indexname" source="sourceName" | eval uniqueString=field01.field02 | eventstats earliest(_time) as earliest_event by uniqueString | timechart span=15m count(eval(_time==earliest_event))

kristian's suggestion is much cleaner imho but this might yield better performance.

EDIT: dc --> count

Legend

Sorry, my brain is working poorly. That should be count(), not dc() (because the distinct filter is done in the eval). Changing my answer to reflect this.

0 Karma

Builder

Ayn, thanks for you answer but here's what I got when I tried it. First, the error below;

Error in 'timechart' command: The eval expression 'dc(eval(time==earliestevent))' must be renamed.

I simple added "as bob" to it and it ran fine, but my results were not what I expected. Each 15 minute grouping in the output had a "bob" count of 1. I was looking for a total like in the results from Kristian's answer above.

Thanks all the same. I really appreciate you taking the time to help on this.
~Ed

0 Karma

Ultra Champion

yes, well, reverse is not really a nice thing..

0 Karma

Ultra Champion

One way of doing that is to use the following search;

sourcetype=your_sourcetype index=your_index 
| reverse
| dedup field01 field02 
| eval uniqueString = field01.field02
| timechart span=15m dc(uniqueString)

/k

UPDATE: Keep reverse before dedup.

Builder

Kristian,
You answer seems to work great, (I still have to test). I tried it and noticed that my counts were going down as the 15 minute time blocks went up - expected because as the "uniqueStrings" are identified, they are not counted in the following 15 minute time block. Thanks.
~Ed

0 Karma

Ultra Champion

see update above. Should work better...

0 Karma