Security & the Enterprise
Much secured. So patch!

Combine two data sources to display the results as column1 from the first data source and column2 from the second data

swathiadireddy
Loves-to-Learn Everything

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

0 Karma

scelikok
Champion

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;

 

If this reply helps you an upvote is appreciated.
0 Karma

swathiadireddy
Loves-to-Learn Everything

@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 

0 Karma

swathiadireddy
Loves-to-Learn Everything

@scelikok Cisco is not a field so it won't work

0 Karma

scelikok
Champion

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? 

If this reply helps you an upvote is appreciated.
0 Karma

scelikok
Champion

@swathiadireddy,

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
If this reply helps you an upvote is appreciated.
0 Karma

swathiadireddy
Loves-to-Learn Everything

@scelikok 

 

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

0 Karma

swathiadireddy
Loves-to-Learn Everything

@scelikok The values of query1 should be the input of query2

 

so need to use the sub search. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

swathiadireddy
Loves-to-Learn Everything

@richgalloway it didnt work

0 Karma