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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...