earliest and latest HH:MM across multiple days

[middlemiddle]

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?

[ITWhisperer]

| eval hhmmss=_time%(60*60*24)

[middlemiddle]

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

[isoutamo]

As I didn’t get your needs, can you post your current SPL? This could help me to understand your issue.

[middlemiddle]

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")