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

[wojtek_emca]

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

[aljohnson_splun]

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 · Accepted solution]

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]

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)