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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...