Return
-
Title
What queries are used to generate SQL Server and Windows Health reports? -
Description
What queries are used to generate SQL Server and Windows Health reports? -
Resolution
Query used to pull data for these reports:DECLARE @t table([Time] datetime,monitored_object_id int,monitored_object_name nvarchar(200),monitored_object_display_name nvarchar(200),sqlconnectioncnt 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'table_name',@column_list = N'column_name'select monitored_object_display_name as [Connection], [Time] as [Date Time],sqlconnectioncnt as [Sessions] from @torder by monitored_object_display_name, [Time]1. host_name = Monitored connection name2. Change table_name and column_list conditions with below table an columns values obtained from below queries.3. Obtain table names for SQL Server report:exec spotlight_get_tables 'sqlserver_spotlight'4. Run below query for each table to obtain the desired column name:exec spotlight_get_table_columns 'sqlserver_spotlight','SQLConnections'exec spotlight_get_table_columns 'sqlserver_spotlight','sqlstatistics'exec spotlight_get_table_columns 'sqlserver_spotlight','sqlbuffermanager'exec spotlight_get_table_columns 'sqlserver_spotlight','sqlcachemanager'exec spotlight_get_table_columns 'sqlserver_spotlight','sqlmemorymanager'5. Obtain table names for Windows health report:exec spotlight_get_tables 'windows'6. Run below query for each table to obtain the desired column name:exec spotlight_get_table_columns 'windows','physicalmemory'exec spotlight_get_table_columns 'windows','memory'exec spotlight_get_table_columns 'windows','pagingfiles'