Splunk Search

Joining results from saved searches

brettcave
Builder

Is it possible to merge the results from different saved searches in splunk? I have come across 2 similar questions while trying to find a result:

how to get a single report by evaluating 3 saved searches and merging results from 3 searches

But basically I have relatively complex searches that I don't want to manage in 1 report with joins or appends. So I have saved 3 searches, each of the 3 searches product the same fields, but I would like to join them together referencing the saved search names, e.g.

searchName="SavedSearch1" | append [ searchName="AnotherSavedSearch ] | append [ searchName="Third Saved Search" ]

I have been trying with savedsearch MySearch | append [ savedSearch AnotherSearch ], but savedsearch doesn't return the results. each search returns a table.

Tags (1)
1 Solution

sideview
SplunkTrust
SplunkTrust

Well... you need to use the savedsearch command, and when you use it you need to put a pipe character in front of it. If you don't put that pipe character, what you'll be doing is searching for the literal word "savedsearch" in your events, and then looking for the matching events that also have a 'searchName' field whose value is "SavedSearch1". Which is quite different.

Here you go:

| savedsearch "SavedSearch1" | append [ | savedsearch "AnotherSavedSearch" ] | append [ | savedsearch "Third Saved Search" ]

However... commonly users head in this direction when the better direction is to merge the logic of the searches by using parentheses and OR's in the initial search clause. If you post your exact search syntax I'd be happy to update my answer and take a stab at doing it without append.

View solution in original post

brettcave
Builder

So now I have a single query without appending results, but I am not sure how to split the results over different rows

| eval touroffset=mvfind(RequestURI,"/tour") 
| eval aboutoffset=mvfind(RequestURI,"/about*")
| eval inviteoffset=mvfind(RequestURI,"/request_invite") 
| eval signupoffset=mvfind(RequestURI,"/signup") 
| eval did_tour=if(isnotnull(touroffset),1,0)
| eval tour_invites=if(isnotnull(touroffset) AND isnotnull(inviteoffset), 1, 0)
| eval tour_signups=if(isnotnull(touroffset) AND isnotnull(signupoffset), 1, 0) 
| eval tour_imports=if(isnotnull(touroffset) AND isnotnull(importoffset), 1, 0) 

| eval no_tour=if(isnull(touroffset),1,0)
| eval notour_invites=if(isnull(touroffset) AND isnotnull(inviteoffset), 1, 0)
| eval notour_signups=if(isnull(touroffset) AND isnotnull(signupoffset), 1, 0)
| eval notour_imports=if(isnull(touroffset) AND isnotnull(importoffset), 1, 0)

Previously after that I ran it through stats to sum up:

| stats sum(did_tours) as "complete_count" sum(tour_invites) as "invite_count" sum(tour_signups) as "signup_count"

but now I can't add all the sums into the same stats command, as i can't split it into different rows. I need to generate stats for the notour metrics and put them into a new row. Not sure of the approach for this, been looking through the commands to try figure out how to pivot or tag or use some multivalue function to get this right.

0 Karma

brettcave
Builder

Here's some samples. I am building reports to see the effectiveness of certain marketing components (viewing tour, visiting informational pages) on a site with regards to user activation actions (requesting invitation, registering, updating profile, etc). The reporting is based custom acccess logs.

A query to show activations relating to visitors who viewed the tour:

eventtype="Analytics" | transaction VisitorID | table VisitorID RequestURI | makemv RequestURI | eval inviteoffset=mvfind(RequestURI,"/request_invite") | eval signupoffset=mvfind(RequestURI,"/signup") | eval touroffset=mvfind(RequestURI,"/tour") | eval did_tours=if(touroffset>=0,1,0) | eval tour_invites=if(touroffset>=0 AND inviteoffset>=0, 1, 0) | eval tour_signups=if(signupoffset>=0 AND touroffset >= 0, 1, 0) | stats sum(did_tours) as "complete_count" sum(tour_invites) as "invite_count" sum(tour_signups) as "signup_count" | eval perc_invites=invite_count/complete_count*100 | eval perc_signups=signup_count/complete_count*100 | eval activation="Did Tour" | table activation complete_count perc_invites perc_signups perc_imports

Now the same query, showing the activation ratios for users that did not view the tour:

eventtype="Analytics" | transaction VisitorID | table VisitorID RequestURI | makemv RequestURI | eval inviteoffset=mvfind(RequestURI,"/request_invite") | eval signupoffset=mvfind(RequestURI,"/signup") | eval touroffset=mvfind(RequestURI,"/tour") | eval no_tours=if(isnull(touroffset),1,0) | eval notour_invites=if(isnull(touroffset) AND inviteoffset>=0, 1, 0) | eval notour_signups=if(signupoffset>=0 AND isnull(touroffset), 1, 0) | stats sum(no_tours) as "complete_count" sum(notour_invites) as "invite_count" sum(notour_signups) as "signup_count" | eval perc_invites=invite_count/complete_count*100 | eval perc_signups=signup_count/complete_count*100 | eval activation="Did Tour" | table activation complete_count perc_invites perc_signups perc_imports

Each of these queries are saved, and I want to build a report that shows row by row the values.

As I was pasting, I realised I could just pipe all the evals from both together. In the first query add in a | eval no_tours=... and so on. But the reason for splitting is to make managing the queries a bit easier. Having 20 evals in a report can be a bit cumbersome.

0 Karma

sideview
SplunkTrust
SplunkTrust

Well... you need to use the savedsearch command, and when you use it you need to put a pipe character in front of it. If you don't put that pipe character, what you'll be doing is searching for the literal word "savedsearch" in your events, and then looking for the matching events that also have a 'searchName' field whose value is "SavedSearch1". Which is quite different.

Here you go:

| savedsearch "SavedSearch1" | append [ | savedsearch "AnotherSavedSearch" ] | append [ | savedsearch "Third Saved Search" ]

However... commonly users head in this direction when the better direction is to merge the logic of the searches by using parentheses and OR's in the initial search clause. If you post your exact search syntax I'd be happy to update my answer and take a stab at doing it without append.

jan_wohlers
Path Finder

I'm searching for a pretty similar problem.

Is it possible so use wildcards for the savedsearch name?

Lets say I want to run 15 similar searches with the name ending with treshold. Is it possible to use "|savedsearch *treshold...." in a different way? now savedsearch is searching for the exact name "*treshold" but I'm searching for "search1_treshold", "search20-treshold"

/Jan

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...