Splunk Search

Best way to Join two tables

timmalos
Communicator

Hey. I have these kind of datas every one week :

"SilkWorm48000",SwitchWWN ,160,"SwSerialNumber","http://UrlManagement/",swIsPrincipal,"42.2","v6.4.3"

(host=sancocsw2 sourcetype=CSWInfos source=\bob01\sancocsw2_infos.txt)

And these kind of datas every 2 minutes :

State,Status,CPU Usage,Memory Usage

(host=sancocsw2 sourcetype=CSWInfos source=\bob01\sancocsw2_infos.txt)

I would have a table that join those 2 datas in one table, that is all fields from the second data joined with the fields of the first one.
This search display all the lines of data i need :

index=main sourcetype="cswinfos" OR sourcetype="cswstatus"| dedup host,sourcetype sortby -_time

I saw in the doc many ways to do that(Like append,appendcols,appendpipe,join,...), sometimes with a SubSearch and I would know which one is the best practise.

Thanks for your help,

Tags (3)
0 Karma
1 Solution

timmalos
Communicator

In theory I would one big table containing all the fields from both cswinfos and cswstatus. As here, only data from cswstatus need to be in real-time (cswInfos will be refreshed every night by the restart of the server)

This search did the job :

index=main sourcetype="cswstatus"|dedup host sortby -_time|join host [search index=main sourcetype="cswdesc"|dedup host sortby -_time]|table host swModel swSerialNumber swWWN swMaxPort swURLManagement swPrincipal swHardVersion swSoftVersion swState swStatus swCPUUsage swMemoryUsage

The problem now is when i got 2 or 3 join to do from 3 different sourcetype, all of them in real-time. I posted here : answers.splunk

View solution in original post

0 Karma

timmalos
Communicator

Answered as an answer as it was too long for a comment

0 Karma

timmalos
Communicator

In theory I would one big table containing all the fields from both cswinfos and cswstatus. As here, only data from cswstatus need to be in real-time (cswInfos will be refreshed every night by the restart of the server)

This search did the job :

index=main sourcetype="cswstatus"|dedup host sortby -_time|join host [search index=main sourcetype="cswdesc"|dedup host sortby -_time]|table host swModel swSerialNumber swWWN swMaxPort swURLManagement swPrincipal swHardVersion swSoftVersion swState swStatus swCPUUsage swMemoryUsage

The problem now is when i got 2 or 3 join to do from 3 different sourcetype, all of them in real-time. I posted here : answers.splunk

0 Karma

lguinn2
Legend

What output or analysis do you need? I don't know that a join or even a subsearch is needed at all.

If you really just want to put everything in one big table in real time, then you could set up the first data source as a lookup table (or even a time-based lookup).

But the best practice depends on the result that you want.

0 Karma

timmalos
Communicator

The field which can be used for the join is "host"
My problem is that i run these search in RealTime, and with join i have a subsearch which is running only on all-time

0 Karma

linu1988
Champion

Hello Tim,
i suppose there are no best practices for using these keywords as they are used for different purpose.

e.g. Join can be used if we have common column, where as append we can use anywhere just to combine the result. What's the fields we are having in the first source?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...