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.

SQL query for multiple VCOPs alerts filtered by type, level and resource kind 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
;
NumberDescription
AlarmType12Fault
AlarmLevel2Warning
AlarmLevel4Critical
RESKND_ID18*vCenter*
RESKND_ID20*ESX Host*

SQL query for the resource kind IDs in this installation* Every vCenter Operations installation has unique Resource Kind IDs. You can see your IDs in the ResourceKind table.

Leave a Reply

Required fields are marked *.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>