Solved: Re: How can I graph the string/human-readable valu...

smoir_splunk · ‎06-11-2019

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

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?

niketn · ‎06-12-2019

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

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

niketn · ‎06-12-2019

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

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

niketn · ‎06-12-2019

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

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!!!"

VatsalJagani · ‎06-13-2019

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

smoir_splunk · ‎06-13-2019

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!

niketn · ‎06-13-2019

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

smoir_splunk · ‎06-13-2019

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

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

Enterprise Security Content Update (ESCU) | New Releases

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

Index This | What are the 12 Days of Splunk-mas?