Dashboards & Visualizations

How can I convert a timerange token to an epoch value for filtering events on a different time field?

Communicator

We have a data source that has a default _time variable indexed correctly on UPDATE_TIME, which we can't change, but we have some searches where we want to filter and count by OPEN_TIME. As a result, I want to set up a timerange picker that will produce an epoch value for the token value, so I can compare OPEN_TIME to the token directly. For example here's what I want to do, which would work if all time token values were returned as epoch values:

index=sm9 source=incident 
| eval _time = strptime(OPEN_TIME,"%Y-%m-%d %H:%M:%S")
| where _time>$time.earliest$ AND _time<$time.latest$

This above doesn't work because not all timerange picker values return the epoch time, they could be in the form of epoch value (e.g. 1484063893), string representation (e.g., -7d@h), or 'now'. As a result, and based on other Splunk Answers posts, I tried to catch these options with logical statements (if, case), but those approaches did not work. For example:

index=sm9_us source=incident 
| eval _time = strptime(OPEN_TIME,"%Y-%m-%d %H:%M:%S"),
    earlytime=tonumber(if(isnum($time.earliest$), "$time.earliest$", relative_time(now(), "$time.earliest$"))),
    lasttime = tonumber(case(isnum($time.latest$), "$time.latest$", "$time.latest$"="now", now(), 1=1, relative_time(now(), "$time.latest$"))) 
| where _time>earlytime AND _time<lasttime

The problem is that the isnum() command will throw a malformed error if the token is a preset string representation (e.g., -7d@h):

Error in 'eval' command: The expression is malformed. Expected ).

However, if you put quotes around the token (isnum("token")) so it runs, then any token as an epoch number (e.g., 1484067160) will always return FALSE since it'll be a string.

Note: I also tried 'addinfo' command to get epoch-based fields (info_min_time, info_max_time), but that only works if you're also filtering the events based on the default indexed _time variable (which I am not; in fact that will return potentially wrong results).

So my question: how can I make a time token always return as an epoch-based variable or convert the token to an epoch?

Thanks

0 Karma

SplunkTrust
SplunkTrust
0 Karma

Champion

How about using gentimes to get the epoch value and generating the search string? Try this

 index=sm9_us source=incident
  [
    | gentimes start=-1 
    | addinfo 
    | rename info_min_time as earliest, info_max_time as latest 
    | fields earliest latest 
    | eval search = "(" + earliest + " < OPEN_TIME_LOCAL_TZ  AND OPEN_TIME_LOCAL_TZ <" + latest + ")" 
    | return $search
  ]
0 Karma

Communicator

I'm not sure what the gentimes command here actually does. But if i use your approach and try this (just comparing the _time field to test):

index=sm9_us source=interaction 
           [
             | gentimes start=-1 
             | addinfo 
             | rename info_min_time as earliest, info_max_time as latest 
             | fields earliest latest 
             | eval search = | search "(" + earliest + " < _time AND _time <" + latest + ")" 
             | return $search
           ]
          | timechart count span=1d

I get this error:

Error in 'eval' command: Failed to parse the provided arguments. Usage: eval dest_key = expression

Does the "addinfo" command actually work though if the search isn't using the timerange picker to filter the default _time field? That is, there aren't entries for earliest and latest after the query in the xml:

<query>....</query>
<earliest></earliest>  is not included  
<latest></latest> is not included
0 Karma

Champion

You get the error because you introduced typo in the eval search = ... line. You added a "|" character to mine which causes the problem.

Also, you said the original goal was to compare the time in "OPEN_TIME" not the inherent "_time" field, so you have to keep "OPEN_TIME" in that line if you use my approach because you are comparing the value of the field "OPEN_TIME" directly, not after you overwrite "_time".

Regarding your last question, addinfo will return the epoch values of the search period applied to the search. As long you did not change the name of the timepicker by giving it a new value for the "token" field, then the timepicker sets the default search period for all searches in your dashboard. If you are doing something more complicated with multiple timepickers, it would be more helpful if you could share more details about the different timepickers. Raw XML would be best.

0 Karma

Communicator

Oh yes sorry about that, I was trying to do an eval statement before the subsearch so I had added that in. Here is the XML for what I just tried using the gentimes approach, which returns no results:

<form>
  <label>Test_timepicker Clone</label>
  <fieldset submitButton="false">
    <input type="time" searchWhenChanged="true">
      <label></label>
    </input>
  </fieldset>

  <row>
    <panel>
      <chart>
        <search>
          <query>
          index=sm9_us source=interaction 
            [
              | gentimes start=-1 
              | addinfo 
              | rename info_min_time as earliest, info_max_time as latest 
              | fields earliest latest 
              | eval search =  "(" + earliest + " lt; _time AND _time lt;" + latest + ")" 
              | return $search
            ]
           | timechart count span=1d

          </query>
          <sampleRatio>1</sampleRatio>
        </search>
      </chart>
    </panel>
  </row>
</form>

However, I did just find an alternate way to do this which seems to work as expected:

<form>
  <label>Test_timepicker</label>
  <fieldset submitButton="false">
 <input type="time" searchWhenChanged="true">
   <label></label>
   <default>
     <earliest>-7d@w0</earliest>
     <latest>@w0</latest>
   </default>
   <change>
     <condition match="isnum($earliest$) OR isnum($latest$)">
       <eval token="etok">$earliest$</eval>
       <eval token="ltok">$latest$</eval>
     </condition>
     <condition>
       <eval token="etok">relative_time(now(), $earliest$)</eval>
       <eval token="ltok">relative_time(now(), $latest$)</eval>
     </condition>
   </change>
 </input>
  </fieldset>


  <row>
    <panel>
      <title>Earliest: $etok$  Latest:$ltok$</title>
      <chart>
        <search>
          <query>
            index=sm9_us source=interaction OPEN_TIME_LOCAL_TZ&gt;$etok$ OPEN_TIME_LOCAL_TZ&lt;$ltok$ 
            | eval _time=OPEN_TIME_LOCAL_TZ 
            | dedup INTERACTION_ID 
            | timechart count span=1d </query>
          <sampleRatio>1</sampleRatio>
        </search>
      </chart>
    </panel>
  </row>
</form>

It appears that the isnum() logical test won't return a malformed search when it's used within the input->change->condition tag. I've tried multiple presets and time ranges, and they all come back as epoch times. So now, as long as my other time fields is stored as an epoch time (e.g., OPEN_TIME_LOCAL_TZ in example above), then this works as expected.

Champion

First, the XML for the gentimes approach is missing ampersands. Also, as I said earlier, you should use the field "OPEN_TIME_LOCAL_TZ" instead of _time.

           index=sm9_us source=interaction 
             [
               | gentimes start=-1 
               | addinfo 
               | rename info_min_time as earliest, info_max_time as latest 
               | fields earliest latest 
               | eval search =  "(" + earliest + " &lt; OPEN_TIME_LOCAL_TZ AND OPEN_TIME_LOCAL_TZ &lt;" + latest + ")" 
               | return $search
             ]
            | timechart count span=1d
0 Karma

SplunkTrust
SplunkTrust

I guess you copied the eval search statement wrong. Check the query in answer.

0 Karma