How to create a funnel using a subsearch?

[Valisha2005]

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"

[richgalloway]

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 

[elliotproebstel]

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

[richgalloway]

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

[elliotproebstel]

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

[elliotproebstel]

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