How to Timechart for only the 10 highest counted values?

[dsitek]

I am monitoring access logs for various endpoints (which I denote as path), and in each event I have some data including how long the event took. I have one timechart that monitors which endpoints get called the most, and I am trying to create a timechart that will monitor the max transaction times, but only for the most called endpoints.
The first timechart was very easy:

index=... | timechart count by path useother=false usenull=false

The second search has proven more difficult, as this:

index=... | timechart max(transTime) by path useother=false usenull=false

Only yields the max transaction times regardless of how often the path is called.
I have tried using top and head to restrict the available paths, but to no avail. Is there a way to force timechart to use only the 10 most common paths?

[kamlesh_vaghela · Accepted solution]

Hi @dsitek,

Can you please try where condition is your timechart search? like. WHERE max in top10

My Sample search:

index=_internal | timechart count useother=f by source WHERE max in top10

Please check following link for more information.

http://docs.splunk.com/Documentation/Splunk/7.1.1/SearchReference/Timechart#Where_clause_examples

Thanks
Kamlesh

[DalJeanis]

@kamlesh_vaghela - Awesome. I learned something new today.

[niketn]

Same here. Thanks Kamlesh 🙂

[kamlesh_vaghela]

@DalJeanis

Thanks. 🙂

[DalJeanis]

I'm leaving this here because it shows a way you could use to build more complex in-or-out behavior, but the simple and elegant answer by @kamlesh_vaghela is the correct one to use.

---

You need to do some kind of summary aggregation before the timechart command... in the part of the search you left out... in order to make that happen. The tool of choice for this is often eventstats - which acts like the stats command but leaves the underlying events alone, merely adding the results to the underlying events. Another useful tool is appendpipe - which takes all the results, lets you process them in some way, and then adds them back as new events onto the end of the prior results. And a third useful tool is a subsearch, where you go get certain information and feed that information back as a limit on the outer search.

Which one of the above constructions performs best will be VERY data dependent. So, you try each one and stop when you have acceptable performance, or pick the best of the unacceptable performances.

Now, you need to define what you mean by "the 10 most common paths". You could mean the ones that were the 10 most common today, or this month. you could mean the 10 paths that had the highest AVERAGE use, or the highest PEAK use, or the highest P90 use.

I'm going to assume the total count over the period of the search - which amounts to the same as average - for the purposes of these examples. This first one filters off only the paths you want from your second search..

  index=... | timechart max(transTime) by path useother=false usenull=false
  | search [ your first search | top 10 by path | table path]

That way will work, but it is eliminating the unneeded paths after doing all the work of calculation. Better to do it in the other order.

  index= ... [ your first search | top 10 by path | table path]...
  ...
| timechart max(transTime) by path useother=false usenull=false

If the field path is not on the underlying records, then put the search [...] as early as you can, in order to cull the records.

[kmaron]

I believe you could use a subsearch to return just the top 10 paths before you did the timechart

index=...
[ search index=... | stats count by path | top 10 path | fields path]
 | timechart max(transTime) by path useother=false usenull=false

[DalJeanis]

@kmaron - Change fields to table.

The difference is that fields leaves present all the internal fields that start with underscore, like _time, whereas table gets rid of them.

To see what search index=... | stats count by path | top 10 path | fields path turns into when it hits the end of the square braces, feed it in a base search to the format command.

index=... | stats count by path | top 10 path | fields path | format

You will see that there are other fields being attempted to be matched than just "path".

[kmaron]

that makes so much sense. Thank you!

[dsitek]

This seems like it should be working but the search comes up empty. I am using rex statements to extract both the paths and transaction times. Is this affecting the search in some way?

[kmaron]

It could be. Your best bet would be to try the subsearch on its own first. Make sure it is returning the 10 paths you expect. You will need the rex in both the inner and outer searches.