Reporting

Funnel Report based on URL sessions

aa274t
New Member

I am creating a funnel report based on total customer sessions on each url by taling sessionid

www.abc.com www.abc.com/page1 www.abc.com/page1/page2 www.abc.com/page1/page2/page3

I want the number people went through each page with time chart for eg:

Time |Total Session |www.abc.com |www.abc.com/page1 |www.abc.com/page1/page2 |www.abc.com/page1/page2/page3
09:00 | 10 | 10 | 8 | 5 | 2
10:00 | 15 | 15 | 12 | 9 | 7
11:00 | 11 | 11 | 10 | 6 | 5

I have achieved the above using lookup

index=abc sourcetype=test1

| timechart span=1h count as "TotalSession"
|appendcols [index=abc sourcetype=test1 www.abc.com
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com"]

|appendcols [index=abc sourcetype=test1 www.abc.com/page1
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1"]

|appendcols [index=abc sourcetype=test1 www.abc.com/page1/page2
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1/page2"]

|appendcols [index=abc sourcetype=test1 www.abc.com/page1/page2/page3
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1/page2/page3"]

the lookup file is scheduled every 5 mins to append all the sessionid irrespective of page url.

Problem: Cannot use Join as it has limitations of 50K records
The above approach is slow very slow and it is trying to run all the query.

Need a fine tuned optimized solution.

Tags (1)
0 Karma

aa274t
New Member

continued from top

|appendcols [index=abc sourcetype=test1 www.abc.com/page1/page2/page3
| lookup filename.csv SessionID |outputnew sessionid as x_sessionid
| dedup x_sessionid
| timechart span=1h count as "www.abc.com/page1/page2/page3"]

the lookup file is scheduled every 5 mins to append all the sessionid irrespective of page url.

Problem: Cannot use Join as it has limitations of 50K records
The above approach is slow very slow and it is trying to run all the query.

Need a fine tuned optimized solution.

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Tech Talk Recap | Mastering Threat Hunting

Mastering Threat HuntingDive into the world of threat hunting, exploring the key differences between ...

Observability for AI Applications: Troubleshooting Latency

If you’re working with proprietary company data, you’re probably going to have a locally hosted LLM or many ...

Splunk AI Assistant for SPL vs. ChatGPT: Which One is Better?

In the age of AI, every tool promises to make our lives easier. From summarizing content to writing code, ...