I have a dashboard with too many searches. How to best split these searches using a base search and post-process searches?

[kmaron]

I have a dashboard that has way too many searches on it so I was trying to split it up using a base search and the post-process searches. However I'm not having any luck and I'm ready to throw things so I'm hoping someone here can help.

I have 10 different queries that look like this - only for each one the %TABLENAME% is different to give me a separate search per table.

index=ecm sourcetype="sql" source="%TABLENAME%" QUEUE_DEPTH_PROD 
 | fields _time QUEUE_DEPTH_PROD
 | eval mytimes=mvappend(_time,relative_time(_time,"-10m"),relative_time(_time,"+10m"))
 | mvexpand mytimes
 | search mytimes < now()
 | eval _time=mytimes
 | timechart  avg(QUEUE_DEPTH_PROD) span=15m

I've tried putting the base search and timechart without the tablename:

index=ecm sourcetype="sql" QUEUE_DEPTH_PROD | timechart  avg(QUEUE_DEPTH_PROD) span=15m

And then have just the source in the post-process search but that doesn't seem to work either or I'm doing it wrong.

Any help would be greatly appreciated.

[koshyk · Accepted solution]

Your basesearch should be

index=ecm sourcetype="sql" QUEUE_DEPTH_PROD 
| stats count by _time QUEUE_DEPTH_PROD  TABLENAME

Then your postprocess searches should be something like

<base-search>
    | search TABLENAME="myfirstTable"
    |eval mytimes=mvappend(_time,relative_time(_time,"-10m"),relative_time(_time,"+10m"))
    | mvexpand mytimes
    | search mytimes < now()
    | eval _time=mytimes
    | timechart  avg(QUEUE_DEPTH_PROD) span=15m

[AshimaE]

Why is the stats count part put in the Base search in the answer.

[koshyk]

stats is very efficient and the base-search will reduce the dataset from millions to handful. It is all about efficiency . You can extend the fields to be just enough to satisfy your "pre-process" searches

[kmaron]

That's exactly what I was looking for! So close yet still so far. Thank you!

[AshimaE]

And how do we decide which part has to be put in the base and the post processing search..

[niketn]

You can take advantage of Post Processing when you use transforming or statistical functions in your base search. You should not be trying to send raw data through Post Process.

http://docs.splunk.com/Documentation/Splunk/latest/Viz/Savedsearches#Post-process_limitations

You can create a base search with combination all 10 sources in the single base search and perform sitimechart. Finally you can created 10 base searches one for each Panel. However, you would need to test the performance of your dahsboard with and without post-processing. As it has been mentioned in the document sometimes you are better of running same search multiple time.

index=ecm sourcetype=sql source=source1 source=source2 ...  source=source10 
| <your remainining search>
| sitimechart span=15m avg(QUEUE_DEPTH_PROD) by source