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!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...