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!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...