Splunk Search

How to produce a human readable date from $earliest$ or $latest$ variables from a date picker?

bfnpmsz
New Member

I have a dashboard form which contains several panels and are arranged in a 2 up format. The first column is the first week and the second column the second week of a two week window. The two week window can be picked from a date picker.

The date picker is returning $earliest$ and $latest$ as an epoch date, 1440997200.

What I want to do is convert the epoch date to a human readable format, %b %d, %Y, and place that into the title of each panel of the dashboard.

Here is my XML of the form. I hope it's not too confusing....

<form>
  <label>IT Governance</label>
  <fieldset autoRun="true" submitButton="false">
    <input type="time" searchWhenChanged="true">
      <default>
        <earliestTime>-14d@w1</earliestTime>
        <latestTime>@w0</latestTime>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <chart>
        <title>Top 10 Ticketed ATM's - $earliest$ to $latest$</title>
        <search>
          <query>* ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ [search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 | reverse | head 1 | addinfo | eval earliest = relative_time(info_min_time, "@w1") | eval latest = relative_time(info_max_time, "-6d@w1") | fields earliest latest | format "(" "(" " " ")" "OR" ")" ]  | top  limit=10 InstanceName</query>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">45</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">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</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.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">minimal</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <search>
          <query>* ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ [search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 | reverse | head 1 | addinfo | eval earliest = relative_time(info_min_time, "+7d@w1") | eval latest = relative_time(info_max_time, "@w1") | fields earliest latest | format "(" "(" " " ")" "OR" ")" ]  | top  limit=10 InstanceName</query>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">45</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">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</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.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">minimal</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
  </row>
</form>

As you can see, I can put the $earliest$ and $latest$ into the form and get the epoch back. I want to get a human readable date format so the user can see what the date range is for each panel.

Any assistance would be appreciated.

0 Karma
1 Solution

MuS
Legend

Hi bfnpmsz,

I'm not sure if this will work at all, because using $earliest$ is returning epoch - so it would need some conversion first.
You can have a look at this run everywhere dashboard which uses a simple panel on top to display the search time range:

<form>
  <label>Display search time ranges in dashboard</label>
  <description></description>
  <fieldset submitButton="false">
    <input type="time" token="time">
      <label>Select time range:</label>
      <default>
        <earliest>@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <single>
        <search>
          <query>|gentimes start=-1 | addinfo | convert ctime(*) | eval reportDate=info_min_time." to ".info_max_time | table reportDate</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="beforeLabel">Report Date:</option>
        <option name="linkView">search</option>
        <option name="afterLabel">.</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>Index _internal sourcetype count:</title>
        <search>
          <query>index=_internal | stats count by sourcetype</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </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.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">column</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">all</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>
  </row>
</form>

Hope this helps ...

cheers, MuS

View solution in original post

MuS
Legend

Hi bfnpmsz,

I'm not sure if this will work at all, because using $earliest$ is returning epoch - so it would need some conversion first.
You can have a look at this run everywhere dashboard which uses a simple panel on top to display the search time range:

<form>
  <label>Display search time ranges in dashboard</label>
  <description></description>
  <fieldset submitButton="false">
    <input type="time" token="time">
      <label>Select time range:</label>
      <default>
        <earliest>@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <single>
        <search>
          <query>|gentimes start=-1 | addinfo | convert ctime(*) | eval reportDate=info_min_time." to ".info_max_time | table reportDate</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="beforeLabel">Report Date:</option>
        <option name="linkView">search</option>
        <option name="afterLabel">.</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>Index _internal sourcetype count:</title>
        <search>
          <query>index=_internal | stats count by sourcetype</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </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.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">column</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">all</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>
  </row>
</form>

Hope this helps ...

cheers, MuS

bfnpmsz
New Member

MuS, Thanks for the quick response.

Your answer is not exactly what I need but I think we are on the right track now.

Your solution does put the date range in human readable form at the top of the dashboard in its own panel, but....

I want the individual date ranges for each panel. The left panel will be week #1 and the right panel will be week #2.

So my overall date range would be "08/31/2015 to 09/13/2015", that is shown on the top panel. Below that panel, the panel to the left would show the range "08/31/2015 to 09/6/2015" and the one to the right will show "09/07/2015 to 09/13/2015".

I'm sure there has to be a way to get this outcome.

0 Karma

bfnpmsz
New Member

Mus,

I added two panels to my dashboard which define the date range of the panels in that column. That was the best I could come up with.

Thanks for your help. Your solution showed me the way and then some.

Thanks again

0 Karma

MuS
Legend

Just tweak your dashboard to show it that way, I just provided an example to show you how it can be done.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...