topic Re: Join two searches by different field names in Splunk Search

Join two searches by different field names

akhanVG — Mon, 28 Sep 2020 19:40:39 GMT

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?

Re: Join two searches by different field names

dolivasoh — Fri, 24 Apr 2015 17:52:54 GMT

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.

Re: Join two searches by different field names

richgalloway — Fri, 24 Apr 2015 17:56:17 GMT

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]

Re: Join two searches by different field names

akhanVG — Fri, 24 Apr 2015 17:56:37 GMT

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

Re: Join two searches by different field names

akhanVG — Fri, 24 Apr 2015 18:07:34 GMT

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

Re: Join two searches by different field names

masonmorales — Fri, 24 Apr 2015 18:54:12 GMT

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