All Apps and Add-ons

epoch time from CSV as search parameter

Explorer

Hi All,

I have a CSV with 4 fields:

timestamp,num1,num2,num3

1352714400,5,50,231

1352800800,6,143,500

etc.(timestamp is generated with "| eval timestamp = now()")


I'd like to create a line chart using these fields and convert to drilldown to which I'd like to pass timerange parameters: timestamp as latest and timestamp-24h as earliest.

Here is my code:


<module name="Search">

   <param name="search">| inputlookup lookupname | convert timeformat="%m/%d/%Y" mktime(timestamp) as _time | fields _time,num1,num2,num2</param>

<module name="Search">
  <param name="search">| inputlookup lookupname | convert timeformat="%m/%d/%Y" mktime(timestamp) as _time | fields _time,num1,num2,num2</param>

    <module name="HiddenChartFormatter">
      <param name="charting.chart">line</param>
      <param name="charting.legend.placement">right</param>

      <module name="FlashChart">
        <param name="width">100%</param>
        <param name="height">160px</param>

        <module name="ValueSetter">
          <param name="name">latest</param>
          <param name="value">$click.value$</param>

          <module name="PostProcess">
            <param name="search">| eval earliest=relative_time("$latest$", "-1d")</param>

            <module name="ResultsValueSetter">
              <param name="fields">earliest</param>

              <module name="PostProcess">
                <param name="search"></param>

                <module name="Search">
                  <param name="search">search-for-drilldown | stats count(something) by something </param>
                  <param name="earliest">$earliest$</param>
                  <param name="latest">$latest$</param>
 etc

I'm not sure about correct usage of epoch time nor in conversion is the main search (I converted it for human-readable reasons) and if I run this I get an "invalid earliest_time" error message.

If I do not convert anything and work only with timestamp values (postprocess eval changes to "| eval earliest=$latest$-86400") error message is the same. It prints out the correct values anyways...

What am I missing?



Thanks in advance,
Jagresz

0 Karma

SplunkTrust
SplunkTrust

OK. First, the _time handling in JSChart, FlashChart, SimpleResultsTable and even the Table module is a little unintuitive. In the end the differences can be quite useful though. In short under the hood these modules all convert the "main" timerange to be the drilldown timerange automatically. And they convert the _time values in the UI to be string-formatted local times.

So when you try and wire things up yourself with things like $click.value$, even when you know _time is epochtime-valued (ie number of seconds since 1970), the $click.value$ keys downstream from it will be the locale-aware string-formatted time.

So you have to kind of take a step back and let the system do the work for you -- get the drilldown timerange from $search.timeRange.earliest$ (by the way this $search.timeRange.*$ convention is actually something that Sideview Utils adds, although it refers to the "main" Splunk timerange in effect at that point in the hierarchy.)

But anyway, here's an initial example, where I'm using ValueSetter and ResultsValueSetter to do things manually, but ultimately leveraging the $search.timeRange.*$ keys underneath the FlashChart to do it.

<module name="Search">
  <param name="search">| stats count | fields - count | eval foo="1355333734,1355333634,1355333534,1355333134,1355333134,1355333034" | eval foo=split(foo,",") | mvexpand foo | rename foo as _time | timechart count</param>

  <module name="HiddenChartFormatter">
    <param name="charting.chart">line</param>
    <param name="charting.legend.placement">right</param>

    <module name="FlashChart">
      <param name="width">100%</param>
      <param name="height">160px</param>

      <module name="ValueSetter">
        <param name="name">latest</param>
        <param name="value">$search.timeRange.latest$</param>

        <module name="PostProcess">
          <param name="search">| eval earliest=relative_time("$latest$", "-1d")</param>   

          <module name="ResultsValueSetter">
            <param name="fields">earliest</param>

            <module name="Search">
              <param name="search">index=* OR index=_* | head 10000 | timechart count by sourcetype</param>
              <param name="earliest">$earliest$</param>
              <param name="latest">$latest$</param>

              <!-- useful just so we can check the job inspector -->
              <module name="SearchControls"></module>

              <module name="HTML">
                <param name="html"><![CDATA[
                 DEBUGGING: <br>
                 earliest= $search.timeRange.earliest$<br>
                 latest = $search.timeRange.latest$<br>
                 label = $search.timeRange.label$<br>
                 <br>
                 search = $search$<br>

                ]]></param>
              </module>
            </module>
          </module>
        </module>
      </module>
    </module>
  </module>
</module>

NOTE: as long as you're on current Sideview Utils (>2.2.7), that old workaround of having to clear out old PostProcess searches with a blank PostProcess, is no longer necessary. The underlying Splunk systems are patched from Sideview Utils so that problem has been resolved.

But here's another example where we actually lean even further back and rely on the legacy support for earliest/latest/now in the search language itself. Note that this will produce annoying messages in the UI to the effect of "the timerange was substituted based on your searchstring", but still I think it's an interesting counterexample. See below.

<module name="Search">
  <param name="search">| stats count | fields - count | eval foo="1355333734,1355333634,1355333534,1355333134,1355333134,1355333034" | eval foo=split(foo,",") | mvexpand foo | rename foo as _time | timechart count</param>

  <module name="HiddenChartFormatter">
    <param name="charting.chart">line</param>
    <param name="charting.legend.placement">right</param>

    <module name="FlashChart">
      <param name="width">100%</param>
      <param name="height">160px</param>

      <module name="Search">
        <param name="search">now="$search.timeRange.latest$" earliest="-1d" index=* OR index=_* | head 1000 | stats count by sourcetype</param>

        <!-- just here so we can look at the job inspector to test things --> 
        <module name="SearchControls"></module>

        <module name="HTML">
          <param name="html"><![CDATA[
           DEBUGGING: <br>
           earliest= $search.timeRange.earliest$<br>
           latest = $search.timeRange.latest$<br>
           search = $search$<br>

          ]]></param>
        </module>
      </module>
    </module>
  </module>
</module>
0 Karma