How to add dummy row with 0 when count returns 0?

[vinaysathyanara]

Hello Splunkers,
I am using | stats count by X, Y at the end of my query. X has 4 possible values and so does Y resulting in 16 different combinations. I need a count of 0 for each combination that doesn't exist.

I am trying | appendpipe [| stats count by X, Y | where count = 0] to get additional rows with 0 count but it is not working. Can someone please help me get additional rows in the table with 0 count?

[niketn · Accepted solution]

@vinaysathyanarayana please try the following run anywhere example which should always return 16 rows for each possible combination of field1 and field2. The first search is to simulate some combination of data available in index. For example for field1 values as A and C and field2 values as X and Y there are 4 combination which will have count (through random() function). The remaining combinations are not present. the query from | append ... till | dedup field1 field2 needs to be added to your existing search where field1 and field2 are X and Y as per your question. Please try out and confirm!

| makeresults 
| fields - _time
| eval field1="A,C", field2="X,Y" 
| makemv field1 delim="," 
| makemv field2 delim="," 
| mvexpand field1 
| mvexpand field2 
| eval count=random(), count=substr(count,1,2) 
| table field1 field2 count
| append 
    [| makeresults
    | fields - _time
    | eval field1="A,B,C,D", field2="W,X,Y,Z" 
    | makemv field1 delim="," 
    | makemv field2 delim="," 
    | mvexpand field1 
    | mvexpand field2 
    | eval count=0] 
| dedup field1 field2

[shreyas0801]

Thanks, this is exactly what I was looking for.

[vinaysathyanara]

@niketnilay Thanks a lot. That was a perfect solution. That's exactly what I was looking for.

[niketn]

@vinaysathyanarayana is it static 4*4 or can the values/list change? Also can you hardcode the 4 X values and 4 Y values or can it come from some static resource like lookup?

[vinaysathyanara]

Yes I can hardcode as they are static

[oscar84x]

I'm not sure if this will work for your search without seeing the data but have you tried fillnull? Default is 0.

... | fillnull

https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Fillnull

[vinaysathyanara]

Hello @oscar84x Thank you for the response. I tried | fillnull but it didn't help. Let me try to summarize the scenario if it helps. I have 2 fields, say category and status with 4 possible values. let's say category 1,2,3,4 and status A,B,C,D.

Now I would like to count events for each combination of category and status. Like how many events with category=1 and status=A, with category=1 and status=B and so on for all 16 possible combinations. I am using | stats count by Category, Status. Now, If there are no events with Category=2 and status=C, It doesn't show it in the table. I would like to have a 0 value for this combination.

I hope I have made it clear. please write back if it isn't clear yet.