topic Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros? in Splunk Search

Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 06 Feb 2018 17:19:53 GMT

Hi,

I have this XML code where I'm attempting to convert the clicked time in epoch format into a human readable time but for some reason the hetime and hltime return all zeros no matter what I try. Any thoughts?

<drilldown>
          <set token="clicked_earliest">$earliest$</set>
          <set token="clicked_latest">$latest$</set>

          <eval token="hetime">strftime($clicked_earliest$, "%H:%M:%S")</eval>
          <eval token="hltime">strftime($clicked_latest$, "%H:%M:%S")</eval>
        </drilldown>

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 06 Feb 2018 17:25:06 GMT

Thinking it might be a problem of the quotes in the strftime function so I tried the below but still no luck

<eval token="hetime">strftime($clicked_earliest$, &quot;%H:%M:%S&quot;)</eval>
          <eval token="hltime">strftime($clicked_latest$, &quot;%H:%M:%S&quot;)</eval>

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

micahkemp — Tue, 06 Feb 2018 17:29:50 GMT

Try this:

 <drilldown>
           <set token="clicked_earliest">$earliest$</set>
           <set token="clicked_latest">$latest$</set>

           <eval token="hetime">strftime("$clicked_earliest$", "%H:%M:%S")</eval>
           <eval token="hltime">strftime("$clicked_latest$", "%H:%M:%S")</eval>
</drilldown>

$token_name$ will be inserted literally into your query, so the eval ended up looking like:

strftime(1234567890, "%H:%M:%S")

instead of

strftime("1234567890", "%H:%M:%S")

In the former, stftime is looking for a field named 01:23:45, which isn't going to exist.

After editing this to look like actual timestamps, though, stftime should be able to take a timestamp (which is just an integer) without the quotes.

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 06 Feb 2018 17:38:53 GMT

Well, progress 🙂

Instead of all zeros... now I get "Invalid Date" . very strange

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

micahkemp — Tue, 29 Sep 2020 18:00:34 GMT

I should have used a timestamp in my example instead of something already in HH:MM:SS format. Are $clicked_earliest$ and $clicked_latest$ already in unix timestamp format?

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 29 Sep 2020 18:00:36 GMT

Hi Micahkemp,

I believe so, if I click on one of the columns in the graph $clicked_earliest$ and $clicked_latest$ resolve to 1517464800 1517551200 respectively.

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 06 Feb 2018 18:22:58 GMT

In case it helps, here is a more complete snippet of the dashboard

<row>
    <panel>
      <title>Playback vs Error over time</title>
      <chart>
        <title>Click a column for additional detail</title>
        <search>
          <query>index=wholesale_app buildTarget=$mso_selection$ product=$Product_token$ CustomAnalytic Properties.index=31 OR Properties.index=17|rename Properties.index as pindex|eval pindexname=if(pindex="17","CVR Playback", "CVR Error")| eval pindexname=pindex." - ".pindexname|timechart count by pindexname</query>
          <earliest>$time_field.earliest$</earliest>
          <latest>$time_field.latest$</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">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">log</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">log</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.overlayFields">"CVR Error"</option>
        <option name="charting.chart.showDataLabels">all</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>
        <drilldown>
          <set token="clicked_earliest">$earliest$</set>
          <set token="clicked_latest">$latest$</set>
          <eval token="clicked_group">substr($click.name2$,0,2)</eval>
          <eval token="state">if($clicked_group$="31",  "stats values(accountid) as accountid values(mac_address) as mac  values(error_msg) as Error_Message values(cmodel) as model values(cversion) as version by clientSessionId", "stats values(accountid) as accountid values(mac_address) as mac values(cmodel) as model values(cversion) as version by clientSessionId")</eval>
          <eval token="statevalue">if($clicked_group$="31", "errored sessions", "successful sessions")</eval>
          <eval token="hetime">strftime("$clicked_earliest$", "%H:%M:%S")</eval>
            <eval token="hltime">strftime("$clicked_latest$", "%H:%M:%S")</eval>
        </drilldown>
      </chart>
    </panel>
  </row>
  <row>
    <panel depends="$clicked_group$">
      <title>Playback vs Error details for $statevalue$ $clicked_earliest$ $clicked_latest$ $hetime$ to $hltime$</title>
      <table>
        <title>(Click any row to close)</title>
        <search>
          <query>index=wholesale_app   [search index=wholesale_app buildTarget=$mso_selection$ product=$Product_token$ CustomAnalytic Properties.index="$clicked_group$" |table clientSessionId]  |rex "cameraMACAddress.....(?&lt;mac_address&gt;[^\\\]+)"|rex "cameraFirmwareVersion..:..(?&lt;cversion&gt;[^\\\]+)"|rex "cameraModel..:..(?&lt;cmodel&gt;[^\\\]+)"|rex  "errorMessage.....(?&lt;error_msg&gt;[^\\\\]+)"|$state$|mvexpand mac|rename accountid as "Account ID" mac as "MAC Address" model as "Camera Model" version as "Camera Firmware Version"</query>
          <earliest>$clicked_earliest$</earliest>
          <latest>$clicked_latest$</latest>
        </search>
        <option name="refresh.display">preview</option>
        <drilldown>
          <unset token="clicked_earliest"></unset>
          <unset token="clicked_latest"></unset>
          <unset token="clicked_group"></unset>
        </drilldown>
      </table>
    </panel>
  </row>

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

micahkemp — Tue, 06 Feb 2018 18:56:02 GMT

It looks like you're hoping the drilldown actions get performed sequentially, and I'm not sure that's how it works (I'm also not sure it's not how it works).

Is there a reason you want to run your eval against a set token, when you could just run it against the value that token would be set to?

<eval token="hetime">strftime("$earliest$", "%H:%M:%S")</eval>

Re: Why is the conversion of epoch time to human readable time (hetime and hltime) returning all zeros?

dbcase — Tue, 06 Feb 2018 22:04:07 GMT

No reason and I thought surely that would work but alas, still getting invalid date as the value for hetime.... I'm thinking I'm trying to do something that Splunk just fundamentally doesn't like.