Return
-
Title
How to manually pull respond time in spotlight repository. -
Description
User wants to get a report from Spotlight that displays SQL Response time every 5 minutes from spotlight repository. -
Resolution
The monitoredobject_list parameter accepts a comma delimited list of connections in the format host_instance_sqlserver for named instances or host_sqlserver for default instances. E.g. rqa777r43_test14_sqlserver or ptr2i87sw_sqlserverHere's the scriptsUSE [SpotlightStatisticsRepository]GODECLARE @t table([Time] datetime,monitored_object_id int,monitored_object_name nvarchar(200),monitored_object_display_name nvarchar(200),[Response Time] int,statistic_key_id int)insert into @tEXEC [dbo].[spotlight_rt_get_batch_data2]@start_date = N'jan 1 2012',@end_date = N'jan 1 2018',@monitoredobject_list = N'host_instance_sqlserver',@table_name = N'SQLResponseTime',@column_list = N'ResponseTime'select monitored_object_display_name as [Connection], [Time] as [Date Time],[Response Time] from @torder by monitored_object_display_name, [Time]**See the Spotlight Statistics Repository Database blog for more information on how to query data from Spotlight repository database.