Splunk dashboard timechart x-axis does not show hourly interval

[mmdacutanan]

I have got a query that I that I turned into a dashboard. It has two panels, one is a line chart and the other a statistics table. The query behind the chart looks like this:

index=ivr sourcetype=ivr_SEF applicationName=testApp CV4_TExCd!=000000 | lookup lookuptable.csv TeamCode AS CV4_TExCd OUTPUT Site AS Site| timechart span=1h count by Site useother=F

My problem is that on the line chart, the x axis only displays hour value at 4 hour intervals ( so 8PM - 12 AM - 4AM - 8AM -12PM -4PM ....) How do I tweak my xml code to display hourly ( 01AM -2AM- 3AM -4AM - 5AM, etc) intervals?

Here is the xml code:

Hello World
Dashboard Test

<panel>
  <chart>
    <search>
      <query>index=ivr sourcetype=ivr_SEF applicationName=testApp CV4_TExCd!=000000 | lookup lookuptable.csv TeamCode AS CV4_TExCd OUTPUT Site AS Site| timechart span=1h count by Site useother=F</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.axisLabelsX.majorUnit">P1D</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.scale">linear</option>
    <option name="charting.axisY.scale">linear</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.placement">right</option>
  </chart>
</panel>


<panel>
  <table>
    <search>
      <query>index=ivr sourcetype=ivr_SEF applicationName=testApp CV4_TExCd!=000000 | lookup lookupTable.csv TeamCode AS CV4_TExCd OUTPUT Site AS Site| timechart span=1h count by Site useother=F| sort - _time</query>
      <earliest>-24h@h</earliest>
      <latest>now</latest>
    </search>
    <option name="count">100</option>
    <option name="drilldown">none</option>
    <option name="totalsRow">true</option>
  </table>
</panel>

I have seen a similar post but it is really old. I tried it and it doesn't work for me:

https://answers.splunk.com/answers/91717/timechart-x-axis.html?utm_source=typeahead&utm_medium=newqu...

Please help!

[niketn · Accepted solution]

@mmdacutanan, Splunk Charts with _time on x-axis dynamically adjusts number of data points (or gap in time) based on the width of the chart i.e. you may get all hour labels on x-axis depending on the width of your display however, if you brought two timecharts in the same row (in other words divided the width in half), the number of data points may reduce dynamically.

The chart will also adjust x-axis label as you increase or decrease the size of browser. Following screenshot is at half the size as compared to full screen.

Following are three options you can try:

1) Use timechart but convert time to "%H:%M %p" using cascaded strptime() and strftime() i.e. 8:00 AM, 9:00 AM etc. Then rotate the labels to 90 degree using Simple XML Chart Configuration or through Splunk Web UI by Edit Panel.

2) Use chart over _time as"%H:%M %p" to plot series starting from first occurrence of using cascaded strptime() and strftime() i.e. 8:00 AM, 9:00 AM etc. Then rotate the labels to 90 degree using Simple XML Chart Configuration or through Splunk Web UI by Edit Panel.

3) Use chart with date_hour on x-axis to plot only hours with data.

Following is run anywhere dashboard based on Splunk _internal index for the screenshot below:

<dashboard>
  <label>Table x-Axis as Hours</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
|  timechart span=1h count by log_level
|  fillnull value=0
|  eval _time=strptime(strftime(_time,"%H:%M %p"),"%H:%M %p")</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-90</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.text">Error Count</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">zero</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</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>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
|  chart span=1h count over _time by log_level
|  fillnull value=0
|  eval _time=strptime(strftime(_time,"%H:%M %p"),"%H:%M %p")</query>
          <earliest>@d</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-90</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.text">Error Count</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">zero</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</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>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
|  chart count over date_hour by log_level</query>
          <earliest>@d</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">Error Count</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">zero</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</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>
</dashboard>

If you want to plot only hours on x-axis, I expect you are looking at only one day data like Today or Yesterday etc. However if you need to compare hourly data across several days, you should consider use of either Calendar Heatmap Custom Visualization: https://splunkbase.splunk.com/app/3162/ or Punchcard Custom Visualization: https://splunkbase.splunk.com/app/3129/, depending on your needs.

Hope this helps!

[mmdacutanan]

Hello niketnilay,

This is awesome! THis answered my question and taught me even more stuff. I really appreciate it. I ended up shortening my duration for last 24 to last 12 hrs and also rotating the x axis lables. This showed the hourly lables on my x axis.

Thanks so so much!!

[niketn]

Glad it worked. We all learn things from time to time and Splunk Answers is a great place to learn and share learning with others.