Splunk Search

Join two searches by different field names

Path Finder

I have a search I'm running which now works fine

index="ecom" eventName=pageLoad | regex referrer="^http://www.example.com/these-files/*" | rex field=referrer "(?.*?)\?" | rex field=new_referrer mode=sed "s/^http:\/\/www.example.com/ /g" | stats count as clicksOut by new_referrer | rename new_referrer as currentPage

This gets me results such as
currentPage - clicksOut
/these-files/abc - 4
/these-files/def - 5

etc.

I am now trying to join a separate search based on the currentPage field

| join currentPage [search index="ecom" eventName=pageLoad | stats count as pageViews by currentPage]

If I run this search I get back a variety of results (as should be)
/ - 100
/these-files - 400
/these-files/abc - 10
/these-files/def - 5

Ideally when the join occurs I'd have a list of all clicksOut and pageViews
currentPage - clicksOut - pageViews
/these-files/abc - 4 - 10
/these-files/def - 5 - 5

When I run the full search below I get no results though - any ideas?

index="ecom" eventName=pageLoad | regex referrer="^http://www.example.com/these-files/*" | rex field=referrer "(?.*?)\?" | rex field=new_referrer mode=sed "s/^http:\/\/www.example.com/ /g" | stats count as clicksOut by new_referrer | rename new_referrer as currentPage | join currentPage [search index="ecom" eventName=pageLoad | stats count as pageViews by currentPage]

Tags (1)
0 Karma

Influencer

If this works for you, please click "Accept Answer"...

index="ecom" eventName=pageLoad 
| regex referrer="^http://www.example.com/these-files/*" 
| rex field=referrer "(?.*?)?" 
| rex field=new_referrer mode=sed "s/^http://www.example.com/ /g"
| stats count as pageViews, count(eval(new_referrer="*")) as clicksOut by currentPage
| fillnull value=0 pageViews clicksOut 
0 Karma

SplunkTrust
SplunkTrust

Your subsearches don't have a currentPage field - they have pageviews. That aside, I think you'd have better results with the appendcols command.

index="ecom" eventName=pageLoad | regex referrer="^http://www.example.com/these-files/*" | rex field=referrer "(?.*?)?" | rex field=new_referrer mode=sed "s/^http://www.example.com/ /g" | stats count as clicksOut by new_referrer | rename new_referrer as currentPage | appendcols [search index="ecom" eventName=pageLoad | stats count as pageViews by currentPage] 
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder

No dice - I tried this but the clicksOut always comes up as blank.

0 Karma

Contributor

You shouldn't need to do the same join twice here and you can use join type=left to still get your original result set even if noting matched. Try removing the last join and change the first one to join type=left and let us all know the results. It may be that the joined result set has no matches or doesn't exist.

Path Finder

Oh shoot that was a mistake in formatting there is only one join but I'll see if left works

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!