Handle Duration Results from EnumHistory in PowerShell

Lately I've been very busy with client work, and one of my projects is to capture the history for SQL Agent jobs across all their servers so they can evaluate when to schedule jobs. (Yes, I know SQL Sentry has an excellent product for this, but right now there are budget constraints.) Anyway, I can do this in PowerShell with SMO.

Once you connect to a server in SMO, the JobServer object has a collection called Jobs. For each job in that collection, you can call the EnumHistory method and it will return both job and job step results, including duration.

On the Technet page describing the datatable returned when calling EnumHistory, it indicates that the Duration property is the duration of the job or step in seconds. Unfortunately this is blatantly incorrect.

Let's say a long running job returns a duration value of 93413. Well, converting seconds to hours, minutes and seconds returns 1.01:56:53 (one day, 1 hour, 56 minutes and 53 seconds). But if you look in the View History window for the job in SQL Server Agent you'll see that the job ran in 9 hours, 34 minutes and 13 seconds. Yes, the duration of 93413 translates to 9:34:13.

I'm not sure who made the decision to record the time this way, because parsing it back out so that it's useful is problematic. I need to get this done, so I wrote this PowerShell function for just that purpose.

# Function to turn the Duration from EnumHistory() bigint into useable time value
Function Convert-Duration {
	param ([int]$sec)
	
	#Now break it down into its pieces
	if ($sec -gt 9999) {$hh = [int][Math]::Truncate($sec/10000); $sec = $sec - ($hh*10000)}
		else {$hh = 0}
	if ($sec -gt 99) {$mm = [int][Math]::Truncate($sec/100); $sec = $sec - ($mm*100)}
		else {$mm = 0}
	
	#Format and return the time value
	$dur = ("{0:D2}" -f $hh) + ':' + "{0:D2}" -f $mm + ':' + "{0:D2}" -f $sec
	$dur
	}

The function takes an integer value as a parameter, and returns a string formatted to be useful as a time string, so this code:

$sec = 93413
$dur = Convert-Duration $sec
$dur

returns a result of 09:34:13, which I then use to pass to a stored procedure parameter defined as a datatype of TIME.

I had to add the

[Math]::Truncate

I hope that helps you, because with that function my job is much easier.

Allen