MECM Client Remediation -SMSAGENTHOST – Service Auto stopped

Issue:

The SMSAGENTHOST service or CCMEXEC is going on stopped state straight after the CCMEVAL task scheduler remediation.

Workaround/Solution:

CCMREPAIR.EXE execution on the clients where the smsagent host or CCMEXEC is stopped would be remediated, since it’s the native client exe available in the client path “c:/windows/ccm/ccmrepair.exe” its considered as the solution rather than a workaround.

Cause:

Unknown, MEMCM agent service goes down/stops immediately after CCMEvaluation is executed based on task scheduler health checks.

No client as per console but the SMS agent host service getting stopped.

Remediation: CCMRepair.exe to be executed.

Post remediation, the client actions are repaired & client is turned green & healthy!

Compliance for Needed patch count per device in a collection based on scan report

SELECT CS.Name0 AS [Server Name], CASE WHEN (SUM(CASE WHEN UCS.status = 2 THEN 1 ELSE 0 END)) > 0 THEN (‘Needs ‘ + (CAST(SUM(CASE WHEN UCS.status = 2 THEN 1 ELSE 0 END) AS varchar(10)) + ‘ Patches’)) ELSE ‘Good Client’ END AS ‘Status’, ws.LastHWScan AS ‘Last HW scan’, FCM.CollectionID
FROM v_UpdateComplianceStatus AS UCS LEFT OUTER JOIN
v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = UCS.ResourceID INNER JOIN
v_CICategories_All AS catall2 ON catall2.CI_ID = UCS.CI_ID INNER JOIN
v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = ‘UpdateClassification’ LEFT OUTER JOIN
v_GS_WORKSTATION_STATUS AS ws ON ws.ResourceID = CS.ResourceID LEFT OUTER JOIN
v_FullCollectionMembership AS FCM ON FCM.ResourceID = CS.ResourceID
WHERE (UCS.Status = ‘2’) AND (FCM.CollectionID = ‘P01000xx’)
GROUP BY CS.Name0, ws.LastHWScan, FCM.CollectionID
ORDER BY [Server Name], ‘Last HW scan’, FCM.CollectionID

Patch compliance report based on SUG

SELECT sys.Name0 AS [Machine Name], sys.User_Name0, ui.Title AS [Patch Title], (CASE ucs.status WHEN 2 THEN ‘Required’ WHEN 1 THEN ‘NOT REQUIRED’ WHEN 0 THEN ‘INSTALL STATE UNKNOWN’ WHEN 3 THEN ‘Installed’ END) AS [Install Status], v_AuthListInfo.Title AS [Update Group], ui.ArticleID,
(CASE UI.IsExpired WHEN 0 THEN ‘NOT EXPIRED’ WHEN 1 THEN ‘EXPIRED’ END) AS EXPIRY, (CASE UI.IsSuperseded WHEN 0 THEN ‘NOT Superseded’ WHEN 1 THEN ‘Superseded’ END) AS SEPERSEDENCE,
(CASE ui.Severity WHEN 0 THEN ‘none’ WHEN 2 THEN ‘low’ WHEN 6 THEN ‘MODERATE’ WHEN 8 THEN ‘IMPORTANT’ WHEN 10 THEN ‘CRITICAL’ END) AS SEVERITY, ui.DatePosted AS DateReleased, ucs.LastStatusChangeTime AS DateInstalled
FROM v_ClientCollectionMembers INNER JOIN
v_R_System AS sys ON v_ClientCollectionMembers.ResourceID = sys.ResourceID LEFT OUTER JOIN
v_CIRelation INNER JOIN
v_UpdateInfo AS ui INNER JOIN
v_UpdateComplianceStatus AS ucs ON ui.CI_ID = ucs.CI_ID ON v_CIRelation.ToCIID = ui.CI_ID INNER JOIN
v_AuthListInfo ON v_CIRelation.FromCIID = v_AuthListInfo.CI_ID ON sys.ResourceID = ucs.ResourceID
GROUP BY sys.Name0, ui.IsExpired, ui.IsSuperseded, ucs.Status, ui.Severity, v_AuthListInfo.Title, v_ClientCollectionMembers.CollectionID, sys.User_Name0, ui.ArticleID, ui.Title, ui.DatePosted, ucs.LastStatusChangeTime
HAVING (ui.IsExpired = 0) AND (v_AuthListInfo.Title LIKE ‘Workstations Monthly Patching -SUG’) AND (v_ClientCollectionMembers.CollectionID LIKE ‘P01000XX’)
ORDER BY [Machine Name]