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