Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Sql querry for Altiris report

Created: 03 Apr 2013 • Updated: 04 Apr 2013 | 5 comments
This issue has been solved. See solution.

I need sql querry for software installation report with date wise

Operating Systems:

Comments 5 CommentsJump to latest comment

prakash@mcits.com.sg's picture

Hello Manish,

I need software installation report with IP address. Please help.\

Prakash Soni

Sachin Sawant's picture

try this querry

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

SELECT DISTINCT comps.Guid [Guid]
               ,comps.Name [Computer Name]
            ,sci.[Name]
            ,isc.[Version]
            ,company.[Name] [Company]
,Inv_AeX_AC_TCPIP.[Ip Address]

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 Inv_AeX_AC_TCPIP
ON Inv_AeX_AC_TCPIP.[Host Name]=comps.Name

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]

SOLUTION
prakash@mcits.com.sg's picture

thx sachin, I have checked this querry on my Altiris console. It can fetch the result as I need.

Thank you very much.

Prakash Soni

Ambesh_444's picture

Hello Sachin, 

Nice querry....cool querry Thumbsup!!!!!!

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."