Splunk Search

Combining 2 queries based on a common value

angersleek
Path Finder

1st query
ns=mynamespace* app_name=A-api API=GET_INITIAL_DATA NAME=*

2nd query
ns=mynamespace* app_name=B-api API=GET_FINAL_DATA NAME=*

I have the following 2 queries. Each is querying a micro service's logs. But I do not want to call them individually and looking to have a single query.
I want to be able to match 1st query against the 2nd query based on name. I am trying to get a % and also total count. Trying to achieve something like the following:

GET_INITIAL_DATA Total count: 10000000
GET_FINAL_DATA count that matched NAME in 1st call : 8000000
Matching call Percentage : 80%
Non Matching call Percentage : 20%

and show that in a chart divided weekly over a 3month period. Is there a way to do this? I am expecting millions of records thus it would not make sense for me to make the first query, get all the names (millions of em) and then use that data to make second call. Please assist. Thank you.

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

ns=mynamespace* (app_name=A-api API=GET_INITIAL_DATA) OR (app_name=B-api API=GET_FINAL_DATA) NAME=*
| stats count dc(API) as APIs by NAME
| stats sum(count) as Total sum(eval(if(APIs=2,count,0))) as Matched
| eval MatchedPercentage=round(Matched*100/Total,2)
| eval NonMatchedPercentage=100-MatchedPercentage

View solution in original post

somesoni2
Revered Legend

Give this a try

ns=mynamespace* (app_name=A-api API=GET_INITIAL_DATA) OR (app_name=B-api API=GET_FINAL_DATA) NAME=*
| stats count dc(API) as APIs by NAME
| stats sum(count) as Total sum(eval(if(APIs=2,count,0))) as Matched
| eval MatchedPercentage=round(Matched*100/Total,2)
| eval NonMatchedPercentage=100-MatchedPercentage

angersleek
Path Finder

Thank you. Do you mind explaining this?

if(APIs=2,count,0)

Is it equal to ---> if APIs = 2 set value as count else set to 0 ?

If yes does that mean it is actual making one call every time to see if NAME exists in both APIs and the value of count is actually always 2?

Cos the NAME value could occur multiple times in either APIs but I only want to see if the NAME came in both APIs and count that as a single match and not care if there are multiple same NAME values across both APIs.

0 Karma

somesoni2
Revered Legend

The first stats (line 2) calculates the count (how many times a NAME appeared) and distinct count of API in which it appeared. (so if it has appeared on just GET_INITIAL_DATA OR just GET_FINAL_DATA, APIs value will be 1, if it has appeared on both API call, it'll have value of 2). Once we get the data for each NAME, next stats (line 3) will further find total count of events and count of NAME which were having a match in both API (or in other words appeared in both APIs). Percentage calculation is standard.

Here I was trying to sum the count of events that's why in line 3 stats we used count field in both places. If you want to count multiple calls from same NAME as 1, you can replace line 3 with this:

| stats count as Total sum(eval(if(APIs=2,1,0))) as Matched
0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>