Get-TSGroupDurations.ps1

#region Functions
function Invoke-SQL {
    param (
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query")
    )

    $connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database";
    $connection = new-object System.Data.SqlClient.SqlConnection($connectionString);
    $command = New-Object System.Data.SqlClient.SqlCommand($sqlCommand, $connection);
    $connection.Open();

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command;
    $dataset = New-Object System.Data.DataSet;
    $adapter.Fill($dataSet) | Out-Null
    #Populate Hash Table

    $connection.Close();
    return $dataSet;
}

function DataTableToHashTable {
    param (
        [System.Data.DataTable] $table = $null
    )

    $hashtable = @()
	foreach ($row in $table.Rows)
	{
		## Create psobject from each row
		$obj = new-object PSObject
		foreach ($col in $table.Columns)
		{
			## Add a field to the object using the columnname as the name
			$obj | Add-Member -NotePropertyName $col.ColumnName -NotePropertyValue $row.ItemArray[$col.Ordinal]
		}       
		$hashtable += $obj        
	}
    return $hashtable
}
#endregion

#region Globals
$sccmsqlserver = "#SCCM_SQL_SERVER#"
$sccmsqldb = "#SCCM_DB_NAME#"

$hostname = "#HOSTNAME#"

$query = @"
SELECT
	a.PackageName,
	s.Netbios_Name0,
	ts.ExecutionTime,
	ts.step,
	ts.GroupName,
	ts.LastStatusMessageID
FROM
	v_TaskExecutionStatus AS ts
	inner join
	v_R_System AS s
		ON ts.ResourceID = s.ResourceID
	inner join
	v_AdvertisementInfo as a
		on ts.AdvertisementID = a.AdvertisementID
WHERE
	s.Netbios_Name0 = '##REPLACE-HOSTNAME##'
	AND
	(LastStatusMessageID IN (11124,11127,11143)
	OR 
	(LastStatusMessageID = 11140 AND step = 0))

ORDER BY
	ExecutionTime ASC
"@
#endregion

#region Main
$query = $query.Replace("##REPLACE-HOSTNAME##",$hostname)

$table = (invoke-sql -dataSource $sccmsqlserver -database $sccmsqldb -sqlCommand $query).Tables[0]

$hash = DataTableToHashTable -table $table

$groupoutput = @()
foreach ($line in $hash)
{
    $thisgroupname = $line.GroupName
    if($line.LastStatusMessageID -eq 11124)
    {
        ## Start of Group
        $obj = [pscustomobject]@{
            GroupName = $line.GroupName
            ParentGroup = $null
            StartTime = $line.ExecutionTime
            EndTime = $null
            Duration = $null
            DurationSeconds = $null
        }
        $groupoutput += $obj
    } elseif ($line.LastStatusMessageID -eq 11127)
    {
        ## End of Group
        $findgroup = $groupoutput | ?{$_.GroupName -eq $line.GroupName}
        $findgroup.EndTime = $line.ExecutionTime
    }
}

## Calculate Duration of Each Group
$groupoutput | % {$_.Duration = $_.EndTime - $_.StartTime}

## Calculate Duration in Seconds
$groupoutput | % {$_.DurationSeconds = $_.Duration.TotalSeconds}
#endregion