How do you use an eval command to calculate 'latest' for use in a search?

[_smp_]

I have crafted the following search that calculates a value for the 'latest' field relative to 'earliest' and uses it in a search. But the time window in the search result shows those values are ignored.

Can you help me understand why this technique doesn't work?

| makeresults
| fields - _time
| eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S")
| eval latest=relative_time(earliest,"+2d@d")
| eval earliest=strftime(earliest,"%m/%d/%Y:%H:%M:%S"), latest=strftime(latest,"%m/%d/%Y:%H:%M:%S")
| search index=aws sourcetype=aws:guardduty earliest=earliest latest=latest

[petom · Accepted solution]

@kamlesh_vaghela was on the right direction, just that the subsearch has to return a valid search string. and his subsearch does not end creating a plain string, which can be used in parent search

try the following:

index=aws sourcetype=aws:guardduty [ | makeresults | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S") | eval latest=relative_time(earliest,"+2d@d") | return earliest latest ]

@scottprigge to answer your question, why your technique doesn't work. It is because in your search query you are creating a result set first and then you add filter using search command. However, this search command will only filter events from the results you are already passing to it and those results do not contain fields index and sourcetype and don't have a timestamp (you removed field _time), which you are also trying to filter on by adding earliest and latest constraints.
To show you what you are actually doing in your expression, try to modify it by removing all conditions in the search command. Something like this:

 | makeresults
 | fields - _time
 | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S")
 | eval latest=relative_time(earliest,"+2d@d")
 | eval earliest=strftime(earliest,"%m/%d/%Y:%H:%M:%S"), latest=strftime(latest,"%m/%d/%Y:%H:%M:%S")
 | search 

If it is still not clear, let's extend your query even more by adding some additional field, e.g. foo, and play with filtering by that field:

 | makeresults
 | fields - _time
 | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S")
 | eval latest=relative_time(earliest,"+2d@d")
 | eval earliest=strftime(earliest,"%m/%d/%Y:%H:%M:%S"), latest=strftime(latest,"%m/%d/%Y:%H:%M:%S")
 | eval foo="bar"
 | search foo="ba*"

What confused you is that search command, when it is first in the query pipeline, then it retrieves data from the indexes according to the condition. And

| search index=aws sourcetype=aws:guardduty

is the same as just

index=aws sourcetype=aws:guardduty

But when it is anywhere else in the pipeline, then it filters events arriving to it from previous command.

[_smp_]

Lots of good technique in here. Thanks for the post!

[_smp_]

Between my colleague's understanding of Simple XML and my understanding of EVAL, we were able to figure this out. P1 uses the Time Picker for the initial search and includes stats table with a field named 'event_time'. When the user clicks on one of the resulting rows in the P1 table, it uses the value of the 'event_time' field to calculate two new files fields that are used for time constraints in P2-5.

The 'drilldown' and 'condition' XML tags are getting stripped - the eval tag is surrounded by empty ones.
P1

<eval token="earliest_offset">relative_time(strptime($row.event_time$,"%m/%d/%Y %H:%M:%S"),"-1h@h")</eval>
<eval token="latest_offset">relative_time(strptime($row.event_time$,"%m/%d/%Y %H:%M:%S"),"+1h@h")</eval>

The 'earliest' and 'latest' XML tags are getting stripped.
P2-5

$earliest_offset$

$latest_offset$

[woodcock]

Doing earliest and latest in a subsearch is tricky and requires special handling, including only using integer values and eliminating double-quotes. Try this for your subsearch; it will work unless timestamp is not a number (it must be an integer time_t😞

 index=aws sourcetype=aws:guardduty
 [| makeresults
  | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S")
  | eval latest=relative_time(earliest,"+2d@d")
  | format "" "" "" "" "" ""
  | rex field=search mode=sed "s/\"//g"]

[_smp_]

Yes, this also seems to work.

[macadminrohit]

It didnt work because you didnt pass the epoch time to the search in earliest and latest.

[kamlesh_vaghela]

@scottprigge

Can you please try this?

 index=aws sourcetype=aws:guardduty  [| makeresults | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S") | eval latest=relative_time(earliest,"+2d@d") | table earliest latest]  

Thanks

[_smp_]

Thanks for the response. I did try this approach for a while, but kept running into this problem:

Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side.

[kamlesh_vaghela]

@scottprigge

Can you please try this?

index=aws sourcetype=aws:guardduty  [| makeresults | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S") | eval latest=relative_time(earliest,"+2d@d") | table earliest latest | format "(" "(" "" ")" "AND" ")"]

Also, want to know about Splunk version.

[_smp_]

Yes, this also seems to work. Thanks for the input.

[skoelpin]

It's because your formatting the timestamp away from epoch on line 5. Splunk uses epoch time exclusively to do calculations. It should look like this. Notice I added human after earliest and latest on line 5 and kept the earliest and latest without human on line 6. Next question is, whats the point of doing it this way? I've created a similar use-case in a dashboard where a hidden panel accepts the epoch values from the timerange picker, then offsets them by 7 days in the past to compare historical values against now. If this is what you're trying to do then I can go look for my old answer which as all the SPL

 | makeresults
 | fields - _time
 | eval earliest=strptime("01/15/2019:20:00:00","%m/%d/%Y:%H:%M:%S")
 | eval latest=relative_time(earliest,"+2d@d")
 | eval earliest_human=strftime(earliest,"%m/%d/%Y:%H:%M:%S"), latest_human=strftime(latest,"%m/%d/%Y:%H:%M:%S") 
   | search index=aws sourcetype=aws:guardduty earliest=earliest latest=latest

[_smp_]

Thanks for there response. I converted from epoch to a string because of this statement the Time Modifiers documentation for earliest and latest:

You can specify an exact time such as earliest="10/5/2016:20:00:00"

But when I copy/paste your search, it doesn't seem to work either. The time window in the search results still reflects the time range specified by the Time Picker, not earliest/latest.

The reason I posted this is two-fold:
1. To increase my knowledge about SPL - this is not the first time when I've had difficulty using field values later in the search pipeline. Quite often, I'm left asking myself if I have to surround a field name by single quotes, $$, or just use the field name.
2. This search is being run ad-hoc, not from a dashboard. I certainly could create a dashboard for it, but this brings me back to point #1.

[skoelpin]

The reason it's not working is because your using a generating command to start your SPL then trying to tack on | search index=.. If you start with makeresults then you cant pipe into indexed data since Splunk is only looking at the newly generated data. You could forgo the |makeresults all together and use simple evals. I'm still unsure about your use-case and why you would want to do it this way. The only way this would make sense is if you had a dashboard and wanted to pass the time from the timerange picker to the field and do some type of math to it and pass the new results to other commands.

As for your point one. Single quotes around the field represent the value you want from the field so assuming this foo=barr and you had | eval newfield='foo', your newfield value would be bar. If you put double quotes around them like this | eval newfield="foo" it would be foo since your explicitly wanting the value with double quotes. As for $$, those are reserved for tokens which are heavily used in dashboards to pass values. Try for yourself, change the single quotes to doubles on the 3rd line and watch the output change

| makeresults 
| eval foo="bar"
| eval new='foo'
| fields + new

[_smp_]

f you start with makeresults then you cant pipe into indexed data since Splunk is only looking at the newly generated data.
Ooohhh, of course. That makes perfect sense, thank you for that clarity.

I posted this question on behalf of a colleague without fully understanding his use case, so I had a chat with him. He is indeed using a dashboard with a time picker and two panels. He wants to use the time picker as the constraint of the first panel, which includes a field with a timestamp. He wants to then calculate time offsets relative to that field or use as earliest/latest constraints in the second panel.

I am having trouble envisioning how a field from Panel #1 can be referenced in Panel #2. Also wonder if I I can say something like earliest=eventtime-2d latest=eventtime+2d. I'll do a little experimentation.

[skoelpin]

If this helped you out, please feel free to upvote 🙂

I had the exact scenario about a year ago. Here's the question I asked and here's how I did it. You create a hidden panel and fields which accepts input from the timerange picker, (the fancy SPL below will also convert from relative to absolute time), it than does some math on the epoch time, i.e. subtracts 7 days from that time, then appends the new transformed time to another panels search so that panel will ONLY show the offset time

https://answers.splunk.com/answers/664666/how-to-add-seconds-to-epoch-time-using-time-modifi.html

<form refresh="500">
  <label>xxxx</label>&gt;<search id="application">
    <query>....
</query>
    <earliest>$field1.earliest$</earliest>
    <latest>$field1.latest$</latest>
  </search>
  <fieldset submitButton="false">
    <input type="time" searchWhenChanged="true" token="field1">
      <label>Time Period</label>
      <default>
        <earliest>-4h@m</earliest>
        <latest>now</latest>
      </default>
      <change>
        <eval token="start7">$field1.earliest$-604800</eval>
      </change>
    </input>

h

<row>
    <panel depends="$show_panel$">
      <title>Generate a timeframe</title>
      <table>
        <search>
          <query>| gentimes start=-1 
| addinfo 
| eval earliest=$field1.earliest$ 
| eval latest=$field1.latest$
| table earliest latest 
| format 
| eval search=trim(replace(search, "\(", ""))
| eval search=trim(replace(search, "\)", ""))</query>
          <done>
            <set token="tokField1">$result.search$</set>
          </done>
          <earliest>1528779600</earliest>
          <latest>1528866000</latest>
          <progress>
            <condition match="job.resultCount > 0">
                <set token="panel_show">true</set>
            </condition>
            <condition>
                <unset token="panel_show"/>
            </condition>
        </progress> 
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>




      <query><BASE SPL> $tokField1$

[_smp_]

After studying your example, I guess there is one big difference in our use cases. I am trying to calculate an offset based on a time field from search results in panel1 and using the offset value as time constraints in panel2. You calculated the offset from the time picker. It doesn't help that I don't know Simple XML very well - finding it difficult to understand the doc on how to pass tokens from one panel to another.

[skoelpin]

I wouldn't consider it a big difference. How are you defining time from panel 2? I'm assuming your you're explicitly putting relative time for earliest and latest? Perhaps epoch time? Regardless, you go through the same exact motions as grabbing time from the timerange picker as you would with another panels time

[_smp_]

There are 5 panels (P1-5) and a time picker. P1 uses $time.earliest$ and $time.latest$ from the time picker and returns a table of results with a field named 'event_time'. When the user clicks on the event_time field, I need to calculate earliest/latest offsets from value and use them to constrain P2-5. I am getting all mixed up in the XML doc..., , ,etc ... what elements go where, and what/where to use token values.

[skoelpin]

What your explaining is more advanced knowledge of SPL and SimpleXML. Probably not the best use case to get started with. I'd recommend doing all the exercises in the dashboard app prior to taking this project on. You will have a much better understanding

https://splunkbase.splunk.com/app/1603/