SQL queries to filter vCOps alerts and events

In the current version of vCenter Operations it is not possible to filter events and alarms by the info column, but only by the resource name column. With SQL queries you can workaround that limitation. Because of that limitation you can only filter by a hostname or datastore name, but you cannot search for certain events like “connection loss to server…”.

In the Alerts Overview you can browse through the list of the generated alerts. In the tree on the left side you can filter by resources like ESX hosts or datastores. On the top left side you have the search form. This search only filters by the “resource name” column, but not by the info column. In this example it means you cannot filter by “Resource is down”.

You can run sql queries against the vcops tables. In this example i use a MS SQL server. The other by vCOps supported database is Oracle. The select queries alerts that match the shown Alert.Info text entries. The results can then be exported into a csv file for further reports.

use vcops

select dateadd(SECOND, convert(bigint, StartTimeUTC) / 1000, convert(datetime, '1-1-1970 02:00:00')) as Date, Name, Info
FROM Alert INNER JOIN AliveResource ON Alert.RESOURCE_ID = AliveResource.RESOURCE_ID
WHERE Alert.Info LIKE 'Lost Connection to NFS server%'
OR    Alert.Info LIKE 'A possible host failure has been detected by HA on host%'
order by Date desc
;

The second example queries alarms (events) that match the shown Alarm.MessageInfo ‘Connection failed for %’ text entries and the AliveResource.RESKND_ID 20. This number is the internal ID for the resource ESX Host.

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.MessageInfo LIKE 'Connection failed for %'
and AliveResource.RESKND_ID = 20
order by Date desc
;

I have raised a vCOps feature request for filtering by info column. Until VMware provides that feature you can use that queries as a workaround.