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.
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
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
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 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.