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.
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
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.
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 🙂
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]
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
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.
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 😞
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 ..
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.
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]