Combining 2 queries based on a common value

[angersleek]

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.

[somesoni2 · Accepted solution]

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]

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.

[somesoni2]

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