topic Re: Count by column with clausule where and fill with 0 if not found in Splunk Search https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303330#M91272 <P>Works fine for me thank you <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 12 Oct 2017 06:02:45 GMT miki73 2017-10-12T06:02:45Z Count by column with clausule where and fill with 0 if not found https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303328#M91270 <P>Hello,<BR /> So situation is </P> <PRE><CODE>| stats count by col1 | where col1 IN ("tmp1", "tmp2", "tmp3") </CODE></PRE> <P>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 </P> <P>col1 count<BR /> tmp1 0<BR /> tmp2 5<BR /> tmp3 10</P> <P>After that i want to create from this a chart so i need to include values with count=0 to analize. <BR /> anyone know how to handle that situation ?</P> Wed, 11 Oct 2017 11:58:47 GMT https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303328#M91270 miki73 2017-10-11T11:58:47Z Re: Count by column with clausule where and fill with 0 if not found https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303329#M91271 <P>Something like this, should work. </P> <P><CODE>| 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")</CODE></P> <P>If the list is too big, its better to put them in a lookup table and use append command.</P> <P>link for lookup solution.<BR /> <A href="https://answers.splunk.com/answers/580763/help-needed-with-a-search-and-a-lookup.html#answer-582032">https://answers.splunk.com/answers/580763/help-needed-with-a-search-and-a-lookup.html#answer-582032</A></P> Wed, 11 Oct 2017 16:18:28 GMT https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303329#M91271 kyaparla 2017-10-11T16:18:28Z Re: Count by column with clausule where and fill with 0 if not found https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303330#M91272 <P>Works fine for me thank you <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 12 Oct 2017 06:02:45 GMT https://community.splunk.com/t5/Splunk-Search/Count-by-column-with-clausule-where-and-fill-with-0-if-not-found/m-p/303330#M91272 miki73 2017-10-12T06:02:45Z