Identify missing events based Lookup list of values

[rangarbus]

Hey Team

I have events which contains a field "job_code". 

index=default source=jobfeed 

I have a lookup (jobs.csv) which has the list of allowed job codes. 

jobCode	jobDesc
000	EX
001	PT

My requirement is to generate an alert every day, If any of the jobCode available in lookup didn't show up at all on the events for past 2 days.

For instance,  for past 2 days if splunk didn't receive event with job_code as 000 , then i need an alert.

I need this check for all the jobCode in the lookup table.

Can you please help me with a query for this?

Thank you

[gcusello · Accepted solution]

Hi @rangarbus,

it's the same solution to identify missing hosts, something like this:

index=default source=jobfeed 
| stats count BY job_code
| append [ | inputlookup jobs.csv | eval count=0 | fields job_code count ]
| stats sum(count) AS total BY host
| where total=0

You can define the scheduling and related timeperiod fo this alert (e.g. 24 h).

In few words: total=0 means that the zero value is from lookup and you haven't any log from the main search.

Ciao.

Giuseppe

[rangarbus]

Thanks @gcusello . It worked..

Only change i made was to rename the event field name (job_code) to match the lookup field name (jobCode)

index=default source=jobfeed 
| stats count BY job_code
| rename job_code as jobCode
| append [ | inputlookup jobs.csv | eval count=0 | fields jobCode count ]
| stats sum(count) AS total BY jobCode
| where total=0

[gcusello]

Hi @rangarbus,

good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

Hi @rangarbus,

I'm sorry with you becase I wasn't so explicit:

with the above search, using the condition "| where total=0" you take only the job_codes that are in the lookup but not in the search!

The solution is just to solve the problem that when a job_codes is missing it isn't in the main search result, for this reason you added the subsearch with the count=0:

total=0 means that you haven't results in the main search and the job_codes are missing.

Running the search without the condition "| where total=0", you have both the conditions:

• if total>0 you have events for that job_code
• if total=0 you haven't events for that job_code

Ciao.

Giuseppe