How to pull disk usages information from Spotlight repository database for reporting purpose?
This query needs to be executed against spotlightstatisticrepository database:
=============
set @domain = (SELECT TOP 1 [domain_description]
FROM [spotlight_domains])
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 @disks table
(
timecollected datetime,
monitored_object_id int,
monitored_object_name nvarchar(max),
monitored_object_display_name nvarchar(max),
name nvarchar(max),
totalmb real,
freemb real,
statistic_key_id int
)
insert into @disks
EXEC [dbo].[spotlight_rt_get_batch_data]
@start_date = N'1 Jan 2000',
@end_date = N'30 Dec 2050',
@domain_name_list = @domain,
@monitoredobject_list = @melist,
@table_name = N'filesystems',
@column_list = N'name,totalmb,freemb';
select monitored_object_display_name as 'Host',
timecollected as 'Date',
name as 'Drive',
totalmb as 'Total MB',
totalmb - freemb as 'Used MB',
freemb as 'Free MB',
case when totalmb = 0 then null else (totalmb-freemb)/totalmb*100 end as 'Percent Used'
from @disks
order by monitored_object_display_name, name, timecollected
============
