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!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...