How to edit my search to join common values from two sourcetypes?

[rakes568]

New to Splunk.
Suppose I have two sets of data in separate sourcetypes S1 and S2.
S1:
SRC Hostname Field1 Field2

S2:
SRC_IP DST_IP

Now SRC in S1 and SRC_IP in S2 may contain some common values. I want to create a table of SRC_IP, DST_IP and Hostname where S1.SRC==S2.SRC_IP.

I understand that I need to use join with subsearch.
I wrote this search:

sourcetype=S1 | join SRC [search sourcetype=S2 | rename SRC_IP AS SRC]

But this just results in S1 data where S1.SRC==S2.SRC_IP. How can I add Hostname field to it, and create a table from the 3 mentioned fields?

[DalJeanis]

This creates test data to simulate sourcetype=S1 OR sourcetype=S2

| makeresults
| eval mydata="sourcetype=S1,SRC=001.001.001.001,Hostname=hostA,Field1=f1A,Field2=f2A sourcetype=S1,SRC=002.002.002.002,Hostname=hostB,Field1=f1B,Field2=f2B sourcetype=S1,SRC=003.003.003.003,Hostname=hostC,Field1=f1C,Field2=f2C sourcetype=S1,SRC=004.004.004.004,Hostname=hostD,Field1=f1D,Field2=f2D sourcetype=S2,SRC_IP=001.001.001.001,DST_IP=123.123.123.001 sourcetype=S2,SRC_IP=002.002.002.002,DST_IP=123.123.123.002  sourcetype=S2,SRC_IP=004.004.004.004,DST_IP=123.123.123.004 sourcetype=S2,SRC_IP=005.005.005.005,DST_IP=123.123.123.005"
| table mydata
| makemv mydata
| mvexpand mydata
| streamstats count as recno
| makemv delim="," mydata
| mvexpand mydata
| rex field=mydata "(?<fieldname>[^=,]+)=(?<fieldvalue>[^=,]+)"
| eval {fieldname} = fieldvalue
| fields - mydata fieldname fieldvalue 
| stats values(*) as * by recno
| fields - recno

This gets your answer

| eval SRC=coalesce(SRC,SRC_IP) 
| stats values(*) as *, dc(sourcetype) as foundboth by SRC 
| where foundboth==2

[niketn]

You can create an Field Alias in Splunk Knowledge Object so that you can convert SRC to SRC_IP or vice versa for correlation. However following is a query without Field Alias where I am manually changing SRC to SRC_IP

<Your Base Search> (sourcetype=S1 AND SRC=*)  OR (sourcetype=s2 AND SRC_IP=*)
| rename SRC as SRC_IP
| stats values(Hostname) as Hostname  values(DST_IP)  as DST_IP by SRC_IP

[rakes568]

Please see my comment on cmerriman's answer.

[cmerriman]

you can create this without the join command

try something like this:

sourcetype=S1 OR sourcetype=S2|eval SRC=coalesce(SRC,SRC_IP)|stats values(DST_IP) as DST_IP values(Hostname) as Hostname by SRC

[rakes568]

Coalescing will return all the data with SRC in S1 and SRC_IP in S2. In this case, some of the SRC_IP in S2 might not be present in S1(as SRC), so they won't have Hostname values either. So current output as per your search query results in a table with few rows having empty Hostname. I want a subset of SRC/SRC_IP which are present in both sourcetypes, and get corresponding DST_IP and Hostname. I think someone changed question's title, so it looks otherwise.

[cmerriman]

at the end of the stats command you can |search DST_IP=* Hostname=* so you're only getting results with values in both columns.

[rakes568]

This won't work because there are no events with both DST_IP and Hostname present together.

[cmerriman]

try removed the DST_IP=* and just search for Hostname=*

if none of the SRCs have both DST_IPs and Hostnames then one column will always be blank.

[rakes568]

Seems like a neat trick, but it doesn't output anything. Are you sure we can search on stats output?