combining 2 searches - with dynamic urls into 1

[super_edition]

Hello Everyone,

I have 2 splunk search queries

query-1

index="my_index"  kubernetes_namespace="my_ns"  kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" "/my_service/user-registration"
| dedup req_id
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2) 
| eval nintyPerc=round(nintyPerc,2)

output 

url	method	kubernetes_cluster	hits	avgResponse	nintyPerc
/my_service/user-registration	POST	LON	11254	112	535

query-2

index="my_index"  kubernetes_namespace="my_ns"  kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" "/my_service/profile-retrieval"
| eval normalized_url="/my_service/profile-retrieval"
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by normalized_url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2) 
| eval nintyPerc=round(nintyPerc,2)

output

url	method	kubernetes_cluster	hits	avgResponse	nintyPerc
/my_service/profile-retrieval	GET	LON	55477	698	3423

The query-2 returns multiple urls like below but belongs to same endpoint:

/my_service/profile-retrieval/324524352

/my_service/profile-retrieval/453453?displayOptions=ADDRESS%2CCONTACT&programCode=SKW

/my_service/profile-retrieval/?displayOptions=PREFERENCES&programCode=SKW&ssfMembershipId=00408521260

Hence I used eval function to normalized them

eval normalized_url="/my_service/profile-retrieval"

How do I combine both queries to return as simplified output

url	method	kubernetes_cluster	hits	avgResponse	nintyPerc
/my_service/user-registration	POST	LON	11254	112	535
/my_service/profile-retrieval	GET	LON	55477	698	3423

Highly appreciate your help!!

[gcusello]

Hi @super_edition ,

you could try something like this (see my approach and adapt it to your data):

index="my_index"  kubernetes_namespace="my_ns"  kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" ("/my_service/user-registration" OR "/my_service/profile-retrieval")
| eval url=if(searchmatch("/my_service/profile-retrieval"),"/my_service/profile-retrieval","/my_service/user-registration")
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2) 
| eval nintyPerc=round(nintyPerc,2)

Ciao.

Giuseppe

[Prewin27 · Accepted solution]

@super_edition 
You can either use append or eval match condition to combine both for your scenario

using append

(
index="my_index" kubernetes_namespace="my_ns" kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" "/my_service/user-registration"
| dedup req_id
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2)
| eval nintyPerc=round(nintyPerc,2)
)
| append [
search index="my_index" kubernetes_namespace="my_ns" kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" "/my_service/profile-retrieval"
| eval url="/my_service/profile-retrieval"
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2)
| eval nintyPerc=round(nintyPerc,2)
]
| table url method kubernetes_cluster hits avgResponse nintyPerc

combined

index="my_index" kubernetes_namespace="my_ns" kubernetes_cluster!="bad_cluster" kubernetes_deployment_name="frontend_service" msg="RESPONSE" ("/my_service/user-registration" OR "/my_service/profile-retrieval")
| eval url=if(match(url, "^/my_service/user-registration"), "/my_service/user-registration",
if(match(url, "^/my_service/profile-retrieval"), "/my_service/profile-retrieval", url))
| dedup req_id
| stats count as hits avg(responseTime) as avgResponse perc90(responseTime) as nintyPerc by url method kubernetes_cluster
| eval avgResponse=round(avgResponse,2)
| eval nintyPerc=round(nintyPerc,2)
| table url method kubernetes_cluster hits avgResponse nintyPerc

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[super_edition]

Thanks @Prewin27 -  it worked as expected and was fast enough.