El Blog de Gualtrysoft

Windows 2000/2003/2008, Active Directory, VBScript, Hyper-V, PowerShell y todo aquello interesante a la hora de usar, configurar y administrar Windows Server. También tenemos longanizas…

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

Posted by urpiano en Viernes 13 \13\UTC febrero \13\UTC 2009

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'

8 comentarios to “SCCM – Consulta TSQL Para Crear Un Informe De Equipos Y Usuarios”

  1. Monster said

    ¿has probado que esto funciona correctamente? o por el contrario ¿se te ha ocurrido escribirlo para quedar bien?

    BB2+

  2. timon said

    Claro que funciona comprobado al 100%

  3. David said

    Llegué a este artículo sin conocer cual es el entorno de aplicación.

    Cómo se supone que se puede ejecutar este script? Las tablas mencionadas en el FROM son tablas de qué BD o motor?

    Gracias.

  4. KARLA V. said

    0ola urpiano…
    podrias proporcionarm un articulo q m muestr paso a paso como usar el sccm ??? xfavor, sq apnas comienzo a vr eso y necesito mas informacion

    cuidc mucho, spero rspuesta

  5. salomon said

    me sale este error

    Msg 102, Level 15, State 1, Line 181
    Incorrect syntax near ‘Colección’.
    Msg 105, Level 15, State 1, Line 181
    Unclosed quotation mark after the character string ‘
    ‘.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
A %d blogueros les gusta esto: