Looking at timestamps over 1 year old

[rescobar713]

I'm trying to only look at events that were modified over 1 year ago. I have a timestamp field called last_modified that I believe is in the form of a string (e.g. "1/1/2014"). How would I go about finding the difference between the current date and the last_modified date, and then filtering out all events for which that difference is less than 1 year? I've tried using the strptime function to convert the last_modifiedstring to epoch time and then doing arithmetic with that and now(), but strptimedidn't seem to work with last_modified.

last_modifiedis a value I retrieved from a lookup table that stores timestamps in epoch time, so it seems that it was converted to a human-readable string when the lookup was done. But I'm not sure why strptime isn't working.

Any advice?

[richgalloway]

Perhaps you just need to tweak your strptime format string. A sample of your year-old events will help.
Does last_modified match _time? If so, you can use latest=-12m@m.

[woodcock · Accepted solution]

You are on the right track but probably are using a bad strptime string. It should be like this:

... | eval last_modified_epoch=strptime(last_modified, "%m/%d/%Y") | eval modAgeSeconds = now() - last_modified_epoch | where modAgeSeconds > 31536000

Make sure that your timepicker is REALLY long (like maybe "All time").

[rescobar713]

This worked perfectly! Thank you.