Splunk Search

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

justinfranks
Path Finder

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?

Tags (4)
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

kristian_kolb
Ultra Champion

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
Ultra Champion

Thanks!
/k

0 Karma

jackreeves
Explorer

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 🙂

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi /k, congrats to 20k karma 🙂

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...