Send a Weekly OS Deployment Summary Report with PowerShell

Posted on by Trevor Jones in ConfigMgr, OS Deployment, Powershell, Reporting, SCCM, SQL

Here’s a script I wrote that sends a simple summary of ConfigMgr OS Deployments in the last week as an HTML-formatted email.

It gives you the start and finish date/time, duration and model for each computer deployed (where the information is available in SCCM), and a list of any steps in an error state for any of the deployments.

You can use a scheduled task to send this report to yourself each week for a nice deployment overview 🙂

OSDreport

Note: to generate ad-hoc reports for specific time-periods for any ConfigMgr Task Sequence you can use my tool ConfigMgr Task Sequence Monitor

Generate-OSDReport

At the top of the script, enter the following:

  • SQLServer name (and instance name where applicable)
  • ConfigMgr Database name
  • The time in hours past that the report will display data for
  • The name/s of your OSD tasks sequences
  • Email parameters

You need db_datareader permission to the ConfigMgr database with the account running the script.


#requires -Version 2

# Database info
$dataSource = 'mysqlserver\myinstance' # SQLServer\Instance
$database = 'CM_ABC' # Database name
$TimeInHours = '168' # 168 = 7 days
$OSDTaskSequences = "
    'Windows OS Deployment x86',
    'Windows OS Deployment x64'
    " # Name/s of your OSD Task Sequences

#Email params
$EmailParams = @{
    To         = 'Joe.Bloggs@contoso.com'
    From       = 'PowerShell@contoso.com'
    Smtpserver = 'myexchangeserver'
    Subject    = "Operating System Deployment Weekly Report $(Get-Date -Format dd-MMM-yyyy)"
}

$results = @()

$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$Query = "
    select distinct tes.ResourceID
    from vSMS_TaskSequenceExecutionStatus tes
    inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
    where tsp.Name in ($OSDTaskSequences)
    and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
"

$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)

foreach ($ResourceID in $table.Rows.ResourceID)
{
    $Query = "
        Select (select top(1) convert(datetime,ExecutionTime,121)
        from vSMS_TaskSequenceExecutionStatus tes
        inner join v_R_System sys on tes.ResourceID = sys.ResourceID
        inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
        where tsp.Name in ($OSDTaskSequences)
        and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
        and LastStatusMsgName = 'The task sequence execution engine started execution of a task sequence'
        and Step = 0
        and tes.ResourceID = $ResourceID
        order by ExecutionTime desc) as 'Start',
        (select top(1) convert(datetime,ExecutionTime,121)
        from vSMS_TaskSequenceExecutionStatus tes
        inner join v_R_System sys on tes.ResourceID = sys.ResourceID
        inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
        where tsp.Name in ($OSDTaskSequences)
        and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
        and LastStatusMsgName = 'The task sequence execution engine successfully completed a task sequence'
        and tes.ResourceID = $ResourceID
        order by ExecutionTime desc) as 'Finish',
        (Select name0 from v_R_System sys where sys.ResourceID = $ResourceID) as 'ComputerName',
        (select Model0 from v_GS_Computer_System comp where comp.ResourceID = $ResourceID) as 'Model'
    "
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $reader = $command.ExecuteReader()
    $table = New-Object -TypeName 'System.Data.DataTable'
    $table.Load($reader)

    if ($table.rows[0].Start.GetType().Name -eq 'DBNull')
    {
        $Start = ''
    }
    Else
    {
        $Start = $table.rows[0].Start
    }

    if ($table.rows[0].Finish.GetType().Name -eq 'DBNull')
    {
        $Finish = ''
    }
    Else
    {
        $Finish = $table.rows[0].Finish
    }

    #$table
    if ($Start -eq '' -or $Finish -eq '')
    {
        $diff = $null
    }
    else
    {
        $diff = $Finish-$Start
    }

    $PC = New-Object -TypeName psobject
    Add-Member -InputObject $PC -MemberType NoteProperty -Name ComputerName -Value $table.rows[0].ComputerName
    Add-Member -InputObject $PC -MemberType NoteProperty -Name StartTime -Value $table.rows[0].Start
    Add-Member -InputObject $PC -MemberType NoteProperty -Name FinishTime -Value $table.rows[0].Finish
    if ($Start -eq '' -or $Finish -eq '')
    {
        Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value ''
    }
    else
    {
        Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value $("$($diff.hours)" + ' hours ' + "$($diff.minutes)" + ' minutes')
    }
    Add-Member -InputObject $PC -MemberType NoteProperty -Name Model -Value $table.rows[0].Model
    $results += $PC
}

$results = $results | Sort-Object -Property ComputerName

$Query = "
    select sys.Name0 as 'ComputerName',
    tsp.Name 'Task Sequence',
    comp.Model0 as Model,
    tes.ExecutionTime,
    tes.Step,
    tes.GroupName,
    tes.ActionName,
    tes.LastStatusMsgName,
    tes.ExitCode,
    tes.ActionOutput
    from vSMS_TaskSequenceExecutionStatus tes
    left join v_R_System sys on tes.ResourceID = sys.ResourceID
    left join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
    left join v_GS_COMPUTER_SYSTEM comp on tes.ResourceID = comp.ResourceID
    where tsp.Name in ($OSDTaskSequences)
    and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
    and tes.ExitCode not in (0,-2147467259)
    Order by tes.ExecutionTime desc
"

$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)

# Send html email
$style = "
<style>
body {
    color:#333333;
    font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}
    font-size: 10pt;
}
h1 {
    text-align:center;
}
h2 {
    border-top:1px solid #666666;
}
table {
    border-collapse: collapse;
    font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;
}
th {
    font-size: 1.2em;
    text-align: left;
    padding-top: 5px;
    padding-bottom: 4px;
    background-color: #1FE093;
    color: #ffffff;
}
th, td {
    font-size: 1em;
    border: 1px solid #1FE093;
    padding: 3px 7px 2px 7px;
}
<style>
"

$body1 = $results |
Select-Object -Property ComputerName, StartTime, FinishTime , DeploymentTime, Model |
ConvertTo-Html -Head $style -Body "
<H2>OS Deployments This Week ($($results.Count))</H2>

" |
Out-String

$body2 = $table |
Select-Object -Property ComputerName, 'Task Sequence', Model, ExecutionTime, Step, GroupName, ActionName, LastStatusMsgName, ExitCode, ActionOutput |
ConvertTo-Html -Head $style -Body "
<H2>OS Deployment Errors This Week ($($table.Rows.Count))</H2>

" |
Out-String

$Body = $body1 + $body2

Send-MailMessage @EmailParams -Body $Body -BodyAsHtml

# Close the connection
$connection.Close()

Published by Trevor Jones

7 thoughts on “Send a Weekly OS Deployment Summary Report with PowerShell”

    1. Hi Sandy, the model comes from the hardware inventory data which is often not present in the database yet for newly deployed machines, so that’s why some are missing. If you run the same report in another week for example, the model should be populated if the client has been online and run its HI cycle.

      Reply

  3. This is awesome. I would like to see a column with the task sequence name and have it sorted by StartTime instead of ComputerName. I can tinker with the sort but would appreciate if you could throw the Task Sequence Name column on there if possible.

    Reply

    1. Hey Rick,
      I think the following code should do it:

      #requires -Version 2

      # Database info
      $dataSource = ‘mysqlserver\myinstance’ # SQLServer\Instance
      $database = ‘CM_ABC’ # Database name
      $TimeInHours = ‘168’ # 168 = 7 days
      $OSDTaskSequences = ”
      ‘Windows OS Deployment x86’,
      ‘Windows OS Deployment x64’
      ” # Name/s of your OSD Task Sequences

      #Email params
      $EmailParams = @{
      To = ‘Joe.Bloggs@contoso.com’
      From = ‘PowerShell@contoso.com’
      Smtpserver = ‘myexchangeserver’
      Subject = “Operating System Deployment Weekly Report $(Get-Date -Format dd-MMM-yyyy)”
      }

      $results = @()

      $connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
      $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
      $connection.ConnectionString = $connectionString
      $connection.Open()

      $Query = ”
      select distinct tes.ResourceID, tsp.Name as ‘Task Sequence’
      from vSMS_TaskSequenceExecutionStatus tes
      inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
      where tsp.Name in ($OSDTaskSequences)
      and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
      "

      $command = $connection.CreateCommand()
      $command.CommandText = $Query
      $reader = $command.ExecuteReader()
      $table1 = New-Object -TypeName 'System.Data.DataTable'
      $table1.Load($reader)

      foreach ($item in $table1.Rows)
      {
      $Query = "
      Select (select top(1) convert(datetime,ExecutionTime,121)
      from vSMS_TaskSequenceExecutionStatus tes
      inner join v_R_System sys on tes.ResourceID = sys.ResourceID
      inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
      where tsp.Name in ($OSDTaskSequences)
      and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
      and LastStatusMsgName = 'The task sequence execution engine started execution of a task sequence'
      and Step = 0
      and tes.ResourceID = $($item.ResourceID)
      order by ExecutionTime desc) as 'Start',
      (select top(1) convert(datetime,ExecutionTime,121)
      from vSMS_TaskSequenceExecutionStatus tes
      inner join v_R_System sys on tes.ResourceID = sys.ResourceID
      inner join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
      where tsp.Name in ($OSDTaskSequences)
      and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
      and LastStatusMsgName = 'The task sequence execution engine successfully completed a task sequence'
      and tes.ResourceID = $($item.ResourceID)
      order by ExecutionTime desc) as 'Finish',
      (Select name0 from v_R_System sys where sys.ResourceID = $($item.ResourceID)) as 'ComputerName',
      (select Model0 from v_GS_Computer_System comp where comp.ResourceID = $($item.ResourceID)) as 'Model'
      "
      $command = $connection.CreateCommand()
      $command.CommandText = $Query
      $reader = $command.ExecuteReader()
      $table = New-Object -TypeName 'System.Data.DataTable'
      $table.Load($reader)

      if ($table.rows[0].Start.GetType().Name -eq 'DBNull')
      {
      $Start = ''
      }
      Else
      {
      $Start = $table.rows[0].Start
      }

      if ($table.rows[0].Finish.GetType().Name -eq 'DBNull')
      {
      $Finish = ''
      }
      Else
      {
      $Finish = $table.rows[0].Finish
      }

      #$table
      if ($Start -eq '' -or $Finish -eq '')
      {
      $diff = $null
      }
      else
      {
      $diff = $Finish-$Start
      }

      $PC = New-Object -TypeName psobject
      Add-Member -InputObject $PC -MemberType NoteProperty -Name ComputerName -Value $table.rows[0].ComputerName
      Add-Member -InputObject $PC -MemberType NoteProperty -Name TaskSequence -Value $item.'Task Sequence'
      Add-Member -InputObject $PC -MemberType NoteProperty -Name StartTime -Value $table.rows[0].Start
      Add-Member -InputObject $PC -MemberType NoteProperty -Name FinishTime -Value $table.rows[0].Finish
      if ($Start -eq '' -or $Finish -eq '')
      {
      Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value ''
      }
      else
      {
      Add-Member -InputObject $PC -MemberType NoteProperty -Name DeploymentTime -Value $("$($diff.hours)" + ' hours ' + "$($diff.minutes)" + ' minutes')
      }
      Add-Member -InputObject $PC -MemberType NoteProperty -Name Model -Value $table.rows[0].Model
      $results += $PC
      }

      $results = $results | Sort-Object -Property StartTime

      $Query = "
      select sys.Name0 as 'ComputerName',
      tsp.Name 'Task Sequence',
      comp.Model0 as Model,
      tes.ExecutionTime,
      tes.Step,
      tes.GroupName,
      tes.ActionName,
      tes.LastStatusMsgName,
      tes.ExitCode,
      tes.ActionOutput
      from vSMS_TaskSequenceExecutionStatus tes
      left join v_R_System sys on tes.ResourceID = sys.ResourceID
      left join v_TaskSequencePackage tsp on tes.PackageID = tsp.PackageID
      left join v_GS_COMPUTER_SYSTEM comp on tes.ResourceID = comp.ResourceID
      where tsp.Name in ($OSDTaskSequences)
      and DATEDIFF(hour,ExecutionTime,GETDATE()) < $TimeInHours
      and tes.ExitCode not in (0,-2147467259)
      Order by tes.ExecutionTime desc
      "

      $command = $connection.CreateCommand()
      $command.CommandText = $Query
      $reader = $command.ExecuteReader()
      $table = New-Object -TypeName 'System.Data.DataTable'
      $table.Load($reader)

      # Send html email
      $style = "

      body {
      color:#333333;
      font-family: “”Trebuchet MS””, Arial, Helvetica, sans-serif;}
      font-size: 10pt;
      }
      h1 {
      text-align:center;
      }
      h2 {
      border-top:1px solid #666666;
      }
      table {
      border-collapse: collapse;
      font-family: “”Trebuchet MS””, Arial, Helvetica, sans-serif;
      }
      th {
      font-size: 1.2em;
      text-align: left;
      padding-top: 5px;
      padding-bottom: 4px;
      background-color: #1FE093;
      color: #ffffff;
      }
      th, td {
      font-size: 1em;
      border: 1px solid #1FE093;
      padding: 3px 7px 2px 7px;
      }

      ”

      $body1 = $results |
      Select-Object -Property ComputerName, TaskSequence, StartTime, FinishTime , DeploymentTime, Model |
      ConvertTo-Html -Head $style -Body ”

      OS Deployments This Week ($($results.Count))

      ” |
      Out-String

      $body2 = $table |
      Select-Object -Property ComputerName, ‘Task Sequence’, Model, ExecutionTime, Step, GroupName, ActionName, LastStatusMsgName, ExitCode, ActionOutput |
      ConvertTo-Html -Head $style -Body ”

      OS Deployment Errors This Week ($($table.Rows.Count))

      ” |
      Out-String

      $Body = $body1 + $body2

      Send-MailMessage @EmailParams -Body $Body -BodyAsHtml

      # Close the connection
      $connection.Close()

      Reply

  4. Hi. Great looking script! Have you tested this with office 365? I tried running it and get an error “Send-MailMessage: The operation has timed out. CategoryInfo: Invalid operation: (System.Net.Mail.SmtpClient:SmtpClient) [Send-MailMessage], SmtpException. Fully qualified retirement: SmtpException, Microsoft.Powershell.Commands.SendMailMessage

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.