Reporting

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

DEAD_BEEF
Builder

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
0 Karma
1 Solution

Vijeta
Influencer

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.

View solution in original post

Vijeta
Influencer

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.

DEAD_BEEF
Builder

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"
0 Karma

twinspop
Influencer

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.
0 Karma

DEAD_BEEF
Builder

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.

0 Karma

twinspop
Influencer

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

0 Karma

DEAD_BEEF
Builder

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.

0 Karma

Vijeta
Influencer

Are these 8 different saved searches?

0 Karma

DEAD_BEEF
Builder

Yes, these are 8 separate saved searches/reports.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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