topic Re: Join index's together. in Splunk Search

Join index's together.

jerinvarghese — Fri, 07 Aug 2020 12:25:50 GMT

HI all,

I have 2 index, that have same common field together. I want to join both together.

Query 1:

Query 2 :

index=itsm sourcetype=remedy_midtier | rename _time as Time | fieldformat Time=strftime(Time,"%Y-%m-%d %l:%M:%S %p") | table nodelabel, Incident_Number, Time

Output table 1:

nodelabel Status Time

CASCO	Up	2020-08-07 5:45:28 PM
AERIB	Up	2020-08-07 5:30:05 PM
CNPYU	Up	2020-08-07 5:34:41 PM

Output Table 2:

nodelabel Incident_Number Time

CASCO	INC000013850038	2020-08-07 5:45:28 PM
CNPTT	INC000013850032	2020-08-07 5:34:42 PM
CNPYU	INC000013850032	2020-08-07 5:34:41 PM

expected output:

nodelabel Incident_Number Status Time

CASCO	INC000013850038	UP	2020-08-07 5:45:28 PM
CNPTT	INC000013850032		2020-08-07 5:34:42 PM
CNPYU	INC000013850032	UP	2020-08-07 5:34:41 PM

I used join inner command but it failed. please help me in the join function. here nodelabel should be the common factor.

Re: Join index's together.

gcusello — Fri, 07 Aug 2020 12:35:43 GMT

Hi @jerinvarghese,

you could use the join command

index=opennms "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown" | rex field=eventuei "uei.opennms.org/nodes/node(?<Status>.+)" | stats max(_time) as Time latest(Status) as Status by nodelabel | table nodelabel Status Time | join nodelabel [ search index=itsm sourcetype=remedy_midtier | rename _time as Time | fieldformat Time=strftime(Time,"%Y-%m-%d %l:%M:%S %p") | table nodelabel Incident_Number Time ] | table table nodelabel Incident_Number Status Time

but I don't like because it's very slow and there's the limit of 50,000 results in subsearch.

So I hint to explore a different approach using the stats command:

(index=opennms "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown") OR (index=itsm sourcetype=remedy_midtier) | rex field=eventuei "uei.opennms.org/nodes/node(?<Status>.+)" | stats max(_time) as Time values(Incident_Number) AS Incident_Number latest(Status) as Status by nodelabel | table table nodelabel Incident_Number Status Time

Ciao.

Giuseppe

Re: Join index's together.

jerinvarghese — Fri, 07 Aug 2020 13:28:43 GMT

Hi @gcusello

thanks for the reply, i got the output, but there is a problem, output ignores all those values which doesn't have Incidnet_number.

I need those nodelabel as well with blank value for Incident_number.

please help in that

i used below command, hope this is right method.

| join type=outer nodelabel

Re: Join index's together.

gcusello — Fri, 07 Aug 2020 15:05:52 GMT

Hi @jerinvarghese,

as I said, I don't like join so I prefer the second solution that I hint to explore and use:

you are using a DB approach, but Splunk isn't a DB!

About your problem, did you tried to invert the two searches?

Ciao.

Giuseppe

P.s.: Karma Points are appreciated 😉