sum the number of events based on list of possible values

[preben12]

I have an event with a field = message_id.
I have to count the number of occurrences of this id based on a input list of possible values eg. [value1,value2,value3]

something like -> | stats count by message_id, but this doesn't take missing values into account.

The result should be something like
message_id count
value1 10
value2 20
value3 0

How can I do this ?

Second part of the question is that I need to raise an alert if any value = 0

Sorry for my noob questions

[dariusz_kwasny]

Let's assume you have your list of possible values in the lookup named message_id_lookup, your events sourceytpe is named messages and you have the message_id field in your events ant the lookup file looks like that:

message_id
value1
value2
value3
value4
value5
value6

Then, you can use following search:

| inputlookup message_id_lookup
| stats count by message_id
| eval count=count-1
| append [search sourcetype=messages | stats count by message_id ]
| stats sum(count) by message_id

To raise an alert if the message_id doesn't appear in your events you can define the saved search:

 | inputlookup message_id_lookup 
 | search NOT [search sourcetype=messages | dedup message_id | fields message_id]

Then build the alert on this search.

[preben12]

nice even better to use a lookup table. Will try it out

[HiroshiSatoh · Accepted solution]

How is the search statement like this?

･････| stats count(eval(message_id="VALUE1")) as VALUE1,count(eval(message_id="VALUE2")) as VALUE2,count(eval(message_id="VALUE3")) as VALUE3 | transpose |rename column as message_id,"row 1" as count