Tuesday, March 8, 2016

Send a Weekly OS Deployment Summary Report with PowerShell

 


Here’s a script 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
#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 = "
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)