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!

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...