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.
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
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
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
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 😉