Send a Weekly OS Deployment Summary Report with PowerShell

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()

Advertisements

3 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.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s