Hi all! Tell me, please, is it possible to get a report on the use of licenses in the context of subnets?
For example, there is a report:
DECLARE @TrusteeScope nvarchar(max)
SET @TrusteeScope = '%TrusteeScope%'
DECLARE @Culture nvarchar(max)
set @Culture = '%_culture%'
declare @Name nvarchar(255)
set @Name = '%Name%'
select
p.Guid as [_ItemGuid],
p.Name as [Software Product],
isnull(compliance.[Purchased Licenses],0) as [Purchased],
isnull(borrowed.[Borrowed License],0) [Borrowed Licenses],
isnull(donated.[Donated License], 0) [Donated Licenses],
isnull(install.Installed,0) as [Installs],
--isnull(compliance.[Non-Inventoried Installs],0) as [Non-Inventoried Installs],
isnull(compliance.[Purchased Licenses],0) + isnull(borrowed.[Borrowed License],0) -
isnull(donated.[Donated License], 0) - isnull(install.Installed,0) - isnull(compliance.[Non Inventoried Installs],0) as [Compliance]
from vSoftwareProduct p
left join Inv_SoftwareProduct_ComplianceInfo compliance on compliance._ResourceGuid = p.Guid
left join (select _ResourceGuid, sum(SharingNumber) as [Borrowed License] from Inv_SoftwareProduct_LicenseSharing where IsBorrower = 1 group by _ResourceGuid) borrowed on borrowed._ResourceGuid = p.Guid
left join (select _ResourceGuid, sum(SharingNumber) as [Donated License] from Inv_SoftwareProduct_LicenseSharing where IsBorrower = 0 group by _ResourceGuid) donated on donated._ResourceGuid = p.Guid
left join (select _ResourceGuid, sum(1) as Installed from Inv_SoftwareProduct_InstallationInfo group by _ResourceGuid) install on install._ResourceGuid = p.Guid
join [vAC_ManagedSoftwareProducts] t01 on t01.Guid = p.Guid
where lower(p.Name) like '%' + lower(@Name) + '%'
AND p.Guid in ( SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN ( SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@TrusteeScope) ))
and IsManaged <> 2
order by p.Name
Is it possible to modify by adding a subnet filter?