SQL Query - # of Objects with a specific alert/state

I’m trying to build a SQL query that I can use in the SQL plugin to display the number of objects that have a particular alert or are in a particular state.

Example:

of Servers with a Heartbeat Failure

of Logical Disks with critically low disk free space

While I can create views that show the particular objects, we’re also wanting to “know the number” of specific objects of type.

Anyone?

1 Like

If you can make a query that shows your result in objects, then you can change the “Select … from” part of your query, into “Select count(*) from” and you’ll get the number of rows the query returns. This you can then use as a Scalar display type in the SQL Plugin.

Below query shows you how many Alerts there are in the database with resolution state 249:
select count(*) from Alert.vAlertResolutionState where resolutionstate = 249

As this hasn’t been solved yet, I’d like to point you towards Kevin Holman and his useful queries page:

https://blogs.technet.microsoft.com/kevinholman/2007/10/18/useful-operations-manager-2007-sql-queries/

Whilst these are for 2007, they are easily modified to work for 2012 and most will work without issues. Hopefully you’ll be able to muddle your way through creating your own query :slight_smile:

1 Like

Do you have any SQL so far - might be a useful starting point. It sounds like you could add a COUNT() in.

I use this to show me gray agents from the OpsMgrDB

SELECT ManagedEntityGenericView.DisplayName, ManagedEntityGenericView.AvailabilityLastModified
FROM ManagedEntityGenericView
INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id
WHERE (ManagedTypeView.Name = ‘microsoft.systemCenter.agent’) AND (ManagedEntityGenericView.IsAvailable = 0)
ORDER BY ManagedEntityGenericView.DisplayName

I don’t see any query, did you attach something?

I also found this one, that shows the particular agent, but my attempts to use SELECT COUNT(*) haven’t worked yet

SELECT bme.DisplayName, s.LastModified as LastModifiedUTC, dateadd(hh,-5,s.LastModified) as ‘LastModifiedCST (GMT-5)’
FROM state AS s, BaseManagedEntity as bme
WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid
IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SystemCenter.HealthService.ComputerDown’)
AND s.Healthstate = ‘3’ AND bme.IsDeleted = ‘0’
ORDER BY s.Lastmodified DESC

Well, it’s sort of been answered, but yes, I’ve seen Holmans queries, and that’s part of the problem. All of this knowledge, and Holmans queries from 2007 are still the only examples we have.

Answer - be a SQL query master. I still don’t have a good working example of something that seems pretty straight forward and something that every single one of us could find valuable in regards to using them in SquaredUp. Imagine being able to provide your NOC with the Count of systems that are down, vs a list of systems down.