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

[dbcase]

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>

[micahkemp]

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.

[dbcase]

Well, progress 🙂

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

[micahkemp]

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?

[dbcase]

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.

[dbcase]

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>

[micahkemp]

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>

[dbcase]

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.

[dbcase]

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>