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?
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
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
Please see my comment on cmerriman's answer.
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
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.
at the end of the stats command you can |search DST_IP=* Hostname=*
so you're only getting results with values in both columns.
This won't work because there are no events with both DST_IP and Hostname present together.
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.
Seems like a neat trick, but it doesn't output anything. Are you sure we can search on stats output?