ITMS Administrator Group

Hi I need help to find the query behind the calculation of Cost in the Software Product License and Usage, when you click on Manage > Software and select one of the software. 

Would be good to know all the queries but currently need the cosl calculations. I did thought of pulling the average cost from purchases.

I would say that it is either spAC_GetCoveredProductLicenses or spAC_GetSoftwareProductLicenseData. 

The latter also targets usage data.

Thank you "spAC_GetSoftwareProductLicenseData"  does prvides the query, but not being a SQL expert I cannot see how is it estimating the Over deployed cost, (how the Unit prcie is calculated as the each prchase have different price)

Your screenshot is rather small so its hard to view.  I believe the 'Installed and used' value is showing 4, and the 'Installed but unused' value is showing 37, yes? 

If so, then the over deployed value will be 37 + 4 = 41 - 7 = 34 x the cost of a single license.

When you added a license to the Managed Software Product, what did you enter for quantity and total cost?

I think the following section of the SP's query is used to display this information:

        OUTER APPLY(SELECT COUNT(DISTINCT inst._ResourceGuid) AS NumInstalled
                    FROM ResourceAssociation ra
                    JOIN Inv_InstalledSoftware inst
                      ON inst._SoftwareComponentGuid = ra.ChildResourceGuid
                      AND inst.InstallFlag = 1
                    WHERE inst._ResourceGuid IN (SELECT Guid FROM vComputer WHERE IsManaged = 1)
                    AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
                    AND ra.ParentResourceGuid = sp.Guid)inst
        OUTER APPLY(SELECT ISNULL(SUM(CASE WHEN ISNULL(sci.[End Date], GETDATE()) >= GETDATE() THEN purchase.Quantity ELSE 0 END), 0) AS [NumValidLicenses]
                          ,ISNULL(SUM(CASE WHEN ISNULL(sci.[End Date], GETDATE()) < GETDATE() THEN purchase.Quantity ELSE 0 END), 0) AS [NumExpiredLicenses]
                          ,ISNULL(SUM(ISNULL(ci.Amount, 0)), 0) AS [TotalCost]

Hi SK, thanks for your help, there were multiple purchases with different price each time, so that is why wondering how the unit price is calculated. 

The price is added together in order to create the total, and will then most likely be divided by the number of installs, unless it is able to determine which install is associated with which license.

If you no longer require help, please make this thread as resolved.