Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

How SQL database free space monitoring works in the SQL management pack

I have written a query from SCOM dbs to collect free space for All drives. I want to select only drives that has MDF or LDF files. How Can I identify those Drives into My Query.
My query is
select me.path as ServerName,case when c.IsVirtualMachine=1 then ‘Virtual’ Else ‘Physical’ END as ServerType,
pd.datetime, me.DisplayName as DriveName,id.VolumeName_65571163_F69F_CFDB_49A1_2730285FD2A2 AS Label,
[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7] AS TotalSize,
[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7]-pd.averageValue as UsedSpace,pd.averageValue as FreeSpace,
[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7]-pd.MaxValue as MaxUsed,
[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7]-pd.MinValue as MinUsed,
[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7]-pd.averageValue as AvgUsed ,
pr.Countername, pr.ObjectName,id.DriveType_67A23C3E_F435_A1B1_DE3E_D8C412E6D9D5
from perf.vPerfDaily pd
inner join vManagedEntity me
on me.ManagedEntityRowId=pd.ManagedEntityRowId
inner join vPerformanceRuleInstance pri
on pri.PerformanceRuleInstanceRowId=pd.PerformanceRuleInstanceRowId
inner join vPerformanceRule pr on pr.RuleRowId=pri.RuleRowId
inner join operationsmanager.dbo.MTV_Microsoft$Windows$Server$10$0$LogicalDisk id
on id.BaseManagedEntityid=me.ManagedEntityGUID
inner join [OperationsManager].[dbo].[MTV_Computer] C on c.DisplayName=me.Path
where pr.ObjectName=’Logicaldisk’
and pr.CounterName=’Free Megabytes’