How to pull disk disk I/O Stats info from the Spotlight repository database for reporting purposes.
The following query needs to be run again the spotlightstatisticrepository database
===========
declare @start datetime
declare @end datetime
-- change the start and end dates as required
set @end = GETDATE()
set @start = GETDATE() - 7
declare @melist nvarchar(max)
declare @domain nvarchar(max)
set @domain = (SELECT TOP 1 [domain_description]
FROM [spotlight_domains])
-- get a list of all Windows connections
set @melist = (SELECT [monitored_object_name] + ','
FROM [spotlight_monitored_objects] mo
join spotlight_technologies tech on mo.technology_id = tech.technology_id
where tech.technology_name = 'os/windows'
order by mo.monitored_object_name
FOR XML PATH('') )
DECLARE @data table
(
timecollected datetime,
monitored_object_id int,
monitored_object_name nvarchar(max),
monitored_object_display_name nvarchar(max),
Name nvarchar(max),
PctBusy float,
ServiceTime float,
QueueLength float,
WritesPerSec float,
IOPerSec float,
BytesReadPerSec float,
TotalBytesPerSec float,
BytesWrittenPerSec float,
ReadsPerSec float,
statistic_key_id int
)
insert into @data
EXEC [dbo].[spotlight_rt_get_batch_data]
@start_date = @start,
@end_date = @end,
@domain_name_list = @domain,
@monitoredobject_list = @melist,
@table_name = N'physicaldisks',
@column_list = N'Name,ReadsPerSec,WritesPerSec,IOPerSec,BytesReadPerSec,BytesWrittenPerSec,TotalBytesPerSec,PctBusy,ServiceTime,QueueLength';
select * from @data d
order by monitored_object_display_name, name, timecollected
==============
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center