Join index's together.

[jerinvarghese]

HI all,

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

Query 1: 

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 

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.

[gcusello · Accepted solution]

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

[jerinvarghese]

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 

[gcusello]

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 😉