Video Screencast Help

SW Product showing different count

Created: 22 Dec 2013 | 2 comments
skhs's picture

Hi When going through the Manage > Software Catalog and editing the one of Managed product it shows install count that has two issues. 

1. It does not match the Software Product License compliance report. 

2. It would show correct qty when you edit the cirtera (not make any change but just edit and set back to orignal state)

Rules and Criteria are set correctly , nightly task run but the behaviour is very inconsistant. Although not an issue for reporting of license compliance but just annoyance for Asset admin. 


Operating Systems:

Comments 2 CommentsJump to latest comment

SK's picture

That report uses the following SQL:

DECLARE @TrusteeScope nvarchar(max)
SET @TrusteeScope = '2e1f478a-4986-4223-9d1e-b5920a63ab41,582029e2-fc5b-4717-8808-b80d6ef0fd67),b760e9a9-e4db-404c-a93f-aea51754aa4f'
DECLARE @Culture nvarchar(max)
set @Culture = '%_culture%'
declare @Name nvarchar(255)
set @Name = 'name'
  p.Guid as [_ItemGuid],
  p.Name as [Software Product],
  isnull(compliance.[Purchased Licenses],0) as [Purchased],
  isnull(borrowed.[Borrowed License],0) [Borrowed License],
  isnull(donated.[Donated License], 0) [Donated License],
  isnull(install.Installed,0) as [Installed],
  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
  lower(p.Name) like '%' + lower(@Name) + '%'
  p.Guid in (
                [ResourceGuid] FROM [ScopeMembership]
                [ScopeCollectionGuid] IN (
                  SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@TrusteeScope)
order by

Please provide a screenshot of the Managed Software Product showing the install count, so that we can track down the SQL used by that UI.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

skhs's picture

Hi here are screen shots, I see when looking at component vs the reports , and just trying to understand the difference. I am thinking probably ismanaged or something like that coming into the play but not sure. 

SW_Prod_Lic Compliance.pngSW_Prod.png