How to search the rate of events in indexes A, B, C, D to to compare with the rate of events in index D?

[lbogle]

Hello Splunkers,

I am trying to find a way to determine the rate of events of a single index compared to all non-internal indexes. There are numerous indexes, and so I am going to have to use a base search of
index=* NOT index=D | timechart span=1h count but then I need to overlay index=D | timechart span=1h count over the top of it for the timechart. I'm guessing I need to do an appendcols with the index=D data, but am unsure of syntax. Any suggestions?
Thanks!

[somesoni2]

The answer by @Sideview is pretty much what you want. But since you're looking for just the event count by index, you can try a much faster way using tstats command, something like this

| tstats count WHERE index=* by index _time span=1h | eval whichIndex=if(index="D","D","All Others") | timechart span=1h sum(count) by whichIndex

[chanmi2]

You may try this:

index=* NOT index=D | timechart span=1h count| join _time [index=D | timechart span=1h count]

or

index=* | eval isD=if(index=="D", "T", "F") | timechart span=1h count(eval(isD="T")) as "D" count(eval(isD="F")) as "Others"

[sideview]

You can use the eval command to create a new field whose value is "B" vs "All Others, just like so:

index=* | eval whichIndex=if(index="B","B","All Others") | timechart count by whichIndex

And this is a far better way to do it than to use append or appendcols. In general if you have a use case where you feel like you need the join, append, or appendcols command, take a step back and look for a way to get the data off disk in only one fell swoop. The operative metaphors in join/append are very familiar to old SQL hands, but eval and stats are Splunk's most important commands by far.

A good flowchart and breakdown of overall Aggregation logic can be found here.

http://docs.splunk.com/Documentation/Splunk/6.2.5/Search/Abouteventcorrelation

[lbogle]

Thanks sideview,
I appreciate the succint query. Very Splunky. 🙂
Thanks also for the reference material.
I was able to get the query working in two different ways and they both returned the same numbers which was cool.
Thanks!

[ppablo]

Hi @lbogle

Could you actually share the 2 different searches that both worked for other users to learn and see what you did? Also, be sure to click "Accept" below @sideview's answer to resolve the post. Thanks!