topic Re: Help with join command that is not producing correct results in Splunk Search

Help with join command that is not producing correct results

madhuragujarath — Fri, 09 Aug 2019 21:07:06 GMT

Hi I have two searches
search a :

index=*tech* sourcetype=technical_rproxy_access OR  sourcetype=technical_mule_api    NOT statusCode="0" | rex field=source "/appvol/(?[\w\/]+)/logs.*" |  stats  count as GWcount values(apiName) as APIatGW ,values(statusCode) as StatusatGW by MULE

search b:

index=* sourcetype=technical_rproxy_access | rex field=api_name "api\/(?[\w\-]+)*" |rename api_name as apiName|stats count as RPCount values(apiName) as APIatRproxy , values(status) as StatusatRproxy

I have combined two as below :

index=*tech* sourcetype=technical_rproxy_access OR  sourcetype=technical_mule_api    NOT statusCode="0" | rex field=source "/appvol/(?[\w\/]+)/logs.*" |  stats  count as GWcount values(apiName) as APIatGW ,values(statusCode) as StatusatGW by MULE   | join type=left max=0 apiName [ search index=* sourcetype=technical_rproxy_access | rex field=api_name "api\/(?[\w\-]+)*" |rename api_name as apiName|stats count as RPCount values(apiName) as APIatRproxy , values(status) as StatusatRproxy]

I want to join two results such that apiName will be common in both, but my result is not working.

Re: Help with join command that is not producing correct results

richgalloway — Fri, 09 Aug 2019 23:53:18 GMT

The join is expecting field called 'apiName', but neither search's results contain that field. Instead, the stats commands return 'GWcount', 'APIatGW', 'StatusatGW', 'RPcount', 'APIatRproxy', and 'StatusatRproxy'. For join to work, there must be at least one common field name on both sides of the join.

Re: Help with join command that is not producing correct results

madhuragujarath — Wed, 30 Sep 2020 01:42:34 GMT

Hi..Thank you for replying back..in my main search there is one field called apiName..are you saying because I made it values(apiName) as APIatGW I should use APIatGW field ?

in my subsearch there is field called api_Name ..do you think below should work ?

index=tech sourcetype=technical_rproxy_access OR sourcetype=technical_mule_api NOT statusCode="0" | rex field=source "/appvol/(?[\w\/]+)/logs." | stats count as GWcount values(apiName) as APIatGW ,values(statusCode) as StatusatGW by MULE | join type=left max=0 APIatGW [ search index= sourcetype=technical_rproxy_access | rex field=api_name "api\/(?[\w-]+)*" |rename api_name as APIatGW |stats count as RPCount values(apiName) as APIatRproxy , values(status) as StatusatRproxy]

Re: Help with join command that is not producing correct results

madhuragujarath — Wed, 30 Sep 2020 01:42:23 GMT

Hi..Thank you for reply I tried below

index=tech sourcetype=technical_rproxy_access OR sourcetype=technical_mule_api NOT statusCode="0" | rex field=source "/appvol/(?[\w\/]+)/logs." | stats count as GWcount values(apiName) as APIatGW ,values(statusCode) as StatusatGW by MULE | join type=left max=0 APIatGW [ search index= sourcetype=technical_rproxy_access | rex field=api_name "api\/(?[\w-]+)*" |rename api_name as APIatGW|stats count as RPCount values(apiName) as APIatRproxy , values(status) as StatusatRproxy]

Actually what is happening is 1st result gets APIatGW separated by MULE which I want but in for 2nd result its showing no data

I am really stuck with this to get working so thank you for any help 🙂

Re: Help with join command that is not producing correct results

richgalloway — Sat, 10 Aug 2019 11:46:26 GMT

It's important to know the stats command filters fields, much like fields and table. Any field not mentioned in stats will not be available to later commands. This is why your example cannot join on apiName. Try this:

index=*tech* sourcetype=technical_rproxy_access OR sourcetype=technical_mule_api NOT statusCode="0" 
| rex field=source "/appvol/(?[\w\/]+)/logs.*" 
|  stats  count as GWcount values(apiName) as apiName, values(statusCode) as StatusatGW by MULE 
| join type=left max=0 apiName [ search index=* sourcetype=technical_rproxy_access 
  | rex field=api_name "api\/(?[\w\-]+)*" 
  |stats count as RPCount values(api_name) as apiName, values(status) as StatusatRproxy]

Re: Help with join command that is not producing correct results

madhuragujarath — Wed, 30 Sep 2020 01:42:39 GMT

Thank you for checking..:)
I ran below

index=tech sourcetype=technical_rproxy_access OR sourcetype=technical_mule_api NOT statusCode="0"
|rex field=source "/appvol/(?[\w\/]+)/logs." | stats count as GWcount values(apiName) as api_name, values(statusCode) as status by MULE
| join type=outer max=0 apiName [ search index= sourcetype=technical_rproxy_access
| rex field=api_name "api\/(?[\w-]+)*" | rename api_name as apiName
|stats count as RPCount values(apiName) as api, values(status) as StatusatRproxy]

my inner serach is having field called as api_name in data . My outer search has field called as apiName in data . I have passed on apiName to join command ..and then in order to make apiName matches with api_name I have renamed the field . Problem is there are correct results for outer search but inner search is same for each of outer search

e.g. o/p of total command looks like below

MULE GWcount api_name status RPCount StatusatRproxy api
mule1 123324 account--hk-hase-pib-services-papi-proxy 200 201 62035 200 201 /cmb-dbbhase-banking-pa-common-prod-internal-proxy account--hk-hase-pib-services-papi-prod-internal-proxy account--hk-hase-pib-services-papi-prod-proxy api/account--hk-hase-
mobilex-my-accounts-eapi-proxy

I want api and api_name to be matching in line

Re: Help with join command that is not producing correct results

richgalloway — Sun, 11 Aug 2019 12:22:41 GMT

The query you ran does not match the query I provided. How did my query fail? Yours fails because the join command is still looking for the 'apiName' field and the stats commands are still not producing a field by that name.

Examine each stats command and look at each word that follows "as" and "by". Those words are the only field names that will work as joining fields.

Re: Help with join command that is not producing correct results

madhuragujarath — Mon, 12 Aug 2019 00:03:32 GMT

Hi.. I tried query you gave but it shows no results for inner search I mean" stats count as RPCount values(api_name) as apiName, values(status) as StatusatRproxy"

is all blank data ..I am not sure if that is happening because in my first search there os 'by MULE' in main statement ?

MULE GWcount apiName StatusatGW RPCount StatusatRproxy
mule1 61357

account--hk-hase-pib-services-papi-proxy
beluga-accounteligibilitycheck-hk-hsbc-proxy
200
201

added part for result above ..it shows blank for RPCount and StatusatRproxy 😞

Re: Help with join command that is not producing correct results

madhuragujarath — Wed, 30 Sep 2020 01:42:48 GMT

in addition if I ran individually inner query I get result

index=* sourcetype=technical_rproxy_access | rex field=api_name "api\/(?[\w-]+)*" |eval StatusatRproxy=status|stats count as RPCount values(api_name) as apiName, values(status) as StatusatRproxy

But please note that above result just give me single row result what I want is the way outer result is giving me new rows by MULE ( this MULE field is only present in sourcetype=technical_mule not sure if that is causing failure of result )..I want join search to 1st: make new row by MULE 2nd : for each apiName list (e.g pqrs & tuvw ) if inner serach has this api_name matching then it should give count of matching ones

So expecting result in below ..
MULE GWcount apiName StatusatGW RPCount StatusatRproxy
mule1 100 pqrs tuvw 200 400 98 404 200 201
Mule2 50 abcd efgh 200 201 48 302 200

But I am getting
MULE GWcount apiName StatusatGW RPCount StatusatRproxy
mule1 100 pqrs tuvw 200 400

Mule2 50 abcd efgh 200 201

Let me know if I can call any number etc to help further explaning this ..

Re: Help with join command that is not producing correct results

richgalloway — Mon, 12 Aug 2019 12:16:01 GMT

I was so focused on the missing apiName field that I completely missed the missing by clause. Without by, the stats command creates a single set of of statistics, which of course don't match well with numbers grouped by MULE.
Since the second search does not have a MULE field, the join command will not work. Perhaps appendcols will do the job.

Re: Help with join command that is not producing correct results

woodcock — Sun, 10 Nov 2019 23:05:32 GMT

This will get you started:

(index="*tech*" AND (sourcetype="technical_rproxy_access" OR  sourcetype="technical_mule_api") AND NOT statusCode="0") OR
(index="*" AND sourcetype="technical_rproxy_access")
| rex field=source "/appvol/(?<api_name_A>[\w\/]+)/logs.*"
| rex field=api_name "api\/(?<api_name_B>[\w\-]+)*"
| eval api_name_joiner = coalesce(api_name_A, api_name_B)
| eval MULE = coalesce(MULE, "null")
|  stats  count(api_name_A) AS GWcount count(api_name_B) AS RPCount values(api_name_A) AS APIatGW values(api_name_B) AS APIatRproxy values(statusCode) AS StatusatGW BY MULE

Then you might do something like:

... | eval api_name_joiner = coalesce(api_name_A, api_name_B)
| stats Some Stuff Here BY api_name_joiner