Best way to Join two tables

[timmalos]

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,

[timmalos]

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

[timmalos · Accepted solution]

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

[lguinn2]

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.

[timmalos]

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

[linu1988]

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?