Splunk Search

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

vinaysathyanara
Explorer

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?

Labels (1)
0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

shreyas0801
Engager

Thanks, this is exactly what I was looking for.

Tags (1)
0 Karma

vinaysathyanara
Explorer

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

0 Karma

niketn
Legend

@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?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vinaysathyanara
Explorer

Yes I can hardcode as they are static

0 Karma

oscar84x
Contributor

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

0 Karma

vinaysathyanara
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...