topic Re: How do you build a table from multiple reports/searches? in Reporting

How do you build a table from multiple reports/searches?

DEAD_BEEF — Fri, 14 Dec 2018 19:58:44 GMT

I have 8 separate reports that all return single value results (e.g.: 2500). Each of these reports searches different indexes or source types. How can I combine all the single value results from these 8 separate reports into a single 8-row table panel within a dashboard ?

Report 1 - 25
Report 2 - 47
...
Report 8 - 2719

Dashboard Panel

Summary of Reports
(report name or custom text) - 25
Report 2 - 47
Report 3 - 209273
...
Report 8 - 2719

FINAL WORKING QUERY
Turns out, I needed appendpipe instead of append since all my reports use a stats count for the final single-value result.

| loadjob savedsearch="dead_beef:my_app:report_1"
| eval report="1. Report 1" 
| appendpipe 
    [| loadjob savedsearch="dead_beef:my_app:report_2"
    | eval report="2. Report 2"] 
| appendpipe 
    [| loadjob savedsearch="dead_beef:my_app:report_3" 
    | eval report="3. Report 3"] 
| appendpipe 
    [| loadjob savedsearch="dead_beef:my_app:report_4" 
    | eval report="4. Report 4"] 
| rename report AS Report count AS Count
| table Report Count

Re: How do you build a table from multiple reports/searches?

Vijeta — Fri, 14 Dec 2018 20:08:40 GMT

Are these 8 different saved searches?

Re: How do you build a table from multiple reports/searches?

Vijeta — Fri, 14 Dec 2018 20:20:31 GMT

you can create a dashboard using the query

|savedsearch  <searchname1> |eval report="name1"| append[|savedsearch <searchname2>|eval report="name2"]|append[|savedsearch <searchname3>|eval report="name3"]

and so on till 8th search.

Re: How do you build a table from multiple reports/searches?

DEAD_BEEF — Sat, 15 Dec 2018 02:51:12 GMT

Yes, these are 8 separate saved searches/reports.

Re: How do you build a table from multiple reports/searches?

DEAD_BEEF — Sat, 15 Dec 2018 03:26:03 GMT

Okay, so some of the reports take a while to run so that's why I have them scheduled. Thanks to you clueing me into savedsearch that runs the search again so that wouldn't work for me. But, I did find the command loadjob which loads the results from a saved search. I tried it with only one of my searches and so far it just sits at "Finalizing results." but nothing happens. I'm going to clone one of the reports and just have it output a statistic table rather than a single value visual to see if that works.

My not working SPL

| loadjob savedsearch="dead_beef:my_custom_app:Saved Report One"

Re: How do you build a table from multiple reports/searches?

twinspop — Sat, 15 Dec 2018 04:13:31 GMT

You have the right idea with loadjob... i wonder if your saved searches are in Verbose mode? That will include all results in your saved job. I'm not sure exactly how loadjob would handle it, but i definitely see a difference in dispatch directory when running Verbose vs Smart.

jon@splunklab:~$ du -sh splunk-link/var/run/splunk/dispatch/1544846982.11835
17M splunk-link/var/run/splunk/dispatch/1544846982.11835
jon@splunklab:~$ du -sh splunk-link/var/run/splunk/dispatch/1544847158.11844
117K    splunk-link/var/run/splunk/dispatch/1544847158.11844

The second job dir above is from smart mode. The first was verbose. Same exact data set. Same single line answer ( | stats count by host )

EDIT: well shooooooot. That ain't it. From savedsearches.conf.spec:

display.page.search.mode = [fast|smart|verbose]
* This setting has no effect on saved search execution when dispatched by the
  scheduler. It only comes into effect when the search is opened in the UI and
  run manually.

Re: How do you build a table from multiple reports/searches?

DEAD_BEEF — Sat, 15 Dec 2018 07:32:26 GMT

So ultimately, I was testing reports and they weren't always loading the results despite running the reports hourly. Since all my reports return a single value, I ended up listing all 8 reports with the use of repeated append to put them all in one table. I have scheduled this massive search to remedy the timeliness issues.

Re: How do you build a table from multiple reports/searches?

twinspop — Sat, 15 Dec 2018 15:34:55 GMT

loadjob grabs the latest run. If you're running hourly, and they take a long time, there's a good chance the latest run will still be completing. Try using the artifact_offset argument with loadjob to grab the job 1 step back (and presumably complete).

Re: How do you build a table from multiple reports/searches?

DEAD_BEEF — Wed, 26 Dec 2018 18:55:41 GMT

The issue was that since I was using stats in each report, I needed to add the results of each report via appendpipe as the documentation states, "The appendpipe command is used to append the output of transforming commands, such as chart, timechart, stats, and top." I have modified it using appendpipe and it works perfectly.