Splunk Search
Highlighted

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

Explorer

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

S2:
SRCIP DSTIP

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

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?

0 Karma
Highlighted

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

Super Champion

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
Highlighted

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

Explorer

Coalescing will return all the data with SRC in S1 and SRCIP in S2. In this case, some of the SRCIP 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/SRCIP which are present in both sourcetypes, and get corresponding DSTIP and Hostname. I think someone changed question's title, so it looks otherwise.

0 Karma
Highlighted

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

Super Champion

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

Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Super Champion

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.

0 Karma
Highlighted

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

Legend

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

<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



| eval message="Happy Splunking!!!"


Highlighted

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

Explorer

Please see my comment on cmerriman's answer.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma