Client Management Suite

 View Only
  • 1.  Report - join sql querys

    Posted Jan 17, 2013 05:01 AM

    Hi, I am just learning sql I have the following 2 querys that I want to combine, they both give part of the results I need but in separate reports

    The brief from the client is to create a report on the following for a future outlook rollout we will be doing:

    • Computer Name
    • User
    • MOEversion (this is a dataclass we've created) 
    • Office version
    • IE version
    • Managed/Unmanaged

    first report - which I have created with the query builder but can convert to sql

    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    SELECT
       [vri2_Computer].[Guid] AS [_ItemGuid],
       [vri2_Computer].[Name],
       [dca4_MOEVersion].[MOEVersion],
       [dca3_AeX AC Identification].[Last Logon User],
       [vri2_Computer].[IsManaged]
    FROM
       [vRM_Computer_Item] AS [vri2_Computer]
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
             ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_MOEVersion] AS [dca4_MOEVersion]
             ON ([vri2_Computer].[Guid] = [dca4_MOEVersion].[_ResourceGuid])
          INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs5_Inv_AeX_AC_Client_Agent]
             ON ([vri2_Computer].[Guid] = [ajs5_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
    WHERE
       (
          ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )
    GROUP BY
       [vri2_Computer].[Guid],
       [dca4_MOEVersion].[MOEVersion],
       [vri2_Computer].[Name],
       [vri2_Computer].[IsManaged],
       [dca3_AeX AC Identification].[Last Logon User]
    ORDER BY
       [dca4_MOEVersion].[MOEVersion] DESC
     

    Second report - found the sql on the forums

    SELECT vc.Guid,vc.Name, arp.DisplayName,arp.DisplayVersion

    FROM vComputer vc

    JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

    WHERE DisplayName LIKE 'microsoft office professional %' AND arp.InstallFlag=1

     



  • 2.  RE: Report - join sql querys

    Posted Jan 17, 2013 09:32 AM

    Try this:

     

    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    SELECT
       [vri2_Computer].[Guid] AS [_ItemGuid],
       [vri2_Computer].[Name],
       arp.DisplayName as 'Office Name',
       arp.DisplayVersion as 'Office Version',
       [dca4_MOEVersion].[MOEVersion] as 'MOE Version',
       [dca3_AeX AC Identification].[Last Logon User],
       [vri2_Computer].[IsManaged]
    FROM
       [vRM_Computer_Item] AS [vri2_Computer]
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
             ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_MOEVersion] AS [dca4_MOEVersion]
             ON ([vri2_Computer].[Guid] = [dca4_MOEVersion].[_ResourceGuid])
          INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs5_Inv_AeX_AC_Client_Agent]
             ON ([vri2_Computer].[Guid] = [ajs5_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
          JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vri2_Computer.Guid
    WHERE
       (
          ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
     AND
     arp.DisplayName LIKE 'Microsoft Office Professional%' AND arp.InstallFlag=1
       )
    GROUP BY
       [vri2_Computer].[Guid],
       [dca4_MOEVersion].[MOEVersion],
       [vri2_Computer].[Name],
       [vri2_Computer].[IsManaged],
       [dca3_AeX AC Identification].[Last Logon User]
    ORDER BY
       [dca4_MOEVersion].[MOEVersion] DESC
     
     
    Also, compare your first query with my posted query at a site like textdiff.com to learn how I combined them.


  • 3.  RE: Report - join sql querys
    Best Answer

    Posted Jan 21, 2013 09:03 PM

    Hi Mclemson

    I tried your script but it was not runnable but the tip about textdiff.com is very useful for the future, I found the following vidie which helped me use the report maker to create the report.

    http://www.symantec.com/connect/videos/symantec-management-platform-reports-creating-dropdown-menu-filter

    The resolved query is as follows

    DECLARE @v1_TrusteeScope nvarchar(311)
       SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{34DE786C-E6D1-49CC-AB40-E612D9A2094F},{44EB5117-EE86-4BD5-89BE-315A4455ED41},{511BD57A-8F96-4D94-B490-4DBC85384AD7},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{B7C587DF-6710-4FB6-A1A2-1864CD9E69B7},{BAE37EAA-E122-47D9-9EA7-77F7C27D748B}'
    DECLARE @v2_MicrosoftOfficeProfessional nvarchar(1)
       SET @v2_MicrosoftOfficeProfessional = N'%'
    SELECT
       [vri3_Computer].[Guid] AS [_ItemGuid],
       [vri3_Computer].[Name],
       [dca6_MOEVersion].[MOEVersion],
       [dca5_AeX AC Identification].[Last Logon User],
       [dca4_AddRemoveProgram].[DisplayName],
       [vri3_Computer].[IsManaged]
    FROM
       [vRM_Computer_Item] AS [vri3_Computer]
          LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [dca4_AddRemoveProgram]
             ON ([vri3_Computer].[Guid] = [dca4_AddRemoveProgram].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca5_AeX AC Identification]
             ON ([vri3_Computer].[Guid] = [dca5_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_MOEVersion] AS [dca6_MOEVersion]
             ON ([vri3_Computer].[Guid] = [dca6_MOEVersion].[_ResourceGuid])
          INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs7_Inv_AeX_AC_Client_Agent]
             ON ([vri3_Computer].[Guid] = [ajs7_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
    WHERE
       (
          (
             ([dca4_AddRemoveProgram].[DisplayName] LIKE @v2_MicrosoftOfficeProfessional)
          )
          AND
          ([vri3_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )
    GROUP BY
       [vri3_Computer].[Guid],
       [dca6_MOEVersion].[MOEVersion],
       [vri3_Computer].[Name],
       [vri3_Computer].[IsManaged],
       [dca5_AeX AC Identification].[Last Logon User],
       [dca4_AddRemoveProgram].[DisplayName]
    ORDER BY
       [dca6_MOEVersion].[MOEVersion] DESC