Monitoring vCD 5.1 vAPP deployment times with SQL

Whipped up a quick set of SQL scripts that will allow me to monitor vAPP deployment times in vCloud Director 5.1. Maybe somebody out there will also find them useful.

--Finds the deployment and vAPP creation times over a set period of time which is currently 6 hours.
select distinct
     Jobs.operation
    ,Jobs.object
    ,Jobs.Task_Length_Minutes
    ,COUNT(Jobs.job_id) as VM_Count
    ,Jobs.Minutes_Since_Task
    ,Jobs.OrgVDC
from (
      select top 2500
         jobs.job_id
        ,jobs.starttime
        ,jobs.stoptime
        ,jobs.object
        ,jobs.operation
        ,DATEDIFF(MINUTE,(jobs.starttime),(jobs.stoptime)) AS Task_Length_Minutes
        ,DATEDIFF(MINUTE,(jobs.starttime),getdate()) as Minutes_Since_Task
        ,org_prov_vdc.name AS OrgVDC
     from jobs
         Join vapp_vm on vapp_vm.vapp_id = jobs.object_id
            JOIN vm_container on vm_container.sg_id = jobs.object_id
            JOIN org_prov_vdc on org_prov_vdc.id = vm_container.org_vdc_id
        WHERE jobs.operation IN (
                  'VAPP_DEPLOY'
                  ,'VDC_INSTANTIATE_VAPP')
            AND DATEPART(YEAR, stoptime) <> 9999
            AND DATEDIFF(MINUTE,(jobs.starttime),getdate()) <=360
               Group by jobs.object, jobs.starttime, jobs.stoptime, jobs.operation, vapp_vm.name, jobs.job_id, org_prov_vdc.name
      ) Jobs
Group BY  Jobs.operation
    ,Jobs.object
    ,Jobs.Task_Length_Minutes
    ,Jobs.Minutes_Since_Task
    ,Jobs.OrgVDC
    Order by Minutes_Since_Task      


--Finds the Average Deployment Time by vAPP Name over a set period of time which is currently 6 hours.
SELECT Jobs.object
        ,AVG(Jobs.TaskLengthSeconds) as AverageDeployTime
        ,Jobs.ElapsedTimeMinutes
FROM (SELECT top 500 jobs.object
,jobs.starttime
        ,jobs.stoptime
        ,DATEDIFF(MINUTE,(jobs.starttime),(jobs.stoptime)) AS TaskLengthSeconds
        ,DATEDIFF(MINUTE,(jobs.starttime),getdate()) as ElapsedTimeMinutes
     FROM jobs
        WHERE (jobs.operation = 'VAPP_DEPLOY' or jobs.operation = 'VDC_INSTANTIATE_VAPP') and DATEPART(YEAR, stoptime) = 2013
            Group by jobs.object, jobs.starttime, jobs.stoptime
                Order by jobs.starttime DESC) Jobs
WHERE CAST(ElapsedTimeMinutes AS int) <=360
    Group by Jobs.object, Jobs.ElapsedTimeMinutes
        Order by Jobs.ElapsedTimeMinutes

       
--Finds the Longest Deployment Time by vAPP Name over a set period of time which is currently 6 hours.
SELECT Jobs.object
        ,MAX(Jobs.TaskLengthMinutes) as MaxDeployTime
FROM (select top 2500 jobs.object
        ,jobs.starttime
        ,jobs.stoptime
        ,DATEDIFF(MINUTE,(jobs.starttime),(jobs.stoptime)) AS TaskLengthMinutes
        ,DATEDIFF(MINUTE,(jobs.starttime),getdate()) as ElapsedTimeMinutes
     FROM jobs
        WHERE jobs.operation = 'VAPP_DEPLOY' or jobs.operation = 'VDC_INSTANTIATE_VAPP' and stoptime not like '%9999%'
            Group by jobs.object, jobs.starttime, jobs.stoptime
                Order by jobs.starttime DESC) Jobs
WHERE CAST(ElapsedTimeMinutes AS int) <=360
    Group by Jobs.object           
        Order by MaxDeployTime