WSUS con SQL SERVER

Cuando instale WSUS conectandose a un servidor SQL, queria saber un poco más sobre la base y que informacion almecenaba en ésta.

Asi que nuevamente les traido 3 Querys que uso y espero les sean de utilidad; la verdad no recuerdo donde estan publicados, pero no son mios :)


1) Hacer mantenimiento a la base de datos
-------------------------------------------------------------------------
USE SUSDB;
GO
SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int
)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(),
121)
INSERT @work_to_do
SELECT
f.object_id
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'SAMPLED') AS f
WHERE
(f.avg_page_space_used_in_percent <> 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar
(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(),
121)

SELECT @numpages = sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and
fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id =
ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.
OPEN curIndexes

-- Loop through the indexes
WHILE (1=1)
BEGIN
FETCH NEXT FROM curIndexes
INTO @objectid, @indexid, @density, @fragmentation, @numrows;
IF @@FETCH_STATUS < objectname =" QUOTENAME(o.name)" schemaname =" QUOTENAME(s.name)" schema_id =" o.schema_id" object_id =" @objectid;" indexname =" QUOTENAME(name)" fillfactorset =" CASE" object_id =" @objectid" index_id =" @indexid;" fillfactorset =" 1)" command =" N'ALTER">= 5000 AND @fillfactorset = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +
@schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
ELSE
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +
@schemaname + N'.' + @objectname + N' REBUILD';
PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' +
@command;
EXEC (@command);
PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END

-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;


IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
PRINT 'Estimated number of pages in fragmented indexes: ' + cast
(@numpages as nvarchar(20))
SELECT @numpages = @numpages - sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and
fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id =
ps.object_id and i.index_id = ps.index_id

PRINT 'Estimated number of pages freed: ' + cast(@numpages as
nvarchar(20))
END
GO


--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO-------------------------------------------------------------------------

2) CREANDO UNA VISTA PARA EL REPORTE
-------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[aWsusDetalle]

AS

select
WSUSComputers.FullDomainName as [Nombre Maquina],
WSUSComputers.LastSyncTime as [Ultima Sincronización],
WSUSComputers.LastReportedStatusTime as [Ultima Conexión],
WSUSSumary.Unknown as Desconocido,
WSUSSumary.NotInstalled as [No Instaladas],
WSUSSumary.Downloaded as [Necesarias],
WSUSSumary.Installed as [Instaladas],
WSUSSumary.Failed as [Errores],
WSUSSumary.InstalledPendingReboot as [Pendientes De Reinicio]
from
tbComputerTarget WSUSComputers

inner join

tbComputerSummaryForMicrosoftUpdates WSUSSumary
on
WSUSComputers.TargetID= WSUSSumary.TargetID
-------------------------------------------------------------------------

3) El Reporte Gracias a la Vista creada anteriormente(2)

-------------------------------------------------------------------------
Select 'Maquinas Administradas' as Contador, count(*) as Valor
from aWsusDetalle

Union All

select'Desconocido/ No Aplicable' as Contador,count(*) as Valor
from aWsusDetalle
Where Desconocido > 0

union all

select 'No Instaladas' as Contador, count(*) as Valor
from aWsusDetalle
where [No Instaladas]>0

union all

select 'Necesarias' as Contador, count(*) as Valor
from aWsusDetalle
where Necesarias>0

union all

select 'Con Errores' as Contador, count(*) as Valor
from aWsusDetalle
where Errores >0

union all

select 'Pendientes De Reinicio' as Contador, count(*) as Valor
from aWsusDetalle
where [Pendientes De Reinicio] >0
-------------------------------------------------------------------------
Saludos!