Video Screencast Help

Installed software report for (only) managed software

Created: 08 Apr 2013 • Updated: 09 Apr 2013 | 6 comments
R.Prasanna's picture
This issue has been solved. See solution.

Hi,

i have altiris CMS 7.1 SP1  (NO Asset managemnt plug-in) and i need a report for Installed software (only) managed software's and having details like computer name, make, model, serial no, and site information

 

Thanks

 

Operating Systems:

Comments 6 CommentsJump to latest comment

SK's picture

If our "Reports > Discovery and Inventory > Inventory > Cross-platform  Software/Applications > Software > Installed Software" report does not meet your needs, clone it and then modify its SQL definition so it does.

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

SaschaH's picture

I think installed "Software by Computer" is the better starting point. And as SK pointed out clone it and edit the SQL to include the info you need for the computer. The SQL for the hardware info you can find in "Reports > Discovery and Inventory > Inventory > Cross-platform > Hardware > Software > Hardware Inventory Search"

Just add the join from

    JOIN dbo.vHWComputerSystem s
    ON s.[_ResourceGuid]=comps.Guid

 

after the comps gets joined and then add to the select at the top

    s.[Identifying Number] [Serial Number],          
    s.[Manufacturer] [System Manufacturer],
    s.[Model] [Computer Model],

 

or just the whole query I changed in the clone of the Software by computer

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

IF ('%Type%' = 'Managed Software')
SELECT DISTINCT comps.Guid [Guid]
       ,comps.Name [Computer Name],
    s.[Identifying Number] [Serial Number],          
    s.[Manufacturer] [System Manufacturer],
    s.[Model] [Computer Model]
       ,prods.[Name]
       ,spv.[Version]
       ,company.Name [Company]
FROM vRM_Software_Component sc
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sc.Guid
   AND inst.InstallFlag = 1
JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name
      FROM vRM_Software_Product_Item spi
      JOIN ResourceAssociation ra
         ON ra.ParentResourceGuid = spi.Guid
         AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
     )prods
   ON prods.ChildResourceGuid = sc.Guid
JOIN dbo.Inv_Software_Product_State sps
    ON sps._ResourceGuid = prods.Guid
    AND sps.IsManaged = 1
JOIN dbo.Inv_Software_Product_Version spv
        ON spv._ResourceGuid = prods.Guid
JOIN (SELECT vci.Guid, vci.Name
      FROM vRM_Computer_Item vci
      LEFT JOIN ResourceAssociation resAssoc
         ON vci.Guid = resAssoc.ParentResourceGuid
         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
      ) comps
   ON comps.Guid = inst._ResourceGuid
JOIN dbo.vHWComputerSystem s
    ON s.[_ResourceGuid]=comps.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
           FROM RM_ResourceCompany rc
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = rc.Guid
              AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
           ) company
   ON company.softProdGuid = spv._ResourceGuid
        
WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
ORDER BY prods.Name, comps.Name

ELSE
IF ('%Type%' = 'All Software')    
SELECT DISTINCT comps.Guid [Guid]
               ,comps.Name [Computer Name]
               ,sci.[Name]
               ,isc.[Version]
               ,company.[Name] [Company]
FROM vRM_Software_Component_Item sci
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sci.[Guid]
   AND inst.InstallFlag = 1
JOIN (SELECT vci.Guid, vci.Name
      FROM vRM_Computer_Item vci
      LEFT JOIN ResourceAssociation resAssoc
         ON vci.Guid = resAssoc.ParentResourceGuid
         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))  
      ) comps
   ON comps.Guid = inst._ResourceGuid
JOIN dbo.Inv_Software_Component isc
   ON isc._ResourceGuid = sci.[Guid]
LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid
           FROM RM_ResourceCompany vc
           JOIN ResourceAssociation ra
           ON vc.Guid = ra.ChildResourceGuid
           AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
   ON company.SoftCompGuid = sci.[Guid]

WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
ORDER BY sci.Name, comps.Name

 

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

Ambesh_444's picture

Hi,

https://www-secure.symantec.com/connect/forums/how-get-report-altiris-71

 

If you need specific user information, a custom report can be written and the KB attached can assist you in that process:

http://www.symantec.com/docs/HOWTO63547

 

Thank& Regards,

Ambesh

"Your satisfaction is very important to us. If you find above information helpful or it has resolved your issue. Please don't forget to mark the thread as solved."

R.Prasanna's picture

SaschaH almost everything i need ... help me to bring Site name in this report.

i have 18 sites and subnets are mapped for these sites. i need this site information also in this report. 

 

Thanks

Prasanna R

SaschaH's picture

EDIT: Added the Site by Taskserver. Should include all thats needed now.

Is the Task Server for the Site enough? Reduced to only managed software and with Taskserver. Attached it as xml for easy import. Breaking it down to sites needs some more checking.

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT comps.[Guid] [Guid],
       comps.[Name] [Computer Name],
    s.[Identifying Number] [Serial Number],          
    s.[Manufacturer] [System Manufacturer],
    s.[Model] [Computer Model],
    srv.[Name] [TaskServer],
    site.[Name] [SiteName],
       prods.[Name],
       spv.[Version],
       company.[Name] [Company]
FROM vRM_Software_Component sc
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sc.Guid
   AND inst.InstallFlag = 1
JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name
      FROM vRM_Software_Product_Item spi
      JOIN ResourceAssociation ra
         ON ra.ParentResourceGuid = spi.Guid
         AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
     )prods
   ON prods.ChildResourceGuid = sc.Guid
JOIN dbo.Inv_Software_Product_State sps
    ON sps._ResourceGuid = prods.Guid
    AND sps.IsManaged = 1
JOIN dbo.Inv_Software_Product_Version spv
        ON spv._ResourceGuid = prods.Guid
JOIN (SELECT vci.Guid, vci.Name
      FROM vRM_Computer_Item vci
      LEFT JOIN ResourceAssociation resAssoc
         ON vci.Guid = resAssoc.ParentResourceGuid
         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
      ) comps
   ON comps.Guid = inst._ResourceGuid
JOIN dbo.vHWComputerSystem s
    ON s._ResourceGuid=comps.Guid
JOIN [dbo].[Inv_Client_Task_Resources] ctr on  ctr.[_ResourceGuid] = comps.GUID
JOIN [dbo].vComputer srv on srv.Guid = ctr.ClientTaskServerGuid
INNER JOIN (
     SELECT ra1.ParentResourceGuid [ComputerGuid]
     ,       ra1.ChildResourceGuid [SiteGuid]
     FROM ResourceAssociation ra1
     WHERE ra1.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4'
     ) t1
     ON t1.ComputerGuid = srv.[Guid]
JOIN [dbo].vSite site ON site.Guid = t1.SiteGuid
LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
           FROM RM_ResourceCompany rc
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = rc.Guid
              AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
           ) company
   ON company.softProdGuid = spv._ResourceGuid
        
WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
ORDER BY prods.Name, comps.Name
 

AttachmentSize
Managed Software by Computer v2.zip 3.68 KB

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

SOLUTION
R.Prasanna's picture

Hi SaschaH your query worked and i am ready with report what i needed most.yes

As always your comments will be solution or will guide towards it ... Impressed and heart full of thanks.

 

Final Query is below: - which gives all details (Computer name, serial no, Make, Model, IP Address, Subnet, Task server, Site, (Managed) Application name, Version, company)

***********************************************************************

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

 

IF ('%Type%' = 'Managed Software')

SELECT DISTINCT comps.Guid [Guid]

       ,comps.Name [Computer Name],

    s.[Identifying Number] [Serial Number],         

    s.[Manufacturer] [System Manufacturer],

    s.[Model] [Computer Model]

,[tp].[IP Address]

,[tp].[Subnet]

,[cid].[OS Name]

,srv.[Name] [TaskServer]

,site.[Name] [SiteName]

       ,prods.[Name]

       ,spv.[Version]

       ,company.Name [Company]

FROM vRM_Software_Component sc

JOIN Inv_InstalledSoftware inst

   ON inst._SoftwareComponentGuid = sc.Guid

   AND inst.InstallFlag = 1

JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name

      FROM vRM_Software_Product_Item spi

      JOIN ResourceAssociation ra

         ON ra.ParentResourceGuid = spi.Guid

         AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'

     )prods

   ON prods.ChildResourceGuid = sc.Guid

JOIN dbo.Inv_Software_Product_State sps

    ON sps._ResourceGuid = prods.Guid

    AND sps.IsManaged = 1

JOIN dbo.Inv_Software_Product_Version spv

        ON spv._ResourceGuid = prods.Guid

JOIN (SELECT vci.Guid, vci.Name

      FROM vRM_Computer_Item vci

      LEFT JOIN ResourceAssociation resAssoc

         ON vci.Guid = resAssoc.ParentResourceGuid

         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status

      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers

      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))

      ) comps

   ON comps.Guid = inst._ResourceGuid

JOIN dbo.vHWComputerSystem s

    ON s.[_ResourceGuid]=comps.Guid

JOIN [Inv_AeX_AC_TCPIP] AS [tp]

    ON [tp].[_ResourceGuid]=comps.Guid

JOIN [Inv_AeX_AC_Identification] AS [cid]

    ON [cid].[_ResourceGuid]=comps.Guid

JOIN [dbo].[Inv_Client_Task_Resources] ctr on  ctr.[_ResourceGuid] = comps.GUID

JOIN [dbo].vComputer srv on srv.Guid = ctr.ClientTaskServerGuid

INNER JOIN (

     SELECT ra1.ParentResourceGuid [ComputerGuid]

     ,       ra1.ChildResourceGuid [SiteGuid]

     FROM ResourceAssociation ra1

     WHERE ra1.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4'

     ) t1

     ON t1.ComputerGuid = srv.[Guid]

JOIN [dbo].vSite site ON site.Guid = t1.SiteGuid

 

LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name

           FROM RM_ResourceCompany rc

           JOIN ResourceAssociation ra

              ON ra.ChildResourceGuid = rc.Guid

              AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'

           ) company

   ON company.softProdGuid = spv._ResourceGuid

       

WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))

ORDER BY prods.Name, comps.Name

 

ELSE

IF ('%Type%' = 'All Software')   

SELECT DISTINCT comps.Guid [Guid]

               ,comps.Name [Computer Name]

               ,sci.[Name]

               ,isc.[Version]

               ,company.[Name] [Company]

FROM vRM_Software_Component_Item sci

JOIN Inv_InstalledSoftware inst

   ON inst._SoftwareComponentGuid = sci.[Guid]

   AND inst.InstallFlag = 1

JOIN (SELECT vci.Guid, vci.Name

      FROM vRM_Computer_Item vci

      LEFT JOIN ResourceAssociation resAssoc

         ON vci.Guid = resAssoc.ParentResourceGuid

         AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status

      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers

      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope))) 

      ) comps

   ON comps.Guid = inst._ResourceGuid

JOIN dbo.Inv_Software_Component isc

   ON isc._ResourceGuid = sci.[Guid]

LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid

           FROM RM_ResourceCompany vc

           JOIN ResourceAssociation ra

           ON vc.Guid = ra.ChildResourceGuid

           AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company

   ON company.SoftCompGuid = sci.[Guid]

 

WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))

ORDER BY sci.Name, comps.Name

 

    **********************************************************************************

Thanks

Prasanna R