How to pass result of one query to input as filed for another query?

[kiran007]

I'm trying to pass the result of one query to as input field for another query. Please see the below screen shots and help me out.

query1:

index=* sourcetype="prod-ecp-aks-" "bookAppointmentRequest" "Fname" "Lname" | fields data.req.headers.xcorrelationid.

It will return the co-relation id.

query 2: 

index=*  sourcetype="prod-ecp-aks" "7403cb0a-885d-36ee-0857-fa7e99741bf7" "da_appointment"

It will return the appointments for that co-relation id.

I want to combine these two queries and pass that co-relation id.

Note:-  The co-relation id's are more than one sometime, I need appointment id's for all the co-relation id's.

I gone through so many links, tried join, subquery but didn't get expected result. Please help me out.

Thanks.

[gcusello]

Hi @kiran007,

your need is to filter the results of the second for the results of the first or do you want fields from bothe the searches?

if the first case, you can use a subsearch, pointing attention only to one thing: the fields to use for filtering must be the only output of the subsearch and the field name must be the same oth the main search.

In tiyr case, if you want to filter search2 with the values of data.req.headers.xcorrelationid, the first thing is to be sure that the field "data.req.headers.xcorrelationid" is present also in the second search, otherwise you have to renema it, so you could run something like this:

index=*  sourcetype="prod-ecp-aks" "7403cb0a-885d-36ee-0857-fa7e99741bf7" "da_appointment" [ search 
index=* sourcetype="prod-ecp-aks-" "bookAppointmentRequest" "Fname" "Lname" | fields data.req.headers.xcorrelationid ]
| ...

if instead you want something like a join between the results of both searches, you have to be sure that in both searches there's the same field (e.g. "data.req.headers.xcorrelationid"), then you can run something like this:

index=*  sourcetype="prod-ecp-aks" (("7403cb0a-885d-36ee-0857-fa7e99741bf7" "da_appointment") OR ("bookAppointmentRequest" "Fname" "Lname"))
| stats values(*) AS * BY data.req.headers.xcorrelationid

Ciao.

Giuseppe

[kiran007]

Hi @gcusello ,

Thanks for your reply. 

I need result of first query(co-relation id) should be send as search field for the second query. No need to give that co-relation id manually to the second search. 

[gcusello]

Hi @kiran007,

in this case you have to use the first search as subsearch to filter the results of the second.

Remember to use the same field name in both the searches.

Remember also that a subsearch has always the limit of 50,000 results, so if you exceed this limit you have to use a different approach.

Ciao.

Giuseppe