Michigan Endpoint Management User Group

 View Only
Expand all | Collapse all

software license report with Purchase order detail

  • 1.  software license report with Purchase order detail

    Posted Apr 09, 2014 09:51 AM

    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.[Quantity],
        spd.[Purchase Date],
        spd.[Description]
    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]

     



  • 2.  RE: software license report with Purchase order detail

    Posted Apr 29, 2014 03:25 PM

    good job - will have to play with this one