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
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
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.
nice even better to use a lookup table. Will try it out
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