Count by column with clausule where and fill with 0 if not found

[miki73]

Hello,
So situation is

| stats count by col1 | where col1 IN ("tmp1", "tmp2", "tmp3")

and i call this for last 5 min, but sometimes for example "tmp1" is not happen in last 5 min so i want to put into my list value like

col1 count
tmp1 0
tmp2 5
tmp3 10

After that i want to create from this a chart so i need to include values with count=0 to analize.
anyone know how to handle that situation ?

[kyaparla · Accepted solution]

Something like this, should work.

| stats count by col1 | appendpipe [tail 1 | eval col1=mvappend("tmp1", "tmp2", "tmp3") | eval count=0] | mvexpand col1 | stats max(count) as count by col1| where col1 IN ("tmp1", "tmp2", "tmp3")

If the list is too big, its better to put them in a lookup table and use append command.

link for lookup solution.
https://answers.splunk.com/answers/580763/help-needed-with-a-search-and-a-lookup.html#answer-582032

[miki73]

Works fine for me thank you 🙂