can i pass a subsearch argument as a greater than relationship?

[_jgpm_]

I've looked into format and it doesn't look like I can replace the "=".

I want to change

( ( DateStart="12/14/2016" AND TimeEnd="1:38:27 PM" AND TimeStart="1:38:27 PM" ) )

to

( ( DateStart="12/14/2016" AND TimeEnd<"1:38:27 PM" AND TimeStart>"1:38:20 PM" ) )

Thanks in advance for any suggestions on how to fix this.

I'm on 6.4.3.

[DalJeanis · Accepted solution]

Updated answer - found a way, more or less -

| makeresults 
| eval DateStart="12/14/2016" | eval TimeEnd="1:38:27 PM" |eval TimeStart="1:38:27 PM" 
| table DateStart TimeStart TimeEnd 
| format
| append 
    [| makeresults | eval DateStart="12/14/2016" | eval TimeEnd="1:38:27 PM" |eval TimeStart="1:38:27 PM" 
    | table DateStart TimeStart TimeEnd 
    | eval myfield=mvappend("DateStart=".DateStart,"TimeStart>".TimeStart, "TimeEnd<".TimeEnd) 
    | table myfield
    | mvexpand myfield 
    | rename myfield as search 
    | format ( ( " " ) AND ) 
    | rex field=search mode=sed "s/\( \"/( /g"  
    | rex field=search mode=sed "s/([=<>])/\1\"/g"
    ]

which gives this results for comparison

( ( DateStart="12/14/2016" AND TimeEnd="1:38:27 PM" AND TimeStart="1:38:27 PM" ) )
( ( DateStart="12/14/2016" ) AND ( TimeStart>"1:38:27 PM" ) AND ( TimeEnd<"1:38:27 PM" ) )

So those two results LOOK equivalent.

However, I'm leery of the date format, since you'd be comparing to a literal, and I don't think that's what you really want. Assuming your returned search values were in epoch time format, it turns out the code works the same --

| makeresults 
 | eval DateStart=strptime("12/14/2016","%m/%d/%Y")
 | eval TimeEnd=strptime("12/14/2016 1:38:27 PM","%m/%d/%Y %l:%M:%S %p")
 | eval TimeStart=strptime("12/14/2016 1:38:27 PM","%m/%d/%Y %l:%M:%S %p")  
 | table DateStart TimeStart TimeEnd 
 | format
 | append 
     [| makeresults 
     | eval DateStart=strptime("12/14/2016","%m/%d/%Y")
     | eval TimeEnd=(strptime("1:38:27 PM","%l:%M:%S %p")%86400 +DateStart)
     | eval TimeStart=(strptime("1:38:27 PM","%l:%M:%S %p")%86400 +DateStart)  
     | table DateStart TimeStart TimeEnd  
     | eval myfield=mvappend("DateStart=".DateStart,"TimeStart>".TimeStart, "TimeEnd<".TimeEnd) 
     | table myfield 
     | mvexpand myfield 
     | rename myfield as search 
     | format ( ( " " ) AND ) 
     | rex field=search mode=sed "s/\( \"/( /g"  
     | rex field=search mode=sed "s/([=<>]+)/\1\"/g"
     ]

produces this

( ( DateStart="1481673600.000000" AND TimeEnd="1481722707.000000" 
    AND TimeStart="1481722707.000000" ) )
( ( DateStart="1481673600.000000" ) AND ( TimeStart>"1481722707.000000" ) 
    AND ( TimeEnd<"1481722707.000000" ) )

---

Made a slight update to the sed to make sure it would work for >= and <=
Updated to make sure the start and end time are relative to the date.

[_jgpm_]

am I missing your close bracket? I didn't run it, but I don't see it.

[DalJeanis]

yup, cut and paste error

Actually, managed to solve it with a rex, so I'll update the code

[_jgpm_]

I haven't run your code because I'm trying to practice just reading it. I haven't used makeresults yet. I usually just "|stats count as $rename$" and then eval what I want to rename. So you use table instead of "fields - "?

I like the way you did it with format and rex/sed. I do think there is some optimization here but it's a don't-care since the operations take so little time.

If this works, you are saving me from a

index=* | DateStart="12/14/2016" AND TimeStart>"1:38:27 PM" AND  TimeEnd<"1:38:27 PM"

to a

index=* DateStart="12/14/2016" AND TimeStart>"1:38:27 PM" AND  TimeEnd<"1:38:27 PM"

which could be huge.

Internet upvote for you sir. I will test this over the weekend.

[DalJeanis]

These parts of the code just generate the test data

 | makeresults 
 | eval DateStart="12/14/2016" | eval TimeEnd="1:38:27 PM" |eval TimeStart="1:38:27 PM" 
 | table DateStart TimeStart TimeEnd 

or

 | makeresults 
 | eval DateStart=strptime("12/14/2016","%m/%d/%Y")
 | eval TimeEnd=strptime("1:38:27 PM","%l:%M:%S %p")
 | eval TimeStart=strptime("1:38:27 PM","%l:%M:%S %p")  
 | table DateStart TimeStart TimeEnd  

This section creates a multivalue field with literal versions of your three tests, gets rid of every other field, and then splits that one field into three different transactions. By the way, I verified that you CAN update the comparison operators to <= or >= and the code will still work.

 | eval myfield=mvappend("DateStart=".DateStart,"TimeStart>".TimeStart, "TimeEnd<".TimeEnd) 
 | table myfield 
 | mvexpand myfield 

Tthis section renames myfield to "search" and then formats it to ALMOST what you want.

 | rename myfield as search 
 | format ( ( " " ) AND ) 

These two seds get rid of a leading quote inside of each inside parenthesis, and add a quote after each comparison operator.

 | rex field=search mode=sed "s/\( \"/( /g"  
 | rex field=search mode=sed "s/([=<>]+)/\1\"/g"

[DalJeanis]

By the way, as long as these all dates and times are coming in in epoch time format, the two seds can be replaced with a single one that will just kill all quotes in the returned search string

| rex field=search mode=sed "s/\"//g" 

returning this

( ( DateStart=1481673600.000000 ) AND ( TimeStart>=1487338707.000000 ) AND ( TimeEnd<1487338707.000000 ) )

Crud, looking at that, it reminds me that we need to validate that the time epoch values are related to the desired date. Oh, well, that's for another day.

[_jgpm_]

On a small test dataset (~1M events) I went from 115 seconds to 82 seconds. That's not bad. Thanks @DalJeanis!

[DalJeanis]

Nice. How did you handle the issue with the epoch time? Or was the issue not relevant to your method, since I was just generating sample results?

I'd hate to cut the search time by losing some proper results. 😉

I edited my code and the results just so as not to inadvertently lead anyone else astray.

[_jgpm_]

I actually didn't use epoch time and after careful review of the results, I didn't lose any events. I don't use the _time field so that may have something to do with it.

[DalJeanis]

Hmmm. Well, I'll assume you made any adjustments necessary so that the comparisons are valid to your data. The reader is HEREBY warned to make similar adjustments to the code and test thoroughly to verify they are getting the right results.