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 query

Created: 02 Sep 2013 • Updated: 01 Oct 2013 | 8 comments
This issue has been solved. See solution.

Hi Guys,

Is therw any sql query is available from which I can get software installation report datawise.

Thanks in advance.

Operating Systems:

Comments 8 CommentsJump to latest comment

Ambesh_444's picture

Hello,

Check Sachin sawant post in below article.

https://www-secure.symantec.com/connect/forums/sql-querry-altiris-report

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

SOLUTION
network101's picture

Can username also be added into this report?

Ambesh_444's picture

User name details you can get in normal sepm report also.

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

MichaelCiv's picture

Im slightly confused as to exactly what data your trying to pull here

Sachin Sawant's picture

Hi Vikysymautre,

That means you can find which user can use which software.

Sachin Sawant's picture

Hi Vkysysmautre,

Please find query Software Report with User Full Name Last Logon User but in this query not mansion date of installation if you need please reply.

Software Report with User Full Name Last Logon User

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]

,Inv_UG_User_Account.[Full Name], [User Name]

,Inv_AeX_AC_Identification.[Last Logon User]

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 Inv_UG_User_Account

ON Inv_UG_User_Account._ResourceGuid=inst._ResourceGuid

JOIN Inv_AeX_AC_Identification

ON Inv_AeX_AC_Identification.[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]

Ambesh_444's picture

Hello Viky,

If your proble is resolved then don't forget to mark the thread as solved which thread help you best.

so it can benefit future users who may have the same question

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

Vikysymautre's picture

Hi all sorry for delay.
Actually i was on leave. Thanks all for your support.