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
SplunkTrust
SplunkTrust

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 and "Accept as Solution" 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
SplunkTrust
SplunkTrust

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 and "Accept as Solution" is appreciated.
0 Karma

scelikok
SplunkTrust
SplunkTrust

@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 and "Accept as Solution" 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, Karma would be appreciated.
0 Karma

swathiadireddy
Loves-to-Learn Everything

@richgalloway it didnt work

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...