Splunk Search

Timechart count roll up

OldManEd
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

Ayn
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

Ayn
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

OldManEd
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==earliest_event))' 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

kristian_kolb
Ultra Champion

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

0 Karma

kristian_kolb
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.

OldManEd
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

kristian_kolb
Ultra Champion

see update above. Should work better...

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...