Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

Query Custom Counters

You have created a custom counter in Spotlight on SQL Server and now want to create a report that contains data from that counter.

Querying custom counters is exactly the same as querying other data in the Spotlight Statistics Repository. SQL Server custom counters are stored in the statistic class ‘sqlcustomcounters’ and Windows custom counters are stored in the class ‘windowscustomcounters’. You can use the API stored procedures to retrieve date ranges and column names with these like any other data collection.

Figure 3: Sample T-SQL statement that retrieves SQL Server custom counter values.

select

sp.timecollected,

max(case when sn.statistic_name = 'countername' then sp.raw_value end) as 'countername',

max(case when sn.statistic_name = 'countervalue' then sp.raw_value end) as 'countervalue'

from

spotlight_perfdata sp

join spotlight_stat_classes sc on sp.statistic_class_id = sc.statistic_class_id

join spotlight_stat_names sn on sp.statistic_name_id = sn.statistic_name_id

join spotlight_monitored_objects so on sp.monitored_object_id = so.monitored_object_id

where

sc.statistic_class_name = 'sqlcustomcounters'

and so.monitored_object_name = 'Windows01_SQLServer789_sqlserver'

and sp.timecollected between '2009-09-01' and '2009-09-30'

group by

sp.timecollected, sp.statistic_key_id

order by

sp.timecollected

Figure 4: Results returned for the custom counter "Pages Allocated"

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating