topic Re: Combining multiple tables by join in Splunk in Splunk Search

Combining multiple tables by join in Splunk

dbrewerton — Tue, 12 May 2015 12:38:04 GMT

Hey folks, I am new here and glad to find this useful resource. I have four tables that I am trying to create a join to make the information cohesive across all four. My schema is like this:

devmacs
- macaddr
- mac_id

macport
- macportid
- portname

devs
- officeid
- routername
- mac
- ip

offices
- officeid
- officename

Now, what I am trying to do is connect all of these up in the following ways:

devmacs.mac_id joins to macport.macportid
devmacs.mac joins to devs.mac
devmacs.officeid joins to offices.officeid

I want to present my table data like so:

devmacs.macaddr, macport.portname, devs.routername, devs.ip, offices.officename

If this were all SQL server query, I would probably have no trouble. The issue I have is getting used to how Splunk queries actually work using the dbconnect part of Splunk. Any help would be most appreciated and thank you.

Re: Combining multiple tables by join in Splunk

richgalloway — Tue, 12 May 2015 13:56:45 GMT

You mention "tables" and "db connect". Is your data in Splunk or in a SQL database? If the latter then it's just a matter of creating an external database connection and using DB Connect to run a SQL query.

Re: Combining multiple tables by join in Splunk

dbrewerton — Tue, 12 May 2015 16:43:11 GMT

Sorry, I was trying to establish a baseline of what my competency level is. I heard that DB Connect is much like using SQL query language.

Re: Combining multiple tables by join in Splunk

aljohnson_splun — Tue, 12 May 2015 16:51:32 GMT

Read this ! It may be the quickest way to get where you want to be: http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/SQLtoSplunk

Secondly, if you post a few sample events, people will be able to help you much more easily.

If those linked fields, e.g. office id, are very static, you may want to consider using a lookup table to connect these fields... If you did that, you'd be able to just use table to get that output.

Re: Combining multiple tables by join in Splunk

woodcock — Tue, 12 May 2015 16:55:29 GMT

Give me the SQL and I will convert to SPL.

Re: Combining multiple tables by join in Splunk

dbrewerton — Tue, 12 May 2015 17:15:41 GMT

Ok I think this should give me what I am looking for.

SELECT 
dm.macaddr, dm.mac_id, mp.macportid, mp.portname, 
dv.officeid, dv.routername, dv.mac, dv.ip,
off.officeid, off.officename from devmacs dm, macport mp, 
devs dv, offices off 
INNER JOIN macport on dv.mac_id = mp.macportid
INNER JOIN devmacs on dm.mac = dv.mac
INNER JOIN offices ON dv.officeid = off.officeid;

Re: Combining multiple tables by join in Splunk

woodcock — Tue, 12 May 2015 17:24:41 GMT

If I understand you correctly, this is your situation:

sourcetype devmacs has fields {macaddr,mac_id}
sourceytpe macport has fields {macportid,portname}
sourcetype devs has fields {officeid,routername,mac,ip}
sourcetype offices has fields {officeid,officename}

If that is correct, then these are your base searches to do the "joins" (without using "join", which is problematic):
devmacs.mac_id joins to macport.macportid:

sourcetype=devmacs OR sourcetype=macport | eval comboID=coalesce(devmacs,macport) | eventstats dc(sourcetype) AS sourcetypes by comboID | search sourcetypes&gt;=2

devmacs.mac joins to devs.mac:

 sourcetype=devmacs OR sourcetype=devs | eventstats dc(sourcetype) AS sourcetypes by mac | search sourcetypes&gt;=2

devmacs.officeid joins to offices.officeid:

 sourcetype=devmacs OR sourcetype=officeid | eventstats dc(sourcetype) AS sourcetypes by officeid | search sourcetypes&gt;=2

As far as the rest, I do not understand what you are trying to do but this should get you almost there.

Re: Combining multiple tables by join in Splunk

richgalloway — Tue, 12 May 2015 17:47:33 GMT

DB Connect is used to extract data from an SQL database into Splunk. Using DB Connect will feel familiar to SQL users.
If your data is already in Splunk, however, DB Connect is not the answer.

Re: Combining multiple tables by join in Splunk

dbrewerton — Tue, 12 May 2015 17:50:51 GMT

I think I may be looking at a MySQL database. Some of the standard SQL commands give me errors and it even says things about MySQL.

Re: Combining multiple tables by join in Splunk

richgalloway — Tue, 12 May 2015 17:54:51 GMT

What are some of those commands, where are you entering them, and what are the errors?

Re: Combining multiple tables by join in Splunk

dbrewerton — Tue, 12 May 2015 19:31:22 GMT

I was overthinking the situation. Thank you folks 🙂 I did figure it out but each of you did help me on my adventure.

Re: Combining multiple tables by join in Splunk

gangwarj — Mon, 05 Feb 2018 12:08:38 GMT

Could you please share how you were able to do the task at hand. I am facing the similar issue, it would help me