Combining multiple tables by join in Splunk

[dbrewerton]

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.

[dbrewerton]

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

[gangwarj]

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

[woodcock · Accepted solution]

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>=2

devmacs.mac joins to devs.mac:

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

devmacs.officeid joins to offices.officeid:

 sourcetype=devmacs OR sourcetype=officeid | eventstats dc(sourcetype) AS sourcetypes by officeid | search sourcetypes>=2

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

[woodcock]

Give me the SQL and I will convert to SPL.

[dbrewerton]

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;

[aljohnson_splun]

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.

[richgalloway]

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.

[dbrewerton]

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.

[richgalloway]

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.

[dbrewerton]

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.

[richgalloway]

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