topic Re: sum the number of events based on list of possible values in Splunk Search

sum the number of events based on list of possible values

preben12 — Fri, 26 Jul 2013 09:27:27 GMT

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

Re: sum the number of events based on list of possible values

HiroshiSatoh — Mon, 28 Sep 2020 14:26:25 GMT

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

Re: sum the number of events based on list of possible values

dariusz_kwasny — Mon, 28 Sep 2020 14:27:07 GMT

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.

Re: sum the number of events based on list of possible values

preben12 — Tue, 30 Jul 2013 05:51:21 GMT

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