03 Feb 2025 01:27 PM
I have a dashboard that lists task durations based on log timestamps. My DQL looks something like.
fetch logs
| parse content, "LD JSON:audit"
| fields timestamp, audit
| fieldsAdd uuid = audit[uuid], Task = audit[status_name], status = audit[status]
| fieldsRemove audit
| summarize {start_time = min (timestamp),
end_time = max(timestamp)}
, by: {uuid, Task}
| fieldsAdd runtime = end_time - start_time
| fields Task, runtime, start_time, end_time
The field runtime has the datatype “duration”, is listed in nanoseconds and is formatted as hours. So in the dashboard the durations are shows as “3,15 h” and “11,99 h”. However I want to format it as HH:mm. So “3,15 h” is “03:09” and “2,99 h” is “11:59”. How can I do this?
Solved! Go to Solution.
03 Feb 2025 07:29 PM
Unfortunately there is not with single function call, but with some arithmetic and string operations:
data record(d=duration(3.15, "h")),
record(d=duration(2.99, "h")),
record(d=duration(1.8, "m")),
record(d=duration(120, "m")),
record(d=duration(12.25, "h")),
record(d=duration(5, "d")),
record(d=duration(30, "s")),
record(d=duration(29, "s")),
record(d=duration(10, "ms"))
| fieldsAdd h=toString(toLong(d/1h)), m=toString(toLong(round(d/1m)) % 60)
| fields d, hm = concat( if(stringLength(h)<2, concat("0",h), else:h), ":", if(stringLength(m)<2, concat("0",m), else:m))
04 Feb 2025 06:45 AM
Thank you, it works!