Splunk Search

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

Kirantcs
Path Finder

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

1 Solution

skoelpin
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

niketn
Legend

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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Kirantcs
Path Finder

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

0 Karma

niketn
Legend

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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma

ddrillic
Ultra Champion

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Kirantcs
Path Finder

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

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

0 Karma

Kirantcs
Path Finder

Hi skoelpin,

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

Kirantcs
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...