enter arbitrary date on a form, convert to unix time, and search based on date range

[jeff]

I'm probably making this too complicated, but I sense this should be easier than I'm making it out to be and I can't find the answer. I'm trying to build a form based search to find hosts behind a global NAT in our organization, based on a notice we receive from our service provider. They'll send a notice with log data similar to the below (IPs changed to protect the innocent):

08/04/11-10:18:52.896057 IP 10.10.10.10.51506 > 192.168.10.10.80: S 2672294734:2672294734(0) win 64512

Ideally, I'd like to just paste the entire line into the form, parse out the important bits, and execute a series of searches to find which internal IP address was behind the NAT (based on port), find out which device was actually assigned to that IP at the time (based on DHCP logs), and find asset data for the device (based on MAC)...

The real issue I'm having right now is parsing the timestamp from an arbitrary text input without going through the timerangepicker. The actual event is at a very specific time range (within a couple of seconds of our internal logs)... but we use 7 day DHCP leases, so I want to get the most recent assignment within the past 7 days from the event. I can build and successfully set "earliest" and "latest" by executing this hacked up query:

* | head 1 | eval tm="08/04/2011:10:18:52" | convert timeformat="%m/%d/%Y:%H:%M:%S" mktime(tm) as t | eval earliest=relative_time(t,"-7d")| eval latest=relative_time(t,"+2s") | fields earliest, latest 

It returns the most recently logged event with the fields "earliest" and "latest" defined... It's ugly but works. Is there a better way? I didn't see a function to use with eval to convert a human readable date/time to a UNIX time, so I had to use convert... so I had to return a row...

If I attempt to put this into a subsearch, however, Splunk returns an "Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side"

DHCPACK 172.16.10.10 [search * | head 1 | eval tm="08/04/2011:10:18:52" | convert timeformat="%m/%d/%Y:%H:%M:%S" mktime(tm) as t | eval earliest=relative_time(t,"-7d")| eval latest=relative_time(t,"+2s") | fields earliest, latest] | head 1

(172.16.10.10 being the internal IP address behind the 10.10.10.10 global address using that port and accessing the external 192.168.10.10 address...)

[gkanapathy · Accepted solution]

There's a simple answer to you question, but there's probably a better way to tackle this. I recommend you convert using a macro rather than the convert search command. Define the macro as something like:

[mytimeconversionmacro(1,2)]
args = tmstring,offset
iseval = true
definition = relative_time(strptime("$tmstring$","%m/%d/%Y:%H:%M:%S"),"$offset$")

and then in your search string you can use:

DHCPACK 172.16.10.10 earliest=`mytimeconversionmacro("$tm$","-7d)` latest=`mytimeconversionmacro("$tm$","+2s")`

$tm$ would come out of your search form.

---

The simple answer is that you need a new "format" command in your subsearch:

DHCPACK 172.16.10.10 
[ stats count | eval tm="08/04/2011:10:18:52" 
              | convert timeformat="%m/%d/%Y:%H:%M:%S" mktime(tm) as t 
              | eval earliest=relative_time(t,"-7d")
              | eval latest=relative_time(t,"+2s") 
              | fields earliest, latest
              | format "(" "(" "" ")" "OR" ")"] 
| head 1

This is because you can't use AND against earliest and latest specifiers, which I consider to be a bug.

[jeff]

Thanks... I knew there had to be something obscure getting in my way- I validated the "simple" answer works. I'll likely try the macro approach- it seems more straight forward. (Would be nice if I could just do this in an eval... dontcha think?).