Chart with Trendline in splunk

[karthi25]

I want to create a chart with hours as y-axis and Date as X-axis and draw a line based on the time difference between two days.

Values in the chart: ProcessLogId
Chart should based on : Time Difference of startDate and EndDate.

So, My chart will be like

My data will be like:

{processLogId=738528, startDate=2017-12-20 17:44:38.0, endDate=2017-12-20 17:44:45.0, status='COMPLETED'}
{processLogId=728528, startDate=2017-12-19 16:24:38.0, endDate=2017-12-19 16:44:45.0, status='COMPLETED'}
{processLogId=768528, startDate=2017-12-19 17:44:38.0, endDate=2017-12-19 17:44:45.0, status='COMPLETED'}
{processLogId=738928, startDate=2017-12-18 13:20:21.0, endDate=2017-12-18 13:22:21.0, status='COMPLETED'}
{processLogId=712528, startDate=2017-12-17 18:22:01.0, endDate=2017-12-17 18:22:10.0, status='COMPLETED'}

Can Anyone please help me to do it.

[niketn · Accepted solution]

@karthi25, Ideally you should be using Timeline Custom Visualization for plotting duration with Time. Following are some of the options that you may try:
1) Show Line Chart with Event Annotation to pull Process ID overlaid (requires Splunk Enterprise 7.0)
2) Categorical Line Chart each point is one Process ID
3) Timeline Custom Visualization to plot duration

Following is the run anywhere dashboard based on your sample data (commands till | eval _time generate dummy data as per the request:

<dashboard>
  <label>Process Duration with Event Annotation</label>
  <row>
    <panel>
      <title>Option 1 With Annotation with Process ID (Splunk Enterprise 7.0 onward)</title>
      <chart>
        <search type="annotation">
          <query>| makeresults
| eval data=" {processLogId=738528, startDate=2017-12-20 17:44:38.0, endDate=2017-12-20 17:44:45.0, status='COMPLETED'};{processLogId=728528, startDate=2017-12-19 16:24:38.0, endDate=2017-12-19 16:44:45.0, status='COMPLETED'};{processLogId=768528, startDate=2017-12-19 17:44:38.0, endDate=2017-12-19 17:44:45.0, status='COMPLETED'};{processLogId=738928, startDate=2017-12-18 13:20:21.0, endDate=2017-12-18 13:22:21.0, status='COMPLETED'};{processLogId=712528, startDate=2017-12-17 18:22:01.0, endDate=2017-12-17 18:22:10.0, status='COMPLETED'}"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval status=replace(status,"'","")
| eval _time=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval annotation_label = "Process Log Id: ".processLogId
| eval annotation_category = status
          </query>
        </search>
        <search>
          <query>| makeresults
| eval data=" {processLogId=738528, startDate=2017-12-20 17:44:38.0, endDate=2017-12-20 17:44:45.0, status='COMPLETED'};{processLogId=728528, startDate=2017-12-19 16:24:38.0, endDate=2017-12-19 16:44:45.0, status='COMPLETED'};{processLogId=768528, startDate=2017-12-19 17:44:38.0, endDate=2017-12-19 17:44:45.0, status='COMPLETED'};{processLogId=738928, startDate=2017-12-18 13:20:21.0, endDate=2017-12-18 13:22:21.0, status='COMPLETED'};{processLogId=712528, startDate=2017-12-17 18:22:01.0, endDate=2017-12-17 18:22:10.0, status='COMPLETED'}"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval status=replace(status,"'","")
| eval _time=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| search status="Completed"
| eval startDate=strptime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval endDate=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat startDate=strftime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat endDate=strftime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval durationInMin=round((endDate-startDate)/60)
| timechart span=1d latest(durationInMin) as duration</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.annotation.categoryColors">{"COMPLETED":"0x65a637"}</option>
        <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.text">Duration (in min)</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">line</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">gaps</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="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>
      <title>Option 2 Process ID as category</title>
      <chart>
        <search>
          <query>| makeresults
| eval data=" {processLogId=738528, startDate=2017-12-20 17:44:38.0, endDate=2017-12-20 17:44:45.0, status='COMPLETED'};{processLogId=728528, startDate=2017-12-19 16:24:38.0, endDate=2017-12-19 16:44:45.0, status='COMPLETED'};{processLogId=768528, startDate=2017-12-19 17:44:38.0, endDate=2017-12-19 17:44:45.0, status='COMPLETED'};{processLogId=738928, startDate=2017-12-18 13:20:21.0, endDate=2017-12-18 13:22:21.0, status='COMPLETED'};{processLogId=712528, startDate=2017-12-17 18:22:01.0, endDate=2017-12-17 18:22:10.0, status='COMPLETED'}"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval status=replace(status,"'","")
| eval _time=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| search status="Completed"
| eval startDate=strptime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval endDate=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat startDate=strftime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat endDate=strftime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval durationInMin=round((endDate-startDate)/60)
| timechart span=1d latest(durationInMin) as duration by processLogId</query>
          <earliest>-24h@h</earliest>
          <latest>now</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.text">Duration (in min)</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">line</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">gaps</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="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Timeline to plot Process ID duration over time (Timeline Custom Visualization)</title>
      <viz type="timeline_app.timeline">
        <search>
          <query>| makeresults
| eval data=" {processLogId=738528, startDate=2017-12-20 17:44:38.0, endDate=2017-12-20 17:44:45.0, status='COMPLETED'};{processLogId=728528, startDate=2017-12-19 16:24:38.0, endDate=2017-12-19 16:44:45.0, status='COMPLETED'};{processLogId=768528, startDate=2017-12-19 17:44:38.0, endDate=2017-12-19 17:44:45.0, status='COMPLETED'};{processLogId=738928, startDate=2017-12-18 13:20:21.0, endDate=2017-12-18 13:22:21.0, status='COMPLETED'};{processLogId=712528, startDate=2017-12-17 18:22:01.0, endDate=2017-12-17 18:22:10.0, status='COMPLETED'}"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval status=replace(status,"'","")
| eval _time=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| search status="Completed"
| eval startDate=strptime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval endDate=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat startDate=strftime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
| fieldformat endDate=strftime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
| eval durationInSec=round((endDate-startDate))*1000
| bin _time span=1d
| stats  latest(durationInSec) as duration by _time processLogId status</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="timeline_app.timeline.axisTimeFormat">DAYS</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#65a637</option>
        <option name="timeline_app.timeline.minColor">#65a637</option>
        <option name="timeline_app.timeline.numOfBins">3</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">SECONDS</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>
</dashboard>

[cmerriman]

something like this should be able to help. You need to put the start and end dates into epoch form to make then numbers instead of date strings, and then set _time to one of them. I divided the duration by 3600 to put it into hours (you can round, or tweak that eval as you need). You can tweak the timechart also, to do to sum/avg/etc. if a processLogId could be logged more than once in a day.

|eval start_epoch=strptime(startDate,"%Y-%m-%d %H:%M:%S.%1N")
|eval _time=start_epoch
|eval end_epoch=strptime(endDate,"%Y-%m-%d %H:%M:%S.%1N")
|eval duration=(end_epoch-start_epoch)/3600
|timechart span=1d values(duration) by processLogId

[karthi25]

thanks for your reply.. But it returns no result

[cmerriman]

Are your fields extracted or do you need to extract them yet?