Запрос позволяет получить данные о месте на дисках для определённой даты.
Используются обе БД (Operational и DataWareHouse). Таблицы:
- DataWarehouseDB.Perf.vPerfDaily
- DataWarehouseDB.dbo.vManagedEntity
- DataWarehouseDB.dbo.vPerformanceRuleInstance
- DataWarehouseDB.dbo.vPerformanceRule
- OperationsDB.dbo.BaseManagedEntity
Выборка производится по объектам «LogicalDisk» и «Logical Disk» и счётчикам, содержащим в имени «Free Megabytes» и «% Free». Для получения общего размера диска, занятое место в Мб делится на занятое место в процентах. Затем все размеры переводятся в байты и округляются.
SELECT
-- Get Size (we know free space in MB and percent)
ROUND(1024*1024*SUM(CASE WHEN CounterName LIKE '%[%]%' THEN NULL ELSE AverageValue END)*100/SUM(CASE WHEN CounterName LIKE '%[%]%' THEN AverageValue ELSE NULL END), 0) AS [SizeBytes],
-- Get free Space
--ROUND(SUM(CASE WHEN CounterName LIKE '%[%]%' THEN NULL ELSE AverageValue END)*1024*1024, 0) AS [FreeBytes],
-- Get Used space
ROUND(1024*1024*(SUM(CASE WHEN CounterName LIKE '%[%]%' THEN NULL ELSE AverageValue END)*100/SUM(CASE WHEN CounterName LIKE '%[%]%' THEN AverageValue ELSE NULL END) -
SUM(CASE WHEN CounterName LIKE '%[%]%' THEN NULL ELSE AverageValue END)), 0) AS [UsedBytes],
-- Other fields
ManagedEntityGuid,ParentInternalId,ObjectName,[BaseEntity].Path,[BaseEntity].DisplayName FROM
-- Performance data
DataWarehouseDB.Perf.vPerfDaily [Perf]
-- Get Managed entity IDs for performance data
LEFT JOIN DataWarehouseDB.dbo.vManagedEntity [Entity] on [Perf].ManagedEntityRowId=[Entity].ManagedEntityRowId
-- Get rule instance
LEFT JOIN DataWarehouseDB.dbo.vPerformanceRuleInstance [RuleInstance] on [Perf].PerformanceRuleInstanceRowId = [RuleInstance].PerformanceRuleInstanceRowId
-- Get rule class
LEFT JOIN DataWarehouseDB.dbo.vPerformanceRule [Rule] on [RuleInstance].RuleRowId = [Rule].RuleRowId
-- Get base managed entity id from OPSDB
LEFT JOIN OperationsDB.dbo.BaseManagedEntity [BaseEntity] on [Entity].ManagedEntityGuid = [BaseEntity].BaseManagedEntityId
-- Get Parent object (host) internal ID to match with Infra entries
LEFT JOIN (
SELECT [BaseManagedEntityInternalId] AS [ParentInternalId],[BaseManagedEntityId] FROM OperationsDB.dbo.BaseManagedEntity
) [BaseEntity2] on [BaseEntity].TopLevelHostEntityId = [BaseEntity2].BaseManagedEntityId
WHERE (ObjectName = 'LogicalDisk' OR ObjectName = 'Logical Disk')
AND [BaseEntity].IsDeleted = 0
AND [BaseEntity].IsManaged = 1
AND (CounterName LIKE '%Free Megabytes%' OR CounterName LIKE '%[%] Free%')
--Specify date
AND (CONVERT(DATE,DateTime)) = '2014-06-21'
GROUP BY ManagedEntityGuid,ParentInternalId,ObjectName,[BaseEntity].Path,[BaseEntity].DisplayName
Ссылки:
Anton Tikhonov's blog 