How to force all needed rows and columns to display in a chart, table or other, when sometimes no data is found.

[KAC]

Howdy, I'm struggling with the following and hoping you can help. To summarize, I require a 'value' column, which is the left most column that contains all the possible values I have defined in an eval statement. The values in this left most column are all the possible values that might be in the data. The other columns consist of all the possible status values that might be in the data. As an example

Value Status1 Status2 Status3 Status4
Value1

Value2
Value3
Value4
Value5

All values and Status must be display, whether there is data in the index or not. For example, If I have this data.

Value   Status
Value1  Status1
Value1  Status1
Value1  Status2
Value1  Status3
Value2  Status1
Value2  Status2
Value3  Status3
Value3  Status1

The the chart\table result should be as follows

Value Status1 Status2 Status3 Status4
Value1 2 1 1 0
Value2 1 1 0 0
Value3 1 0 0 0
Value4 0 0 0 0
Value5 0 0 0 0

I've danced around this for a couple of days without any success. Looked up and tried all sorts of things without success. Any thoughts or help y'all might offer will be greatly appreciated. Thank you.

PS: I'm really trying to not use joins in any way, so as to avoid the costs associated with it.

[spayneort]

If the values are not in the data, you need to add them. You could append them to the search using makeresults or inputlookup.

To use makeresults - add something like this to your search:

    | append 
        [| makeresults 
        | fields - _time 
        | eval Value="Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9" 
        | makemv delim="," Value 
        | mvexpand Value]
    | stats count(eval(Status=="Status1")) as Status1, count(eval(Status=="Status2")) as Status2, count(eval(Status=="Status3")) as Status3, count(eval(Status=="Status4")) as Status4 by Value

Using inputlookup is similar, but you would first need to create a lookup of each possible value.

| append 
    [| inputlookup values.csv]
| stats count(eval(Status=="Status1")) as Status1, count(eval(Status=="Status2")) as Status2, count(eval(Status=="Status3")) as Status3, count(eval(Status=="Status4")) as Status4 by Value

[KAC]

Thank you spayneort & codebuilder. I have been working on incorporating this into my search code. However, I'm continuing to stumble on this one. Specifically, Within my data I do have a field called, "Application", and when there is a data record, 'Application' is populated. The challenge I'm encountering is, I have a finite set of applications that have to be in the results - even if there aren't any data records for that application. So, I think the question I have now is, using the eval approach you provided earlier, how do I "link" the "Value" from the append, which I called, "MyApp", to the apps from the data, so that, if there are records they are counted under that "MyApp", but if there are no records for a particular app in "MyApp", then it is still set to zero, and comes out in the data

| eval Value="Myapp1,Myapp2,Myapp3,Myapp4....."

Apps Status1 Status2 Status3 Status4
MyApp1 0 0 0 0
MyApp2 5 0 22 7
MyApp3 45 0 0 235
MyApp4 0 2 0 0

MyApp1 - No records in the data
MyApp2 - 34 records in the data
MyApp3 - 280 records in the data
MyApp4 - 2 records in the data.

So two things are needed
1) How do I make sure that when there are no records in the data the app still displays, with zeros.
2) How do I link what's happening within the append with the search results that are puling in data.

Thank you in advance for whatever help you can provide.

[KAC]

Thank you code builder. I am currently using fillnull to ensure that the status' are all there (|fillnull status1 status2 status3 status4), and that is working - across the table, but down the table, I need to ensure every possible value, which are alpha values, are displayed. To further clarify, in the example below, App1 through App999 need to display, every time, with the counts by status, and if there are no counts for a status for any app, then a zero is displayed.

Does this help?

                status1     status2     status3     status4

App1
App2
App3
.
.
.
App999

[codebuilder]

If I understand your issue correctly, it's easily solved by simply adding the following to your search:

| fillnull value=0

https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Fillnull#Examples