Comparar actualizaciones entre dos servidores

En forma predeterminada, el SCCM 2007 trae un reporte, el 97, que compara el inventario de software de dos computadoras. Esto puede ser muy útil en algunos casos, pero no lo es si lo que nos interesa saber es que actualizaciones fueron instaladas en un equipo y en otro no.
La idea de este reporte es, justamente, comparar las actualizaciones en dos computadoras, y devolvernos aquellas que están instaladas en uno de esos equipos y en el otro no.

Es una consulta compleja, pero que puede entenderse con un poco de análisis, y que fácilmente puede modificarse para cualquier otra comparación de inventario que les interese.

La query del reporte es la siguiente:

select
(case when inv1.Description0 is not null then @pc1 else @pc2 end) as Servidor,
  (case when inv1.Description0 is not null then inv1.User_Domain0 else inv2.User_Domain0 end) as Dominio,
  (case when inv1.Description0 is not null then inv1.User_Name0 else inv2.User_Name0 end) as Usuario,
  (case when inv1.Description0 is not null then inv1.Operating_System_Name_and0 else inv2.Operating_System_Name_and0 end) as SO,
  (case when inv1.Description0 is not null then inv1.InstalledOn else inv2.InstalledOn end) as Fecha,
  (case when inv1.Description0 is not null then inv1.InstalledBy0 else inv2.InstalledBy0 end) as [Instalado por],
  (case when inv1.Description0 is not null then inv1.ServicePackInEffect0 else inv2.ServicePackInEffect0 end) as [Service Pack],
  (case when inv1.Description0 is not null then inv1.Caption0 else inv2.Caption0 end) as Identificador,
  (case when inv1.Description0 is not null then inv1.Description0 else inv2.Description0 end) as Descripción
  from
(
Select distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0,
dbo.ConvertirFecha(qfe.InstalledOn0) as InstalledOn,
qfe.InstalledBy0, qfe.ServicePackInEffect0, qfe.Caption0, qfe.Description0
FROM v_R_System sys
JOIN v_GS_QUICK_FIX_ENGINEERING qfe on sys.ResourceID=qfe.ResourceID
WHERE sys.Netbios_Name0=@pc1
union
Select distinct sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0,
 sys.Operating_System_Name_and0,
dbo.ConvertirFecha(PSE.AgentInstallDate) as InstalledOn,
'NA' as InstalledBy0,
 'NA' as ServicePackInEffect0,
 PSE.ID as Caption0,
 PSE.Title as Description0
FROM v_R_System sys
JOIN v_GS_PatchStatusEx PSE on sys.ResourceID=PSE.ResourceID
WHERE sys.Netbios_Name0=@pc1
) as inv1
full outer join
(
Select distinct sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0,
dbo.ConvertirFecha(qfe.InstalledOn0) as InstalledOn,
qfe.InstalledBy0, qfe.ServicePackInEffect0, qfe.Caption0, qfe.Description0
FROM v_R_System sys
JOIN v_GS_QUICK_FIX_ENGINEERING qfe on sys.ResourceID=qfe.ResourceID
WHERE sys.Netbios_Name0=@pc2
union
Select distinct sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0,
 sys.Operating_System_Name_and0,
dbo.ConvertirFecha(PSE.AgentInstallDate) as InstalledOn,
'NA' as InstalledBy0,
 'NA' as ServicePackInEffect0,
 PSE.ID as Caption0,
 PSE.Title as Description0
FROM v_R_System sys
JOIN v_GS_PatchStatusEx PSE on sys.ResourceID=PSE.ResourceID
WHERE sys.Netbios_Name0=@pc2
) as inv2
on inv1.Description0=inv2.Description0
where (inv1.Description0 is NULL) or (inv2.Description0 is NULL)
order by Servidor

Como pueden ver, es bastante extensa, pero no hay que asustarse. Es un gigantesco JOIN de dos "Select" que ya habíamos usado antes. El join es un full outer, es decir compara y obtiene solo los registros que no están en ninguna de las dos subconsultas. Además, utilizando un CASE para cada columna, mostramos solo los valores del equipo que tiene datos, en este caso, el equipo que tiene instalado el update que no se encuentra en el otro equipo.

Los parámetros de la consulta son el nombres de las PCs a comparar (@pc1 y @pc2). Para ambos podemos utilizar esta query:

begin

 if (@__filterwildcard = '')
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0
 else
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1
  and SYS.Netbios_Name0 like @__filterwildcard
  ORDER By SYS.Netbios_Name0
end

En las subconsultas para obtener las actualizaciones instaladas en cualquier servidor utilice una vez más la función ConvertirFecha, cuya query de creación ya dí en el post anterior.

El resultado del reporte debería verse más o menos así:

Espero que les sirva.
Saludos
Franco

Actualizaciones instaladas por fecha

Esta es quizás una de las consultas más comunes para quienes utilizan SCCM para el deploy de updates ¿cuándo se instalo X actualización?.
SCCM no está pensado para realizar este tipo de tareas de auditoria o seguimiento. Le alcanza con saber que todas las actualizaciones necesarias están o no instaladas. Pero en muchas organizaciones el proceso de deploy debe ser controlado rigurosamente, y los conflictos con determinados updates (sobre todo de aplicaciones de terceros) hace necesario tener el detalle de los procesos de deploy.

Lamentablemente, Windows ha ido cambiando su criterio para el registro de la instalación de updates según versión del sistema operativo y arquitectura, lo que hace muy complejo realizar este seguimiento de manera precisa y totalmente acertada. Pero podemos tener una aproximación bastante cercana (y chapucera) utilizando este reporte.

if len(@mes)<2 set @mes='0'+@mes
if len(@dia)<2 set @dia='0'+@dia

Select distinct sys.Netbios_Name0,
 sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0,
dbo.ConvertirFecha(qfe.InstalledOn0) as InstalledOn,
qfe.InstalledBy0, qfe.ServicePackInEffect0, qfe.Caption0, qfe.Description0
FROM v_R_System sys
JOIN v_GS_QUICK_FIX_ENGINEERING qfe on sys.ResourceID=qfe.ResourceID
WHERE dbo.ConvertirFecha(qfe.InstalledOn0)=@anio + @mes + @dia

union

Select distinct sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0,
 sys.Operating_System_Name_and0,
dbo.ConvertirFecha(PSE.AgentInstallDate) as InstalledOn,
'NA' as InstalledBy0,
 'NA' as ServicePackInEffect0,
 PSE.ID as Caption0,
 PSE.Title as Description0
FROM v_R_System sys
JOIN v_GS_PatchStatusEx PSE on sys.ResourceID=PSE.ResourceID
WHERE dbo.ConvertirFecha(PSE.AgentInstallDate)=@anio + @mes + @dia

En este caso, los prompts no requieren query alguna, pero si quieren pueden armarse una sencilla consulta en SQL que le de al usuario los valores de día mes y año que puede utilizar.

Los que hayan revisado con detenimiento la query, habrán notado que hace uso de una función llamada "ConvertirFecha". Es necesario crear esta función en su servidor SQL y en la base del SCCM para que el reporte funcione.

CREATE FUNCTION [dbo].[ConvertirFecha] (@Hexa as nvarchar(max))

RETURNS nvarchar(50)
AS
BEGIN
 DECLARE @Low bigint
 Declare @HighHex varchar(20)
 Declare @LowHex varchar(20)
 DECLARE @High bigint
 Declare @Int64 bigint
 Declare @Retorno nvarchar(50)
 Declare @iSeconds bigint
 Declare @Potencia bigint
 Declare @Dias int
 Declare @Fecha datetime
 
 Declare @Query nvarchar(100)
    Declare @Parameters nvarchar(50)
    Declare @ReturnValue Int
    
    Declare @Anio varchar(4)
 Declare @Mes varchar(2)
 Declare @Dia varchar(2)
 
if len(@Hexa)=16 
Begin

 
 set @HighHex = Substring(@Hexa,1,8) 
 set @High = dbo.ConvertFromBase(@HighHex,16)
 
 set @LowHex = Substring(@Hexa,9,16)
 set @Low = dbo.ConvertFromBase(@LowHex,16)
 
 
 set @Potencia = Power(2.0,32)
 
 set @int64 = @high * @Potencia + @Low
 Set @iSeconds = Floor(@int64 / 10000000)
 Set @Dias = Floor(@iSeconds/86400)-149019
 
 Set @Fecha = DateAdd(d, @Dias, '2009-01-01')
 Set @anio=DatePart(YYYY,@Fecha)
 Set @mes=DatePart(mm,@Fecha)
 Set @dia=DatePart(dd,@Fecha)
 if len(@mes)<2 set @mes='0' + @mes
 if len(@dia)<2 set @dia='0' + @dia

 
 Set @Retorno=@Anio + @mes + @dia
End
Else
Begin
 if isdate(@Hexa)=1
  Begin
   Set @Fecha=cast(@Hexa as DateTime)
   Set @anio=DatePart(YYYY,@Fecha)
   Set @mes=DatePart(mm,@Fecha)
   Set @dia=DatePart(dd,@Fecha)
   if len(@mes)<2 set @mes='0' + @mes
   if len(@dia)<2 set @dia='0' + @dia
   Set @Retorno=@Anio + @mes + @dia  
  End
 Else
  Set @Retorno=NULL
 

End


Return (@Retorno)
END

Con esa query crean la función en su base de SCCM.

Además, necesitan una segunda función (utilizada por la anterior) que se denomina ConvertFromBase. Aquí la query para crearla:

CREATE FUNCTION [dbo].[ConvertFromBase] 

 ( 

     @value AS VARCHAR(MAX), 

     @base AS BIGINT 

 ) RETURNS BIGINT AS BEGIN 

  

     -- just some variables 

     DECLARE @characters CHAR(36), 

             @result BIGINT, 

             @index SMALLINT; 

  

     -- initialize our charater set, our result, and the index 

     SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz', 

            @result = 0, 

            @index = 0; 

  

     -- make sure we can make the base conversion.  there can't 

     -- be a base 1, but you could support greater than base 36 

     -- if you add characters to the @charater string 

     IF @base < 2 OR @base > 36 RETURN NULL; 

  

     -- while we have characters to convert, convert them and 

     -- prepend them to the result.  we start on the far right 

     -- and move to the left until we run out of digits.  the 

     -- conversion is the standard (base ^ index) * digit 

  WHILE @index < LEN(@value) 

         SELECT @result = @result + POWER(@base, @index) *  

                          (CHARINDEX 

                             (SUBSTRING(@value, LEN(@value) - @index, 1) 

                             , @characters) - 1 

                          ), 

                @index = @index + 1; 

  

     -- return the result 

     RETURN @result; 

  

 END

La primera página del reporte debería verse más o menos así:

Y el resultado debería ser parecido al siguiente:

No es difícil modificar el reporte para filtrar por servidor, colección o cualquier otro parámetro típico de los reportes de SCCM.

Saludos
Franco

Obtener todas las actualizaciones requeridas por los equipos en una colección

Otro reporte personalizado para cubrir una necesidad personal. En este caso, necesitaba saber que actualizaciones son requeridas por todos los servidores dentro de una colección. Combinando los resultados de este reporte con datos como ventanas de mantenimiento y deployments es fácil saber que se va a instalar, cuando y dónde.

La query del reporte es la siguiente:

SELECT Name
      ,[Title]
      ,[Description]
      ,[InfoURL]
  FROM [SMS_SVR].[dbo].[v_UpdateComplianceStatus]
 , [SMS_SVR].[dbo].[v_UpdateInfo]
 , [SMS_SVR].[dbo].[v_FullCollectionMembership]
  Where Status=2
  and v_UpdateComplianceStatus.CI_ID=v_UpdateInfo.CI_ID
  and v_UpdateComplianceStatus.ResourceID=v_FullCollectionMembership.ResourceID
  and CollectionID=@ID
  order by Name

Y la query para el parámetro CollectionID es la siguiente:

begin
  if (@__filterwildcard = '')
   SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
  else
   SELECT DISTINCT CollectionID, Name FROM v_Collection
   WHERE CollectionID like @__filterwildcard
   ORDER BY Name
 end

El resultado será algo como esto:

Espero que les sirva.
Saludos.