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