topic Re: Single value with trend for duration? in Splunk Search

Single value with trend for duration?

tamduong16 — Tue, 07 Nov 2017 16:43:58 GMT

I have been searching about this for the last couple of days. I don't think Splunk have this feature but I just want to make sure if I was right. So I have this search:

index="monthlycdr" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "Call Duration" | timechart span="1mon" avg("Call Duration") as "TotalCD"

Which give me this result:

But when I covert my search to 00:00:00 format it doesn't show the trend. Here is the new search:

index="monthlycdr" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "Call Duration" | timechart avg("Call Duration") as "TotalCD"
| eval "TotalCD"=tostring($TotalCD$,"duration") | eval TotalCD=replace(TotalCD,"(\d+):(\d+):(\d+).(\d+)","\1:\2:\3")

Which give me this result:

I want the second search to have trend just like the first search. But I believe I can not do this due to a string conversion. Am I right that Splunk wont be able to do this, at least for now?

Re: Single value with trend for duration?

elliotproebstel — Tue, 07 Nov 2017 17:10:02 GMT

I'm not sure if this will work, but have you tried using fieldformat to format the display value instead of applying a straight eval to it? Here's some info on fieldformat:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Fieldformat

Re: Single value with trend for duration?

niketn — Tue, 07 Nov 2017 17:58:02 GMT

@tamduong16, you are right about Single Value result that it has to be numeric in order to be able to show the sparkline and trend. So, in order to tackle the scenario there could be following round-about approach:
PS: In order to demo I have used run anywhere search example from Splunk's _internal index with date_second field to mimic duration(actually it is not, so ignore the data interpretation ;)).

Step 1) Create single value trend-indicator with your existing timechart command with avg() of duration. Use round(TotalCD,0) to round off seconds.

Step 2) Set the token tokDuration as string duration i.e. HH:MM:SS inside <done> Search Event Handler.

          <done>
            <eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
          </done>

Step 3) Use the token tokDuration as underLabel using Single Value Simple XML Chart configuration option i.e.

<option name="underLabel">$tokDuration$</option>

PS: This step will give you numerical seconds as Single Value Result and string duration in HH:MM:SS as Single Value Under Label. If this suffices your needs you would not require next step for CSS override. 🙂

Step 4) Use CSS to hide numeric duration which is the Single value result. Apply CSS to Single Value Under Label to change its font size, weight, color etc. Use translate to shift Under Label to the position of Single Value Result. (this might take some hit and trial with actual position in your dashboard).
PS: We have created single value with <single id="single1"> to apply CSS override only to one single value not all. The Single Value id and CSS Selector would need to be changed as per Use Case.

      <html depends="$alwaysHideCSS$">
        <style>
          #single1 .single-result{
            visibility:hidden;
          }
          #single1 .under-label{
            font-size: 200% !important;
            font-weight: bold !important;
            transform: translate(-40px,-30px);
            fill:#333333 !important; 
          }
        </style>
      </html>

Following is the complete run anywhere dashboard example code for testing:

<dashboard>
  <label>Duration as HH:MM:SS in Single Value</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSS$">
        <style>
          #single1 .single-result{
            visibility:hidden;
          }
          #single1 .under-label{
            font-size: 200% !important;
            font-weight: bold !important;
            transform: translate(-40px,-30px);
            fill:#333333 !important; 
          }
        </style>
      </html>
      <single id="single1">
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart avg(date_second) as "TotalCD"
| eval TotalCD=round(TotalCD,0)</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
          </done>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="underLabel">$tokDuration$</option>
        <option name="unitPosition">after</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
    </panel>
  </row>
</dashboard>

Re: Single value with trend for duration?

tamduong16 — Wed, 08 Nov 2017 16:27:02 GMT

@niketnilay, Thanks for the answer it works great. Is there anyway I could get the trend number to be in the hh:mm:ss as well?

Re: Single value with trend for duration?

niketn — Wed, 08 Nov 2017 18:02:25 GMT

On similar lines to above Single value expects the trend also to be numeric. So you will need a workaround for that too. However, it is a bit complicated.

Since trend is based on a Compared To value, which is by default auto. Implying compare last value with second last value to get the trend. If you are not changing that you can use the following approach:

1) Make your Single Value Search query as Base Search id="baseSearch"

2) Post process another search to get only last two results from base search timechart results and then pick 2nd last using | tail 2 | head 1. PS: This is where change might be required if you are not comparing last result with 2nd last for trending. Also I have handled NULL data with fillnull in base search. However, you should consider handling no results scenario by defaulting the token tokTrendDiff to 00:00:00 or else it ill remain unset.

3) Hide Delta value in Trend using CSS selector #single1 .single-value-delta .delta-label

4) Add tokTrendDiff to Single Value Simple XML configuration underLabel

5) Change the alignment of Single Value Result and Delta Value Trend through CSS transform

Please find the updated result:

Refer to the complete run anywhere dashboard example below:

<dashboard>
  <label>Duration as HH:MM:SS in Single Value as Result and Trend</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSS$">
        <style>
          #single1 .single-result, #single1 .single-value-delta .delta-label{
            visibility:hidden;
          }
          #single1 .under-label{
            font-size: 200% !important;
            font-weight: bold !important;
            transform: translate(-20px,-20px);
            fill:#333333 !important; 
          }
        </style>
      </html>
      <search base="baseSearch">
        <query>
          | tail 2
          | head 1
        </query>
        <done>
          <eval token="tokTrendDiff">tostring($result.TotalCD$,"duration")</eval> 
        </done>
      </search>
      <single id="single1">
        <search id="baseSearch">
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart avg(date_second) as "TotalCD"
| fillnull value=0 TotalCD
| eval TotalCD=round(TotalCD,0)</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
          </done>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="underLabel">$tokDuration$ ( $tokTrendDiff$ )</option>
        <option name="unitPosition">after</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
    </panel>
  </row>
</dashboard>