How to fill null values by a String when using a timechart

[Kirantcs]

This is the query is used:

index=perfmon* sourcetype=Perfmon:CPU counter="% Processor Time"

| eval status=if(Value!="","UP","DOWN")
| timechart span=5m usenull=true latest(status) by host

If i run this queries it will return:

_time Hostname(replaced by the hostname)
x A(some value)("UP" is displayed here)
y B(some value)("UP" is displayed here)
z "null"(No value looks empty)

So all i wanted to know is how to fill the null value with a string "DOWN".
Any help will be appreciated

[niketn]

@Kirantcs, since you are getting Windows Performance Counters, I believe your expected output is just to find out whether the system is up or down in the last 5 (or may be 10-15 min) window.

If your inputs.conf is configured to push CPU performance counter every 5 min, then if you do not get any data from your Windows Machine to Splunk that means Windows Machine in Down. When you get any data Value will always be present i.e. it will be "0.00" rather than "", which you are trying evaluate to know the status. So a better approach would be to may be check for latest event with CPU performance counter in the last 15 min window with metadata or tstats command and compare the duration between event time and current time to know the status as Up or Down.

You can show numeric data i.e. latest(Value) as the CPU utilization over time with 5 min span in timechart. When there is no CPU Utilization (rare) or Machine is Down or Splunk is not collecting Data (based on inputs.conf) you will have timechart hit 0 value on y-axis.

Following is an example of some of the graphical interpretation of CPU Performance metrics.

Following is the Simple XML Code ( You can replace your base search i.e. index name, sourcetype and collection name )

<form>
  <label>CPU Performance Metrics</label>
  <search>
    <query>| metadata type=sourcetypes index=<yourIndexName>
| search sourcetype="Perfmon:CPU"
| fields lastTime
    </query>
    <earliest>$tokTime.earliest$</earliest>
    <latest>$tokTime.latest$</latest>
    <done>
      <condition match="$job.resultCount$==0">
        <set token="tokStatus">Down</set>
        <eval token="tokLastTime">strptime($job.earliestTime$,"%Y/%m/%d %H:%M:%S %p")</eval>
      </condition>
      <condition>
        <set token="tokStatus">Up</set>
        <set token="tokLastTime">$result.lastTime$</set>
      </condition>
    </done>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="tokTime">
      <label></label>
      <default>
        <earliest>-15m@m</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <viz type="status_indicator_app.status_indicator">
        <title>Last CPU Uptime Duration</title>
        <search>
          <query>| makeresults
| eval status="$tokStatus$"
| eval duration=(now()-$tokLastTime$)." (secs ago)"
| eval icon=if(status=="Up","check-circle","times-circle")
| eval color=if(status=="Up","#65a637","#d93f3c")
| table duration icon color</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
        </search>
        <option name="height">150</option>
        <option name="refresh.display">progressbar</option>
        <option name="status_indicator_app.status_indicator.colorBy">field_value</option>
        <option name="status_indicator_app.status_indicator.fillTarget">background</option>
        <option name="status_indicator_app.status_indicator.fixIcon">warning</option>
        <option name="status_indicator_app.status_indicator.icon">field_value</option>
        <option name="status_indicator_app.status_indicator.precision">0</option>
        <option name="status_indicator_app.status_indicator.showOption">1</option>
        <option name="status_indicator_app.status_indicator.staticColor">#65a637</option>
        <option name="status_indicator_app.status_indicator.useColors">true</option>
        <option name="status_indicator_app.status_indicator.useThousandSeparator">false</option>
      </viz>
    </panel>
    <panel>
      <chart>
        <title>Latest CPU Utilization (%)</title>
        <search>
          <query>index="<yourIndexName>" sourcetype="<yourSourceType>" instance="_Total" collection="<YourCollectionName>" object="Processor Information" counter="% Processor Time"
| head 1
| eval Value=round(Value,1)
| table Value</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <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">radialGauge</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">connect</option>
        <option name="charting.chart.rangeValues">[0,85,95,100]</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.gaugeColors">["0x84E900","0xFFE800","0xBF3030"]</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">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="height">150</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>CPU Utilization Trending (Gaps connected)</title>
        <search>
          <query>index="<yourIndexName>" sourcetype="<yourSourceType>" instance="_Total" collection="<YourCollectionName>" object="Processor Information" counter="% Processor Time"
| timechart values(Value) as "CPU%"
| eval "CPU%"=round('CPU%',1)
| eval Warning=85
| eval Critical=95</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisLabelsY.majorUnit">5</option>
        <option name="charting.axisTitleX.text">Time</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.maximumNumber">100</option>
        <option name="charting.axisY.minimumNumber">0</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">area</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">connect</option>
        <option name="charting.chart.overlayFields">Critical,Warning</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</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">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="height">382</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="charting.fieldColors">{"Warning":"#f7bc38","Critical":"#d93f3c"}</option>
      </chart>
    </panel>
  </row>
</form>

[Kirantcs]

Hi Bro,

Thank you for your answer.
First of all,i dont want CPU performance.

Second,i did try the metadata for the server availability,but metadata holds value only for latest transaction.
According to my requirements,i want to present a servers availability for last month. 🙂

So would be nice,if there's way out.Even by any other means.

Cheers

[niketn]

Your Base Search was sourcetype=Perfmon:CPU counter="% Processor Time", so I anticipated you are interested in CPU Performance counter.

metadata command will give you results based on time selected by using Time Range Picker. Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Metadata#Description
Also, as stated you can also use tstats command. Can you explain what you mean by Server Availability for Last Month? If you want to show trending then you can have Gaps as Zero in your timechart.

Based on your question you need just the Status Indicator on top left. However, since your query in the question had timechart, I tried to show different representation of your data.

Just noticed that your issue is already resolved 🙂

[skoelpin · Accepted solution]

Try this

index=perfmon* sourcetype=Perfmon:CPU counter="% Processor Time" 
| eval status=if(Value!="","UP","DOWN")
| timechart span=5m usenull=true latest(status) by host
| fillnull status value=DOWN

[ddrillic]

Sorry @skoelpin, but on line #2 we already set status to DOWN for the empty ones, right?

[skoelpin]

@ddrillic Yes, I added line 4 which will look at the field status and fill any null values with the string "down"

[Kirantcs]

@skoelpin,

The issue is solved, just used this query:

 index=perfmon* sourcetype=Perfmon:CPU counter="% Processor Time" 
 | eval status=if(Value!="","UP","DOWN")
 | timechart span=5m usenull=true latest(status) by host
 | fillnull value=DOWN

[skoelpin]

Great to hear! Please accept the answer if this worked for you

[Kirantcs]

Hi skoelpin,

Also a new field called "status" will be created by using that query.

[skoelpin]

Yes correct, in SPL anytime you use the eval command, you are telling Splunk to create a new field.

So if you break this down

| eval status=if(Value!="","UP","DOWN")

eval says to create a new field called status and if the field Value is null, assign status the value UP or else assign it DOWN

[Kirantcs]

x and y is time of the event, A and B will be "UP"