SCCM – Consulta TSQL Para Crear Un Informe De Equipos Y Usuarios

Esta consulta TSQL nos permite listar todos los equipos, algunas de sus características, el último usuario logado y algunas de sus propiedades. Tiene una peculiaridad del entorno en el que lo he desarrollado y es que creé una serie de colecciones basadas en determinados conceptos según los requerimientos que se necesitaban cumplir y englobé el conjunto dentro de otra colección "raíz"; la consulta busca los equipos sólo en las subcolecciones que están dentro de la colección "raíz" y se une con otra consulta con aquellos equipos que aparezcan en AllSystems y no estén en las colecciones creadas. Para evitar que aparezcan duplicados equipos, en la primera subconsulta, debemos incluir en la claúsula WHERE que no se incluyan los equipos que estén en la colección "raíz" y que sólo se busquen aquellas cuyo identificador empiece por el identificador del sitio CCM y se excluya la colección "raíz"; en el ejemplo el identificador de las colecciones creadas empieza por TIA, y el identificador de la raíz es TIA0012A. Así pues, en la primera subconsulta esta condición WHERE es:

WHERE
      FCM.CollectionID LIKE 'TIA%'
  AND
      FCM.CollectionID <> 'TIA0012A'

En la segunda subconsulta, miramos únicamente en la colección AllSystems, identificada por SMS00001, pero además tenemos que excluir aquellos que sean devueltos por la primera subconsulta, por lo que en la claúsula WHERE incluiremos, en un NOT IN, otra subconsulta con las condiciones de la primera de las subconsultas

WHERE
      FCM.CollectionID = 'SMS00001'
  AND
      SYS.ResourceID NOT IN (SELECT DISTINCT
                                    SYS.ResourceID
                             FROM
                                    v_FullCollectionMembership FCM
                               JOIN
                                    v_R_System SYS
                                 ON
                                    FCM.ResourceID=SYS.ResourceID
                             WHERE
                                    FCM.CollectionID LIKE 'TIA%'
                                AND
                                    FCM.CollectionID  <> 'TIA0012A'
                              )

Esta es la consulta al completo

SELECT DISTINCT
      SYS.Name0 'Equipo',
      ISNULL(CS.Manufacturer0,'') Marca,
      ISNULL(CS.Model0,0) Modelo, 
      ISNULL(PCB.SerialNumber0,0) 'Nº Serie',
      COL.Name 'Colección',
      ISNULL(SYS.User_Name0,'') 'Usuario',
      ISNULL(USR.Full_User_Name0,'') 'Nombre',
      ISNULL(USR.company0,'') 'Empresa',
      ISNULL(USR.department0,'') 'Departamento',
      ISNULL((SELECT
              'Habilitado'
          FROM
              v_R_User U
          WHERE
              NOT((U.User_Account_Control0 & 2) = 2)
            AND
              U.User_Name0 = USR.User_Name0
          UNION
          SELECT 
              'Deshabilitado'
          FROM
              v_R_User U
          WHERE
              (U.User_Account_Control0 & 2) = 2
            AND
              U.User_Name0 = USR.User_Name0),'') 'Estado',
      ISNULL(ROUND(CONVERT(FLOAT,FDISK.Size0) /1024, 2),'') 'HD Tamaño (GB)',
      ISNULL(ROUND(CONVERT(FLOAT,LDISK.FreeSpace0) / 1024, 2),'') 'HD Libre (GB)',
      ISNULL(LTRIM(PROC1.Name0),'') AS 'Procesador',
      ISNULL(ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000,'') 'Velocidad (GHz)', 
      ISNULL(ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0),'') 'RAM (KB)', 
      ISNULL(OPSYS.Caption0,'') 'Sistema Operativo',
      ISNULL(CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)),'') 'Versión',
      ISNULL(OPSYS.CSDVersion0,'') 'Service Pack'
FROM
      v_FullCollectionMembership FCM
  JOIN
      v_R_System SYS
    ON
      FCM.ResourceID=SYS.ResourceID
  JOIN
      v_Collection COL
    ON 
      FCM.CollectionID = COL.CollectionID
  LEFT OUTER JOIN
      v_R_User USR
    ON
      SYS.User_Name0 = USR.User_Name0 
  LEFT OUTER JOIN
      v_GS_PROCESSOR PROC1
    ON
      SYS.ResourceID = PROC1.ResourceID
  LEFT OUTER JOIN
      v_GS_X86_PC_MEMORY MEM
    ON
      SYS.ResourceID = MEM.ResourceID
  LEFT OUTER JOIN
      v_GS_OPERATING_SYSTEM OPSYS
    ON
      SYS.ResourceID = OPSYS.ResourceID
  LEFT OUTER JOIN
      v_GS_LOGICAL_DISK LDISK
    ON
        SYS.ResourceID = LDISK.ResourceID 
      AND
        LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
  LEFT OUTER JOIN
      v_GS_DISK FDISK
    ON
      SYS.ResourceID = FDISK.ResourceID
  LEFT OUTER JOIN
      v_GS_COMPUTER_SYSTEM CS
    ON 
      SYS.ResourceID = CS.ResourceID 
  LEFT OUTER JOIN
      v_GS_PC_BIOS PCB 
    ON
			SYS.ResourceID = PCB.ResourceID 
WHERE
      FCM.CollectionID LIKE 'TIA%'
  AND
      FCM.CollectionID <> 'TIA0012A'
UNION
SELECT DISTINCT
      SYS.Name0,
      ISNULL(CS.Manufacturer0,0),
      ISNULL(CS.Model0,0), 
      ISNULL(PCB.SerialNumber0,0),
      COL.Name,
      ISNULL(SYS.User_Name0,''),
      ISNULL(USR.Full_User_Name0,''),
      ISNULL(USR.company0,''),
      ISNULL(USR.department0,''),
      ISNULL((SELECT
              'Habilitado'
          FROM
              v_R_User U
          WHERE
              NOT((U.User_Account_Control0 & 2) = 2)
            AND
              U.User_Name0 = USR.User_Name0
          UNION
          SELECT 
              'Deshabilitado'
          FROM
              v_R_User U
          WHERE
              (U.User_Account_Control0 & 2) = 2
            AND
              U.User_Name0 = USR.User_Name0),''),
      ISNULL(ROUND(CONVERT(FLOAT,FDISK.Size0) /1024, 2),''),
      ISNULL(ROUND(CONVERT(FLOAT,LDISK.FreeSpace0) / 1024, 2),''),
      ISNULL(LTRIM(PROC1.Name0),'') 'Procesador',
      ISNULL(ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000,'') AS 'Velocidad (GHz)', 
      ISNULL(ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0),''), 
      ISNULL(OPSYS.Caption0,''),
      ISNULL(CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)),''),
      ISNULL(OPSYS.CSDVersion0,'') 'SP'
FROM
      v_FullCollectionMembership FCM
  JOIN
      v_R_System SYS
    ON
      FCM.ResourceID=SYS.ResourceID
  JOIN
      v_Collection COL
    ON 
      FCM.CollectionID = COL.CollectionID 
  LEFT OUTER JOIN
      v_R_User USR
    ON
      SYS.User_Name0 = USR.User_Name0 
  LEFT OUTER JOIN
      v_GS_PROCESSOR PROC1
    ON
      SYS.ResourceID = PROC1.ResourceID
  LEFT OUTER JOIN
      v_GS_X86_PC_MEMORY MEM
    ON
      SYS.ResourceID = MEM.ResourceID
  LEFT OUTER JOIN
      v_GS_OPERATING_SYSTEM OPSYS
    ON
      SYS.ResourceID = OPSYS.ResourceID
  LEFT OUTER JOIN
      v_GS_LOGICAL_DISK LDISK
    ON
        SYS.ResourceID = LDISK.ResourceID
      AND
        LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
  LEFT OUTER JOIN
      v_GS_DISK FDISK
    ON
      SYS.ResourceID = FDISK.ResourceID
  LEFT OUTER JOIN
      v_GS_COMPUTER_SYSTEM CS
    ON 
      SYS.ResourceID = CS.ResourceID 
  LEFT OUTER JOIN
      v_GS_PC_BIOS PCB 
    ON
			SYS.ResourceID = PCB.ResourceID 
WHERE
      FCM.CollectionID = 'SMS00001'
  AND
      SYS.ResourceID NOT IN (SELECT DISTINCT
                                    SYS.ResourceID
                             FROM
                                    v_FullCollectionMembership FCM
                               JOIN
                                    v_R_System SYS
                                 ON
                                    FCM.ResourceID=SYS.ResourceID
                             WHERE
                                    FCM.CollectionID LIKE 'TIA%'
                                AND
                                    FCM.CollectionID <> 'TIA0012A1
                             )
ORDER BY
      'Colección','Equipo'