Video Screencast Help

software license report with Purchase order detail

Created: 09 Apr 2014 • Updated: 09 Apr 2014 | 1 comment

I had be searching for a report for this information and found several posts that had requested a report that showed Software licenses and the total # of Purchases, or licenses with purchase information.  Sometimes your boss just wants the whole list.

So I did two things. The first report is Total number of Licenses owned and the Name of the License. 

The second report I simply modified a canned report "Software License's Purchased Quantity" to show all software purchases and the licneses.

Please be careful because I have not taken into account Borrowed or upgraded licenses which Symantec doesn't do very well in direct reporting. 

I hope this helps some people out. 

--All Software Licenses with Total Ordered counts

SELECT     SUM(inv_software_purchase_details.Quantity) AS 'Total Purchases', re1.Name, ra.ParentResourceGuid AS _itemguid
FROM         dbo.Inv_Software_Purchase_Details INNER JOIN
                      dbo.ResourceAssociation AS ra ON Inv_Software_Purchase_Details._ResourceGuid = ra.ChildResourceGuid INNER JOIN
                      dbo.vResourceEx AS re1 ON ra.ParentResourceGuid = re1.Guid
WHERE     (ra.ResourceAssociationTypeGuid LIKE 'F7002A49-11A6-44EA-AF19-2E06632FF831')
GROUP BY re1.Name, ra.ParentResourceGuid
ORDER BY re1.Name


--- All Software Licenses and purchases Detailed.

declare @RAswLicenseToswPurchase uniqueidentifier
set @RAswLicenseToswPurchase = 'f7002a49-11a6-44ea-af19-2e06632ff831'
select distinct
    i.Guid as _ItemGuid,
    i.Name as [Software Purchase],
    spd.[Purchase Date],
from  vSoftwareLicense sl
    join        ScopeMembership sm on sm.ResourceGuid = sl._ResourceGuid
    join        ResourceAssociation ra on sl._ResourceGuid = ra.ParentResourceGuid and ra.ResourceAssociationTypeGuid = @RAswLicenseToswPurchase
    join        vItem i on ra.ChildResourceGuid = i.Guid
    left join    Inv_Software_Purchase_Details spd on ra.ChildResourceGuid = spd._ResourceGuid

order by [Software Purchase]

Operating Systems:

Comments 1 CommentJump to latest comment

sdmayhew's picture

good job - will have to play with this one 

Altiris user since 2001, Asset Management for 25 years