How to create a search to add field values in a table even if they are null values?

[Mrig342]

Hi All,

I have got a query like below to get the count of different "Actual_Status" values in below tabular format:

... | rex field=_raw "\<tr\>\s+\<td\s\>(?P<Domain>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>(?P<App_Name>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>(?P<Machine>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>(?P<Type>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>(?P<Instance>[^\<]+)\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\"\w+\"\>(?P<Actual_Status>[^\<]+)\<\/\w+\>\<\/b\>\<\/td\>"
| rex field=_raw "\<tr\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>[^\<]+\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\"\w+\"\>[^\<]+\<\/\w+\>\<\/b\>\<\/td\>\s+\<td\s\>\<b\>\<\w+\s\w+\=\"\w+\"\>(?P<Expected_Status>[^\<]+)\<\/\w+\>\<\/\>\<\/td\>"
| dedup App_Name,Machine,Type,Instance,Actual_Status,Expected_Status
| stats count as Total by Actual_Status

Actual_Status	Total
HAWK	207
RUNNING	46
RUNNING-OOS	91
STOPPED	415

I am using these 7 values of "Actual_Status" for our dashboard (RUNNING,RUNNING-OOS,STOPPED,HAWK,STOPPING, STANDBY & ERROR) and I want to put all the fieldvalues in the table even if its count is zero. Like in below table so that I can show the visualization in "Column chart" view in a more meaningful way:

Actual_Status	Total
HAWK	207
RUNNING	46
RUNNING-OOS	91
STOPPED	415
STANDBY	0
STOPPING	0
ERROR	0

Requesting you all to help me modify the query to get the expected table and dashboard visualization.

Your kind inputs are highly appreciated..!!
Thank You..!!

[richgalloway]

Splunk is designed to find stuff in your logs so it doesn't handle absent events well.  Since the set of possible Actual_Status values is infinite (as far as Splunk knows), it can't fill in missing values for you.  You may be able to do so yourself, however.

<<your query>>
| append [| makeresults format=csv data="Actual_Status,Total
HAWK,0
RUNNING,0
RUNNING-OOS,0
STOPPED,0
STANDBY,0
STOPPING,0
ERROR,0"]
| stats sum(Total) as Total by Actual_Status

The append command adds placeholder values to the query then the final stats command combines the results.

[Mrig342]

Hi @richgalloway,

Thank you for your inputs.. I am able to get the table as expected. But if I use the "Column chart" visualization, I am not able to assign separate colors to each field values although I was able for "Pie chart" visualization.

Can you please suggest on how I can achieve that in column visualization.

Thank You..!!

[richgalloway]

Both chart types use the charting.seriesColors option to set colors for each value.  Depending on your query, charting.fieldColors may be what you should use.

[Mrig342]

Hi @richgalloway,

Yes, I used charting.fieldColors in my query. Like below:

<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">minimal</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"STOPPED":0xFF0000,"HAWK":0xF8BE34,"RUNNING":0x006D9C,"STOPPING":0xDC4E41,"ERROR":0x00FFFF,"RUNNING-OOS":0x800000,"STANDBY":0xFFA500}</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">none</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>

Anything you can suggest..?

[richgalloway]

If "STOPPED", "HAWK", "RUNNING", etc. are values of a field rather than separate field names then charting.fieldColors won't work and you'll have to use charting.seriesColors.

<option name="charting.fieldColors">{0xFF0000,0xF8BE34,0x006D9C,0xDC4E41,0x00FFFF,0x800000,0xFFA500}</option>

You may have to experiment with the order of the values to get the desired color-to-value mapping.