Splunk Search

How to list all events from two separate searches if the second search depends on the values from the main search?

wojtek_emca
New Member

Main search lists all events from sourcetype=A, there is a field CID.
The second search list all events from sourcetype=B, where secondsearchCID=mainsearchCID.

Finally, I would like to list all those events together.

In SQL it is something like table1 LEFT JOIN table 2 on table1.CID=table2.CID

I tried with Splunk join command, but it is just adding fields from second search events to events in main search.

I would like to add the whole events from second search, that meet condition with CID described above.

I also tried using transaction CID, but in this case i loose some events from sourcetype A that doesn't have CID field (so it is not like LEFT JOIN).

How to do that then?

Regards,
Wojtek

0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

your main search sourcetype=A giving field CID  
| append [ your second search sourcetype=B  [search your main search sourcetype=A giving field CID  | stats count by CID | table CID ]  ]

This gives all events from sourcetype=A and all events from sourcetype=B where B.CID=A.CID.

View solution in original post

aljohnson_splun
Splunk Employee
Splunk Employee

If you're coming from the SQL world, please check out this document for some helpful comparisions. Generally, joins and appends are avoided in Splunk if possible, and, can often be avoided if you learn more about the search language.

somesoni2
Revered Legend

Try something like this

your main search sourcetype=A giving field CID  
| append [ your second search sourcetype=B  [search your main search sourcetype=A giving field CID  | stats count by CID | table CID ]  ]

This gives all events from sourcetype=A and all events from sourcetype=B where B.CID=A.CID.

wojtek_emca
New Member

That works fine - thank You.

SO it's kind of workaround to get the result.

Just to be sure - when the second subsearch is completed, the first subsearch, after search string part, is like:
CID=.. OR CID=.. OR ..

or

CID_val1 OR CID_val2 OR ..?

(@aljohnson [Splunk] - thank You for sql guide-good to know)

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...