locked
Merging SCCM Reports RRS feed

  • Question

  • Hi -

    Can someone help me merge the following reports? I need to make one report that displays all Computers with a specific product called "NextThink Collector" installed & that shows the last user & time logged in. Also, need a column that displays which OU the computer resides in.

    Report 1

    SELECT 
     dbo.v_R_System.Netbios_Name0 AS [Computer Name],
     dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number],
     dbo.v_R_System.User_Name0 [Last Logon User Name],
     dbo.v_R_System.Last_Logon_Timestamp0 [Last logged in time]
    FROM
     dbo.v_R_System
    LEFT JOIN
     dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID

    Report 2

    SELECT
     sys.name0 as 'Computer Name',
     MAX(cs.system_ou_name0) as 'OU Path'
    FROM
     v_R_System sys
    LEFT JOIN
     dbo.v_RA_System_SystemOUName
     cs on sys.ResourceID = cs.ResourceID
    GROUP BY
     sys.name0
    ORDER BY MAX
     (cs.system_ou_name0),
     sys.name0

    Report 3

    SELECT
     c.Name0,
     a.DisplayName0,
     b.CollectionID
    FROM
     v_GS_ADD_REMOVE_PROGRAMS a,
     v_FullCollectionMembership b,
     v_R_System c
    WHERE
     a.ResourceID = b.ResourceID
    AND
     a.ResourceID = c.ResourceID
    AND
     b.CollectionID=ScopeID_C0525CD761D74B2E-B8E5-D9BCE1C23168/Application_3bf2a8f2-6735-4b54b8e3fa945f34a33b/10
    AND
     (a.DisplayName0 ='NextThink' OR a.DisplayName0 = 'NextThink Collector')
    GROUP BY
     c.Name0,
     a.DisplayName0,
     b.CollectionID
    ORDER BY
     a.DisplayName0

    Thanks, littlevader

    Friday, February 21, 2020 12:15 AM

All replies