Splunk Search

sum the number of events based on list of possible values

preben12
Communicator

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

Tags (1)
1 Solution

HiroshiSatoh
Champion

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

View solution in original post

dariusz_kwasny
Explorer

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
Communicator

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

0 Karma

HiroshiSatoh
Champion

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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...