Splunk Search

How to create a funnel using a subsearch?

Valisha2005
New Member

Hello,
I am trying to create a funnel that first count the number visits to page one and out of those how many went to page2? So if the first result produces 50, I would like to know out of that 50 how many visited the second page?

index="main" sourcetype=abc event_name=spa | rename page.url as url | search url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" | stats dc(session.id) as "customer_support_page_uniqie_visitspage1"

index="main" sourcetype=abc event_name=spa | rename page.url as url | search url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options" | stats dc(session.id) as "customer_support_page_uniqie_visitspage2"

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You can combine the two searches with stats like this.

index="main" sourcetype=abc event_name=spa | rename page.url as url | search (url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" OR url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options") | stats count(eval(match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue") OR match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support"))) as page1 count(eval(match(url, "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2") OR match(url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options"))) as page2 by session.id 
---
If this reply helps you, Karma would be appreciated.

elliotproebstel
Champion

Thanks. I knew there had to be a way to combine those, but I couldn't seem to muster it.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

@elliotproebstel If your problem is resolved, please accept an answer to help future readers.

---
If this reply helps you, Karma would be appreciated.
0 Karma

elliotproebstel
Champion

Thanks, but I'm guessing you were trying to tag @Valisha2005 on this, since it wasn't my question. So @Valisha2005 - if either of our answers solved your problem, please accept one to help others. 🙂

0 Karma

elliotproebstel
Champion

The more efficient way to get these numbers out of Splunk isn't to create a funneled effect using subsearches (although that is possible, and tempting!) but rather to gather all events that would be used in either count and then use stats to calculate the numbers you seek. I'd do it like this:

index="main" sourcetype=abc event_name=spa page.url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR page.url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support" OR page.url="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR page.url = "https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options"| eval visited_1=if('page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-issue" OR 'page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support", 1, NULL), visited_2=if('page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_support%23select-options2" OR 'page.url'="https%3A%2F%2Fwww.pizzaspa.com%2Fcustomer_options", 1, NULL) 
| stats max(visited_1) AS visited_1, max(visited_2) AS visited_2 BY session.id 
| eval visited_both=if(isnotnull(visited_1) AND isnotnull(visited_2), 1, 0) 
| stats sum(visited_1) AS total_visited_first_page sum(visited_both) AS total_visited_both_pages 
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...