Splunk Search

Help with join command that is not producing correct results

madhuragujarath
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

woodcock
Esteemed Legend

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
0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

madhuragujarath
New Member

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 🙂

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

madhuragujarath
New Member

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

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

madhuragujarath
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

madhuragujarath
New Member

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 ..

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

madhuragujarath
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=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]

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...