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.
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'
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
sc.statistic_class_name = 'sqlcustomcounters'
and so.monitored_object_name = 'Windows01_SQLServer789_sqlserver'
and sp.timecollected between '2009-09-01' and '2009-09-30'
Figure 4: Results returned for the custom counter "Pages Allocated"