topic Re: Search events based on lookup field and display lookup row even if nothing found in Splunk Search

Search events based on lookup field and display lookup row even if nothing found

nomad1981 — Wed, 15 Jul 2020 21:09:43 GMT

I have a lookup file which contains a list of jobnames, description and their SLAs.

Example:

jobName	Description	SLA
job1	Example1	08.00
job2	Example2	10.00
job5	Example3	05.00

My index data (index=autosys) contains events for hundreds of jobs, their status and updated time.

An event example would be: job1, FAILED, 07.00

I'm trying to write a query to output the below. However, there are cases where there won't be any events for a specific job. In that case, I need to display, "NOT RUNNING"

jobName	Description	SLA	Status	Updatetime
job1	Example1	08.00	FAILED	07.00
job2	Example2	10.00	SUCCESS	09.00
job5	Example3	05.00	NOT RUNNING	NULL

I'm using the following query, but it is not displaying the row that does not have any event/data in the index

Re: Search events based on lookup field and display lookup row even if nothing found

to4kawa — Wed, 15 Jul 2020 21:26:39 GMT

Re: Search events based on lookup field and display lookup row even if nothing found

nomad1981 — Wed, 15 Jul 2020 22:42:05 GMT

Unfortunately, the query does not return the item from the lookup if there are no events found.

Also, I need the results to the displayed in the same order of jobName as per what is in the lookup file

Re: Search events based on lookup field and display lookup row even if nothing found

to4kawa — Thu, 16 Jul 2020 10:44:51 GMT

Like this?

Re: Search events based on lookup field and display lookup row even if nothing found

nomad1981 — Thu, 16 Jul 2020 12:22:38 GMT

That query returns all jobs in found in the search index autosys.

I need to return the data that's in the lookup table as is and add 2 additional columns (Status and Updatedtime) which is found in the index data. If the search does not find the Status and Updatedtime for a specfic job, that job should still appear in the table with the 2 fields showing as "NOT RUNNING".

Re: Search events based on lookup field and display lookup row even if nothing found

to4kawa — Thu, 16 Jul 2020 13:36:51 GMT

try where

Re: Search events based on lookup field and display lookup row even if nothing found

nomad1981 — Thu, 16 Jul 2020 15:36:16 GMT

I was able to sort it out using join type=left