Determine whether a time-based field falls within the last 7 days

[wmwilson01]

I'm having a tough time searching for this, sorry if it's been asked many times. I have an event that carries a few time-based fields. I'm trying to search to determine if any of those times fall within the last 7 days. Here's an example event:

Tue, 16 Nov 2010 13:21:33 -0500 client_id=8035016 shost=WWILSON2 
src_ip="192.168.1.120,192.168.56.1" dns_name=wwilson2 os="Win7 6.1.7600" 
status="Fixed" issuer="bfadmin" issue_time="Tue, 14 Sep 2010 15:10:15 -0500"
start_time="Sat, 01 Jan 2011 16:06:09" end_time= fixlet_id=6071005 
fixlet_name="Mozilla Firefox 3.5.12 Available (Superseded)" 
fixlet_site="Updates for Windows Applications" action_id=177 
action_name="Mozilla Firefox 3.5.12 Available" reapply=True 
restart_required=True stopper="bfadmin" 
time_stopped="Tue, 14 Sep 2010 15:32:34 -0500" bigfix_server=BESCORE
soap_url=http://bescore:80/?wsdl soap_user=bfadmin

And here's the search I'm using:

sourcetype=actions (end_time=* OR time_stopped=*) 
| dedup action_id, host, bigfix_server 
| convert timeformat="%a, %d %b %Y %H:%M:%S" mktime(start_time) as start mktime(end_time) as end mktime(time_stopped) as stop 
| eval ended=if(end > relative_time(now(), "-700d"), "Completed", if(stop > relative_time(now(), "-700d"), "Stopped", "None"))

In this case, I've modified the search to look back 700 days in order to catch the event listed above. The field "ended" ends up always being populated with "None"

What am I doing wrong here?

[sideview · Accepted solution]

Are you sure that your convert clause is working correctly?

You can test it out like so:

sourcetype=actions (end_time=* OR time_stopped=*) 
| dedup action_id, host, bigfix_server 
| convert timeformat="%a, %d %b %Y %H:%M:%S" mktime(start_time) as start mktime(end_time) as end mktime(time_stopped) as stop 
| stats count by start end stop

just to see the kinds of values you're getting for start, end and stop.

[wmwilson01]

Sometimes the things I miss.... You're correct, I was re-using the search and missed that the time format changed from one to the other. The stop time includes tz info. Doh. Thanks!