DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @v2_ReportFilter uniqueidentifier
SET @v2_ReportFilter = '%Report Filter%'
SELECT
w1.Name as 'Name',
t1.[Device ID] as 'Drive',
t1.[Description] as 'Description',
t1.[Size (Bytes)] as 'Disk Size Bytes',
(t1.[Size (Bytes)])/1024/1024/1024 as 'Disk Size GB',
t1.[Free Space (Bytes)] as 'Free Space Bytes',
(t1.[Free Space (Bytes)])/1024/1024 as 'Free Space MB',
CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) AS [Free Space(%)], --this is the percentage field
w1.Guid as 'ResourceGuid',
u1.[User] as 'Primary User'
FROM
vComputer w1
INNER JOIN vHWLogicalDisk t1
ON w1.Guid = t1._ResourceGuid
LEFT JOIN Inv_AeX_AC_Primary_User u1
ON w1.Guid = u1._ResourceGuid
INNER JOIN [CollectionMembership] AS [ajs7_CollectionMembership]
ON ([w1].[Guid] = [ajs7_CollectionMembership].[ResourceGuid])
AND
([ajs7_CollectionMembership].[CollectionGuid] = @v2_ReportFilter)
WHERE
(
([w1].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
)
AND u1.[Month] = DATENAME(mm, GETDATE())
AND LOWER(t1.[Description]) LIKE '%local%'
AND t1.[Size (Bytes)] > '2000'
and CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) < '50' ---this is the percentage parameter
ORDER BY w1.[Name]