SQL query to filter active vCenter Faults in the vCOps database

As described in SQL queries to filter vCOps alerts and events it is not possible to filter events and alarms by other things than the resource name column. It is also not possible to filter by alarm type or level.

With that SQL query you can workaround that problem.

use vcops

select dateadd(SECOND, convert(bigint, StartTimeUTC) / 1000, convert(datetime, '1-1-1970 02:00:00')) as Date, Name, MessageInfo
FROM Alarm INNER JOIN AliveResource ON Alarm.RESOURCE_ID = AliveResource.RESOURCE_ID
WHERE Alarm.CancelTimeUTC IS NOT null
AND Alarm.AlarmType = 12
AND Alarm.AlarmLevel = 2
AND AliveResource.RESKND_ID = 20
order by Date desc
;

• Every vCenter Operations installation has unique Resource Kind IDs. You can see your IDs in the ResourceKind table.