Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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