Splunk Search

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

rakes568
Explorer

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?

0 Karma

DalJeanis
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

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

rakes568
Explorer

Please see my comment on cmerriman's answer.

0 Karma

cmerriman
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

rakes568
Explorer

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.

0 Karma

cmerriman
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.

rakes568
Explorer

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

0 Karma

cmerriman
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

rakes568
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...