I want to set dynamic SLA's for File Processing. In order to do this I need to:
1. get the earliest HH:MM:SS the job has processed in the last 30 days.
2. get the latest HH:MM:SS the job has processed in the last 30 days.
3. get the average time the jobs process in the last 30 days.
4. get the difference between the earliest & latest.
Most of what I have found around stats with earliest & latest includes the date, so I end up with the time the job ran on day 1 and day 30. I need the earliest/latest by HH:MM:SS and then diff it?
| eval hhmmss=_time%(60*60*24)
Thank you @ITWhisperer . Any idea on how to convert the seconds into HH:MM:SS? I can use the seconds to get earliest/latest, however when I use the following to get HH:MM:SS they become strings and I'm struggling getting earliest/latest with a string?
| eval date4=tostring(date1, "duration")
| eval date2=date_hour.":".date_minute.":".date_second
| strcat date_hour ":" date_minute ":" date_second date3
| table client_file_name date1 date2 date3 date4
As I didn’t get your needs, can you post your current SPL? This could help me to understand your issue.
I think I have it, see below. The trick was min/max of seconds prior to converting it to time (using duration).
| eval seconds=_time%(60*60*24)
| stats min(seconds) AS "earliest_time_seconds" , max(seconds) AS "latest_time_seconds", count AS number_of_files, by client_file_name
| eval match=if(earliest_time=latest_time,"Yes", "No")
| eval diff_seconds=latest_time_seconds - earliest_time_seconds
| eval earliest_time=tostring(earliest_time_seconds, "duration")
| eval latest_time=tostring(latest_time_seconds, "duration")
| eval difference_in_time=tostring(diff_seconds, "duration")