Splunk Search
Highlighted

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

Communicator

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

View solution in original post

Highlighted

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

Communicator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

yup, cut and paste error

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

0 Karma
Highlighted

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

Communicator

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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"
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

Communicator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

Communicator

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.

0 Karma