Tuesday, March 8, 2016

Client Health: Find all CCMEval Failed or Unknown

Client Health: Find all CCMEval Failed or Unknown

Leave a reply
Keeping your ConfigMgr clients healthy is an important task for an SCCM administrator. Here’s a SQL query that will find all devices in ConfigMgr that have been active in the last 7 days, but have either failed their CCMEval or have “unknown” status, ie no CCMEval results.
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
Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
 when LastHealthEvaluationResult = 2 then 'Not Applicable'
 when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
 when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
 when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
 when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
 when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
 end as 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName
I like to add SQL queries to PowerShell functions so I can return quick results to my current session:
capture
You can run this query from PowerShell like this (enter your database info at the top of 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
function Get-CCMEvalFailedOrUnknown
{
# Database info
$dataSource = 'mysqlserver\INST_SCCM'
$database = 'CM_ABC'
 
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
 
$Query = "
Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
 when LastHealthEvaluationResult = 2 then 'Not Applicable'
 when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
 when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
 when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
 when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
 when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
 end as 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName
 
"
 
$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)
 
# Close the connection
$connection.Close()
 
return $table
}
 
Get-CCMEvalFailedOrUnknown | Out-GridView
capture
You could also send this as a weekly HTML report by email using the Windows Task Scheduler. Simply use the following code, entering your database and email parameters at the top:
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
# Database info
$dataSource = 'mysqlserver\INST_SCCM'
$database = 'CM_ABC'
 
#Email params
$EmailParams = @{
    To         = 'Joe.Bloggs@contoso.com'
    From       = 'PowerShell@contoso.com'
    Smtpserver = 'mysmtpserver'
    Subject    = "Client Health: CCMEval Failed or Unknown Weekly Report $(Get-Date -Format dd-MMM-yyyy)"
}
 
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
 
$Query = "
Select sys.Name0 as 'ComputerName',sys.User_Name0 as 'UserName',
cs.ClientStateDescription,
DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) as 'Days Active',
DATEDIFF(day,cs.LastHealthEvaluation,GetDate()) as 'Days Since Last Eval',
sys.Creation_Date0 as 'SCCM Client Registration Date',
cs.LastActiveTime, cs.LastHealthEvaluation,
case when LastEvaluationHealthy = 1 then 'Pass'
 when LastEvaluationHealthy = 2 then 'Fail'
 when LastEvaluationHealthy = 3 then 'Unknown'
 end as 'Last Evaluation Healthy',
case when cs.ClientRemediationSuccess = 1 then 'Pass'
 when cs.ClientRemediationSuccess = 2 then 'Fail'
 else ''
 end as 'ClientRemediationSuccess',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
 when LastHealthEvaluationResult = 2 then 'Not Applicable'
 when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
 when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
 when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
 when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
 when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
 end as 'LastHealthEvaluationResult',
HealthCheckDescription,ResultDetail,ResultCode
from dbo.v_CH_ClientSummary cs
inner join v_R_System sys on cs.ResourceID = sys.ResourceID
left join v_CH_EvalResults eval on cs.ResourceID = eval.ResourceID
where cs.ClientStateDescription in ('Active/Fail','Active/Unknown')
and DATEDIFF(day,sys.Creation_Date0,cs.LastActiveTime) > 7
Order by ClientStateDescription,ComputerName
 
"
 
$command = $connection.CreateCommand()
$command.CommandText = $Query
$reader = $command.ExecuteReader()
$table = New-Object -TypeName 'System.Data.DataTable'
$table.Load($reader)
 
# Send html email
$style = @"
 
"@
 
$Properties = @(
'ComputerName',
'UserName',
'ClientStateDescription',
'Days Active',
'Days Since Last Eval',
'SCCM Client Registration Date',
'LastActiveTime',
'LastHealthEvaluation',
'Last Evaluation Healthy',
'ClientRemediationSuccess',
'LastHealthEvaluationResult',
'HealthCheckDescription',
'ResultDetail',
'ResultCode'
)
 
$body = $table |
Select-Object -Property $Properties|
ConvertTo-Html -Head $style -Body "

Computers that failed or have unknown CCMEval Results, and have been active in the last week ($($results.Count))

 
" |
Out-String
 
Send-MailMessage @EmailParams -Body $Body -BodyAsHtml
 
# Close the connection
$connection.Close()
capture
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)