how do I find all the machine with failed MCS profiles?

Document ID : KB000102375
Last Modified Date : 14/03/2019
Show Technical Document Details
Question:
How can I find all the hosts with profiles in error status, where the Monitoring tab in USM shows a red cross?
Environment:
UIM 8.51 9.02
Answer:
This query will return the name of the device that have profiles with the red cross:

select name from CM_COMPUTER_SYSTEM where cs_id IN (select cs_id from SSRV2profile where status = 'error')
Additional Information:
The following query will also find "problematic" profiles and will also provide the latest MCS audit trail message for these profiles in the outputs generated:

SELECT ccs.name, b.cs_id, b.status, b.profileid, b.ancestorprofile, a.id, a.timestamp, a.objectname, a.objectvalue, a.action
FROM SSRV2AuditTrail a
JOIN SSRV2Profile b
ON a.objectid = b.profileId
JOIN (SELECT objectid, max(id) AS id FROM SSRV2AuditTrail GROUP BY objectid) c
ON a.id = c.id AND a.objectid = c.objectid
JOIN CM_COMPUTER_SYSTEM ccs
ON b.cs_id = ccs.cs_id
WHERE b.status NOT IN ('ok', 'new', 'suspended')
ORDER BY name;