topic Re: How to fill null values by a String when using a timechart in Splunk Search

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

Kirantcs — Sun, 04 Mar 2018 14:14:03 GMT

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

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

Kirantcs — Sun, 04 Mar 2018 14:15:45 GMT

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

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

skoelpin — Sun, 04 Mar 2018 14:42:07 GMT

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

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

ddrillic — Sun, 04 Mar 2018 14:48:54 GMT

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

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

niketn — Sun, 04 Mar 2018 16:31:11 GMT

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

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

Kirantcs — Sun, 04 Mar 2018 17:39:11 GMT

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

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

Kirantcs — Sun, 04 Mar 2018 17:40:34 GMT

Hi skoelpin,

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

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

skoelpin — Sun, 04 Mar 2018 17:50:43 GMT

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

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

skoelpin — Sun, 04 Mar 2018 17:52:28 GMT

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

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

Kirantcs — Sun, 04 Mar 2018 17:53:32 GMT

@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

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

skoelpin — Sun, 04 Mar 2018 17:57:13 GMT

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

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

niketn — Mon, 05 Mar 2018 01:56:57 GMT

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 🙂