Client Management Suite

 View Only
Expand all | Collapse all

SQL report to show software count by major version number

  • 1.  SQL report to show software count by major version number

    Posted May 06, 2015 01:17 PM

    Need a report that allows you to search for application installs by title or software vendor and displays the results showing number of installs by major version. The report also needs to show the number of licenses purchased from the data we enter in Altiris Asset Management

    Ideally the report would display a table of the results with Software name, vendor name, major version, Division 1 licenses owned, Division 2 licenses owned, Division 1 install counts, Division 2 install counts.

    i can use a case statement to separate the Divisions by OU 

    Any help on this would be great, This is what i have so far, this gives me the count but by all version what i need is for it to say (Adobe Acrobat Pro) then the count of how many of them are installed no matter what version they are using. 



    SELECT
    COUNT(arp.displayname) AS 'Count'
    ,arp.displayname
    ,arp.displayversion
    ,arp.Publisher
    FROM
    Inv_AddRemoveProgram arp
    INNER JOIN
    vComputer vc
    ON arp.[_ResourceGuid] = vc.[Guid]
    WHERE
    arp.DisplayName NOT IN
    (
    'Power Scheme Plug-in Setup'
    ,'Altiris Inventory Agent'
    ,'Patch Management Agent'
    ,'Deployment Solution Agent'
    ,'Software Management Solution Agent'
    ,'Altiris Application Metering Agent'
    ,'Symantec pcAnywhere'
    ,'Symantec_pcAnywhere_plugin_installer'
    ,'Software Management Solution Plugin'
    )
    AND
    arp.DisplayName NOT LIKE 'Security Update%'
    AND
    arp.DisplayName NOT LIKE 'Update for%'
    AND
    arp.DisplayName NOT LIKE 'Hotfix for%'
    AND
    arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
    GROUP BY
    arp.DisplayName, arp.Publisher, arp.displayversion

    order by
    arp.DisplayName asc 


    Thanks in advance for any help given



  • 2.  RE: SQL report to show software count by major version number

    Posted May 07, 2015 10:11 AM

    In Reports, under Service and Asset Management:

    Contract Management > Software Licensing

    You can find a lot of reports that will fulfill your wishes, if not you can tickle its sql code so you can achieve your needs. I think "Software Product Licensing Compliance" report will help you.

    Regards.



  • 3.  RE: SQL report to show software count by major version number

    Posted May 11, 2015 07:40 AM

    thanks for the tip, those report did not help me.