topic Re: Results of a join search in Splunk Search

Results of a join search

brywilk_umich — Mon, 28 Sep 2020 16:47:25 GMT

Hello

I have the below search and it seems to work fine for the most part. The problem is that if search 2 does not have any results the information gathered from search 1 is not displayed. The log line with the simta_ublstatus field can either exist or not exist for the user. There's likely a better way to do this so Im open for suggestions.

index=collaboration tag::host="outbound" sourcetype="simta"
| geoip simta_client_ip
| stats dc(simta_client_ip_country_name) as country_count values(simta_client_ip_country_name) as country_name values(simta_client_ip_city) as country_city values(simta_client_ip) as IPAddress by simta_authuser
| rex field=simta_authuser mode=sed "s/@umich.edu*//"
| sort by country_count
| search country_count>1
| join simta_authuser [search index=collaboration tag::host="outbound" sourcetype="simta" UBL
| eval simta_authuser = simta_ublauthuser
| fields simta_authuser simta_ublstatus ]

Re: Results of a join search

somesoni2 — Wed, 04 Jun 2014 16:28:50 GMT

Try this

index=collaboration tag::host="outbound" sourcetype="simta" | geoip simta_client_ip | stats dc(simta_client_ip_country_name) as country_count values(simta_client_ip_country_name) as country_name values(simta_client_ip_city) as country_city values(simta_client_ip) as IPAddress by simta_authuser | rex field=simta_authuser mode=sed "s/@umich.edu*//" | sort by country_count | search country_count>1 | join type=left simta_authuser [search index=collaboration tag::host="outbound" sourcetype="simta" UBL | eval simta_authuser = simta_ublauthuser | fields simta_authuser simta_ublstatus ]

Try this for second requirement.

index=collaboration tag::host="outbound" sourcetype="simta" | geoip simta_client_ip | stats dc(simta_client_ip_country_name) as country_count values(simta_client_ip_country_name) as country_name values(simta_client_ip_city) as country_city values(simta_client_ip) as IPAddress by simta_authuser | eval shouldShow=[search index=collaboration tag::host="outbound" sourcetype="simta" "*SASL(-13*" | stats count | eval result=if(count>0,"\"No\"","\"Yes\"") | return $result] | where shouldShow="Yes" | fields - shouldShow| rex field=simta_authuser mode=sed "s/@umich.edu*//" | sort by country_count | search country_count>1 | join type=left simta_authuser [search index=collaboration tag::host="outbound" sourcetype="simta" UBL | eval simta_authuser = simta_ublauthuser | fields simta_authuser simta_ublstatus ]

Re: Results of a join search

richgalloway — Wed, 04 Jun 2014 16:35:47 GMT

I see two options:

1) Add a 'fillnull value="null" simta_ublauthuser' statement before 'eval simta_authuser...'

2) Change the join to an outer join so rows that don't match are still returned.

Re: Results of a join search

brywilk_umich — Wed, 04 Jun 2014 17:45:20 GMT

that did the trick! Now Im wondering is there a way to combine that and do a additional search by the simta_authuser so that if it finds a log line with SASL(-13) it will not display any of the results from above

Re: Results of a join search

somesoni2 — Wed, 04 Jun 2014 17:58:54 GMT

Try the updated answer. The condition is added in the middle (subsearch after "eval shouldShow...")