Splunk Search

How can I graph the string/human-readable value of duration?

smoir_splunk
Splunk Employee
Splunk Employee

I am able to graph the duration calculation while it is in seconds, but I want to display the human-readable string value on a bar chart for easy visual consumption. How can I display the human-readable duration values on the chart?

Here's the search that I'm using (yes I know it's duplicative to re-convert the data back to seconds the way I'm doing it but whatever):
index=music source="streaminghistory.json" | stats sum(msPlayed) as totalMs by artistName | eval duration=tostring(round(totalMs/1000), "duration") | sort -duration limit=15 | rename artistName as artist | convert dur2sec(duration) as graph_duration | table artist,duration,graph_duration

alt text

Should I be using a different search command? I tried xyseries but that just made things weird. A chart overlay also did not do the trick. I'd like it to just show the duration on the x-axis, or at the very least have the value for duration be visible when you hover over the bars. Any suggestions?

0 Karma
1 Solution

niketn
Legend

@smoir_splunk You can use Timeline Custom Visualization with some Simple XML CSS extension to display duration in Human readable format like HH:MM:SS (I have added string format to also handle days HH:MM:SS for duration >24 hours).

In the SPL I have added a dummy Time field as current time using now() (since Timeline Custom Visualization needs Time field).
I have calculated some dummy duration values in seconds. Using tostring() and replace() evaluation functions, I have converted duration to Day HH:MM:SS format duration_label.

 index=_internal log_level!=INFO component=*
| stats min(_time) as _time max(_time) as LatestTime by component
| eval duration=ceil(LatestTime-_time)
| search duration>0
| sort 0 - duration
| head 10
| eval _time=now()
| eval duration_label=tostring(duration,"duration")
| eval duration_label=replace(duration_label,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2:\3:\4")
| fields _time component duration_label duration

PS: I have retained Top 10 durations and (omitted 0 sec duration components). Visualization will give a horizontal scroll bar if there are more rows than the panel height. (For 10 row, I have kept height as 400).

For the visualization Tooltip and Label I have used HH:MM:SS but hidden both the values using CSS override.
I have also converted Tooltip Text color for Timeline Custom visualization to White instead of default grey which is barely visible.

alt text

Following is the complete run anywhere Simple XML Dashboard code provided you have Timeline Custom Visualization.

<form>
  <label>Timeline to show duration as tooltip</label>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="time" token="field1">
        <label>For Testing duration only</label>
        <default>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </default>
      </input>
      <html depends="$alwaysHideCSSPanel$">
          <!-- 
               Tooltip CSS override applies to all tooltip for Timeline Custom Visualization,
               as it is not created within viz DOM.
               Tooltip is linked with id which can be dynamically applied only through JS
          -->
        <style>
          #my_timeline g.axis g.tick text{
            visibility: hidden !important;
          }
          div.splunk-timeline-tooltip div.tooltip-inner p.time-span-label{
            visibility: hidden !important;
          }
          div.splunk-timeline-tooltip div.tooltip-meta p{
            color: white !important;
          }
        </style>
      </html>
      <viz id="my_timeline" type="timeline_app.timeline">
        <title>Top 10 cooked up Duration by Component</title>
        <search>
          <query>index=_internal log_level!=INFO component=*
| stats min(_time) as _time max(_time) as LatestTime by component
| eval duration=ceil(LatestTime-_time)
| search duration>0
| sort 0 - duration
| head 10
| eval _time=now()
| eval duration_label=tostring(duration,"duration")
| eval duration_label=replace(duration_label,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2:\3:\4")
| fields _time component duration_label duration</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <!-- Height of panel increased -->
        <option name="drilldown">none</option>
        <option name="height">400</option>
        <option name="refresh.display">progressbar</option>
        <option name="timeline_app.timeline.axisTimeFormat">MINUTES</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#DA5C5C</option>
        <option name="timeline_app.timeline.minColor">#FFE8E8</option>
        <option name="timeline_app.timeline.numOfBins">6</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">MINUTES</option>
        <option name="timeline_app.timeline.useColors">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

Inverted query will be even better i.e. | fields _time duration_label component duration instead of | fields _time component duration_label duration

index=_internal log_level!=INFO component=*
 | stats min(_time) as _time max(_time) as LatestTime by component
 | eval duration=ceil(LatestTime-_time)
 | search duration>0
 | sort 0 - duration
 | head 10
 | eval _time=now()
 | eval duration_label=tostring(duration,"duration")
 | eval duration_label=replace(duration_label,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2:\3:\4")
 | fields _time duration_label  component duration

alt text

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

niketn
Legend

@smoir_splunk You can use Timeline Custom Visualization with some Simple XML CSS extension to display duration in Human readable format like HH:MM:SS (I have added string format to also handle days HH:MM:SS for duration >24 hours).

In the SPL I have added a dummy Time field as current time using now() (since Timeline Custom Visualization needs Time field).
I have calculated some dummy duration values in seconds. Using tostring() and replace() evaluation functions, I have converted duration to Day HH:MM:SS format duration_label.

 index=_internal log_level!=INFO component=*
| stats min(_time) as _time max(_time) as LatestTime by component
| eval duration=ceil(LatestTime-_time)
| search duration>0
| sort 0 - duration
| head 10
| eval _time=now()
| eval duration_label=tostring(duration,"duration")
| eval duration_label=replace(duration_label,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2:\3:\4")
| fields _time component duration_label duration

PS: I have retained Top 10 durations and (omitted 0 sec duration components). Visualization will give a horizontal scroll bar if there are more rows than the panel height. (For 10 row, I have kept height as 400).

For the visualization Tooltip and Label I have used HH:MM:SS but hidden both the values using CSS override.
I have also converted Tooltip Text color for Timeline Custom visualization to White instead of default grey which is barely visible.

alt text

Following is the complete run anywhere Simple XML Dashboard code provided you have Timeline Custom Visualization.

<form>
  <label>Timeline to show duration as tooltip</label>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="time" token="field1">
        <label>For Testing duration only</label>
        <default>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </default>
      </input>
      <html depends="$alwaysHideCSSPanel$">
          <!-- 
               Tooltip CSS override applies to all tooltip for Timeline Custom Visualization,
               as it is not created within viz DOM.
               Tooltip is linked with id which can be dynamically applied only through JS
          -->
        <style>
          #my_timeline g.axis g.tick text{
            visibility: hidden !important;
          }
          div.splunk-timeline-tooltip div.tooltip-inner p.time-span-label{
            visibility: hidden !important;
          }
          div.splunk-timeline-tooltip div.tooltip-meta p{
            color: white !important;
          }
        </style>
      </html>
      <viz id="my_timeline" type="timeline_app.timeline">
        <title>Top 10 cooked up Duration by Component</title>
        <search>
          <query>index=_internal log_level!=INFO component=*
| stats min(_time) as _time max(_time) as LatestTime by component
| eval duration=ceil(LatestTime-_time)
| search duration>0
| sort 0 - duration
| head 10
| eval _time=now()
| eval duration_label=tostring(duration,"duration")
| eval duration_label=replace(duration_label,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2:\3:\4")
| fields _time component duration_label duration</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <!-- Height of panel increased -->
        <option name="drilldown">none</option>
        <option name="height">400</option>
        <option name="refresh.display">progressbar</option>
        <option name="timeline_app.timeline.axisTimeFormat">MINUTES</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#DA5C5C</option>
        <option name="timeline_app.timeline.minColor">#FFE8E8</option>
        <option name="timeline_app.timeline.numOfBins">6</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">MINUTES</option>
        <option name="timeline_app.timeline.useColors">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

@niketnilay,

Agree with you timeline visualization is really nice App. Just one update, it has one bug. If you have only one bar to show it will display bar first and then the title. To solve this I've applied below solution in application.js file (line no. 37568 in version 1.2.0).

var scaleFactor = (1/(ending - beginning)) * (width - margin.left - margin.right);
if(ending == beginning){
    scaleFactor = 0;
}

Search for first line and add below three lines.

0 Karma

smoir_splunk
Splunk Employee
Splunk Employee

This worked super well for me! I haven't figured out how to get the duration to all start at the same time on the left, but it's close enough for now!

index=music source="streaminghistory.json" | stats sum(msPlayed) as totalMs by artistName | eval graph_duration=round(totalMs/1000),duration=totalMs | sort -totalMs limit=15 | rename artistName as artist | eval duration_label=tostring(round(totalMs/1000), "duration") | eval _time=graph_duration | fields _time artist duration_label duration

niketn
Legend

@smoir_splunk I had added the following code in my example to have the same _time added to each row of result. Since you already have the duration calculated you can add the following just before the final table command. Let me know how it goes.

 | eval _time = now()
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

smoir_splunk
Splunk Employee
Splunk Employee

Yeah, that's why I was using | eval _time=graph_duration instead of |eval _time=now() because that just produced small circles.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...