UIM - Find all hosts with failed MCS profiles

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

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, or higher

Resolution

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')

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;


Related KBs:

UIM - All Monitoring Configuration Services (MCS) Profiles Locked
UIM - Unlock MCS Profiles locked with Red Cross