This query will return the name of the device that have profiles with the red cross (profile in error state):
select name from CM_COMPUTER_SYSTEM where cs_id IN (select cs_id from SSRV2profile where status = 'error')
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;
Related KBs:
UIM - All Monitoring Configuration Services (MCS) Profiles Locked
UIM - Unlock MCS Profiles locked with Red Cross