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
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.
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
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.
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
If I understand your issue correctly, it's easily solved by simply adding the following to your search:
| fillnull value=0