I am trying to combine the both the data sources and display the results with columns Name, user-ID, email ID
Note: user-ID is common in the both the queries.
Query1: (This data has the information of the users whose sessions are closed)
index=cert sourcetype="cisco*" | search "closed" | table user-ID
Query2: (This data has the information of the users like Name, email ID...)
index=cert source=identity
| table Name user-ID Email Title
Output:Query1
User-ID
XYZ
ABC
Output:Query2
Name User-ID Email Title
Brian XYZ Brian@gmail.com Programmer
Ashley abc Ashley@gmail.com Manager
When the both the queries are combined the output should be like below.
Final expected output:
Name User-ID Email Title
Brian XYZ Brian@gmail.com Programmer
Hi @swathiadireddy,
I missed your filter condition; I think below should work for you without
index=cert (sourcetype="cisco*" closed) OR source=identity
| eval cisco=if(sourcetype=="cisco*","1",null())
| stats values(Name) as Name values(Email) as Email values(Title) as Title values(cisco) as cisco by user-ID
| where isnotnull(cisco)
| fields - cisco
subsearch;
@scelikok it didn't work.
the condition should be when the user in the query1 matches with the user in the query2
then display the user Name, email ID and user-ID
@scelikok Cisco is not a field so it won't work
Did you try the query? I am creating that temporary field using eval. It is only to mark events coming from query 1. Please show me the output of my search result?
Please try below;
index=cert (sourcetype="cisco*" closed) OR source=identity
| stats values(Name) as Name values(Email) as Email values(Title) as Title by user-ID
Output:Query1
User-ID
XYZ
ABC
Output:Query2
Name User-ID Email Title
Brian XYZ Brian@gmail.com Programmer
Ashley abc Ashley@gmail.com Manager
When the both the queries are combined the output should be like below.
Final expected output:
Name User-ID Email Title
Brian XYZ Brian@gmail.com Programmer
Try this
index=cert source=identity [search index=cert sourcetype="cisco*" | search "closed" | return user-ID]
| table Name user-ID Email Title
The subsearch (the bits with []) runs first and returns a list of closed user-ID values to the main search which finds those user-IDs in the identity list.
@richgalloway it didnt work