Splunk Search

How do I make a search that displays all events in a lookup and alerts if any are missing?

nuaraujo
Path Finder

Hello all,

I need your help with the following search:

I have a lookup file with a list of ids and account ID's

ID | Account_ID
AAA | 111
BBB | 222
CCC | 333

Every day, I have events that I can match with my lookup, using ID field.

DATE | ID
2018-12-10 | AAA
2018-12-10 | BBB
2018-12-10 | CCC
2018-12-11 | AAA
2018-12-11 | BBB
2018-12-11 | CCC (lets suppose this event is missing/does not exist)
2018-12-12 | AAA
2018-12-12 | BBB
2018-12-12 | CCC

What I want to see in my result is: all events from my lookup that has existing events in the search, per day. If any is missing, an error should be displayed.

Any idea about how can I achieve this?

DATE | ID | ACCOUNT_ID |RESULT
2018-12-10 | AAA | OK
2018-12-10 | BBB | OK
2018-12-10 | CCC | OK
2018-12-11 | AAA | OK
2018-12-11 | BBB | OK
2018-12-11 | CCC | ERROR
2018-12-12 | AAA | OK
2018-12-12 | BBB | OK
2018-12-12 | CCC | OK

Thanks in advance for your help on this.

Tags (1)
0 Karma

muralikoppula
Communicator

Try something like this,

| tstats count WHERE index=my_index [| inputlookup account_id.csv 
| table ID Account_ID] by ID Account_ID
| append [| inputlookup account_id.csv | eval count = 0]
| stats max(count) as count by ID Account_ID
| where count=0
| eval status=case(match(Account_ID,"CCC"),"Account ID CCC is missing",
                   match(Account_ID,"AAA"),"Account ID AAA is missing")
| table index ID Account_ID
0 Karma

richgalloway
SplunkTrust
SplunkTrust

What is your current search?

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...