Splunk Search

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

_jgpm_
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
1 Solution

DalJeanis
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

DalJeanis
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.

_jgpm_
Communicator

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

yup, cut and paste error

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

0 Karma

_jgpm_
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

DalJeanis
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

DalJeanis
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

_jgpm_
Communicator

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

0 Karma

DalJeanis
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

_jgpm_
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

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...