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

[nuaraujo]

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.

[muralikoppula]

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

[richgalloway]

What is your current search?