How to use a date field that isn't _time to search HelpDesk tickets closed in the last 30 days?

[justinfranks]

Hello,

I am indexing HelpDesk tickets and I am trying to derive some stats about these tickets.

During the indexing of these events (indexed from a MySQL DB using DB Connect), I have set my timestamp field to be the date the ticket was opened. This generate events like this:

2014-09-07T23:05:44.000 tech_name="Justin Franks" client_name="Wilfred Server" job_ticket_id=10840 problem_type3=Backup problem_type2="Backup and Restore" problem_type1="INFORMATION TECHNOLOGY" department_name= first_response_date= close_date= status_type_name=Open last_updated=1410095145.000 group_name="IT | Backups"

Is there a way to search on which tickets have been closed in say... the last 30 days? How would I change _time to a different field and re-search on it?

[kristian_kolb · Accepted solution]

Yes you can do that. The format of the close_date field is not given in your example, so let's assume it's epoch.

your search 
| eval recent_close = if((now() - close_date) < (30*86400),"1","0") 
| where recent_close = 1 
| blah blah

If you need to convert close_date to epoch (with the eval strptime() function), do so before making the comparison to now()

Just make sure that you search for a wide enough time-range, and not just the last 30 days.

/k

[kristian_kolb]

Thanks!
/k

[jackreeves]

I am trying do something similar but struggling with adding the strptime () function.

My close_date field is in following format "2017/11/30", could you please advise 🙂

[MuS]

Hi /k, congrats to 20k karma 🙂