Splunk Search
Highlighted

Help with join command that is not producing correct results

New Member

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

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

New Member

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=technicalrproxyaccess OR sourcetype=technicalmuleapi 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=technicalrproxyaccess | rex field=apiname "api\/(?[\w-]+)*" |rename apiname as APIatGW |stats count as RPCount values(apiName) as APIatRproxy , values(status) as StatusatRproxy]

0 Karma
Highlighted

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

New Member

Hi..Thank you for reply I tried below

index=tech sourcetype=technicalrproxyaccess OR sourcetype=technicalmuleapi 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=technicalrproxyaccess | rex field=apiname "api\/(?[\w-]+)*" |rename apiname 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 🙂

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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]
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

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

New Member

Thank you for checking..:)
I ran below

index=tech sourcetype=technicalrproxyaccess OR sourcetype=technicalmuleapi 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=technicalrproxyaccess
| rex field=apiname "api\/(?[\w-]+)*" | rename apiname as apiName
|stats count as RPCount values(apiName) as api, values(status) as StatusatRproxy]

my inner serach is having field called as apiname 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 apiname 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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

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

New Member

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 😞

0 Karma
Highlighted

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

New Member

in addition if I ran individually inner query I get result

index=* sourcetype=technicalrproxyaccess | rex field=apiname "api\/(?[\w-]+)*" |eval StatusatRproxy=status|stats count as RPCount values(apiname) 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=technicalmule 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 apiname 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 ..

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma