Implementing WSUS Server

cd "c:\Program Files\Update Services\Tools"
WsusUtil.exe configuressl wsus.leblogosd.lan

$logFolder = "c:\exploit\logs"
$maxLogs = 60

$SyncApprovals = @(

    @{"Source" = "TESTERS"  ; "Target" = "PILOT"  ; "MinDays" = 5},
    @{"Source" = "PILOT"  ; "Target" = "PROD"    ; "MinDays" = 5}
)

# ----------------- Log Files ----------------------
If (!(Test-Path $logFolder)) { New-Item -Path "$logFolder" -ItemType Directory }
$logFile = $logFolder + "\WSUS-ManageApprovals" + (Get-Date -format "yyyyMMdd-HHmmss").ToString() + ".log"
Start-Transcript -Path $logFile -Force | Out-Null

# ----------------- Connect WSUS server ------------
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null
$wsusServer       = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
$subscription     = $wsusServer.GetSubscription()
$wsusServerConfig = $wsusServer.GetConfiguration()
$targetGroups     = $wsusServer.GetComputerTargetGroups()
$updates          = $wsusServer.GetUpdates()

# ----------------- Synchronize --------------------
$subscription.StartSynchronization()

# ----------------- Manage KB ----------------------
$workingUpdates = $updates | Where { -Not $_.IsDeclined}
Foreach ($update in $workingUpdates){
  If ($update.Title -Match "Windows Malicious Software Removal Tool") { $update.Decline()                } # Decline "Windows Malicious Software Removal Tool" Update automatically
  If ($update.RequiresLicenseAgreementAcceptance)                     { $update.AcceptLicenseAgreement() } # Accept license agreement if required
}

# ----------------- Approve KB ------------------
write-host "********** Approve KB **********"
$workingUpdates = $updates | Where { -Not $_.IsDeclined}
Foreach ($update in $workingUpdates){
  $approvals = $update.GetUpdateApprovals()
  Foreach ($syncApproval in $SyncApprovals){
    $sourceGroup    = $targetGroups | Where { $_.Name                  -eq $syncApproval.Source }
    $sourceApproval = $approvals    | Where { $_.ComputerTargetGroupId -eq $sourceGroup.ID      }

        If ($($sourceApproval.Action) -eq "Install"){
      $LastChangeKB = (New-TimeSpan -start $sourceApproval.GoLiveTime -End (Get-Date)).Days
        If($LastChangeKB -ge $syncApproval.MinDays) {

            $targetGroup    = $targetGroups | Where { $_.Name                  -eq $syncApproval.Target }
            $targetApproval = $approvals    | Where { $_.ComputerTargetGroupId -eq $targetGroup.ID }
          If ($($targetApproval.Action) -ne "Install") {
                    Write-host "Enable : $($syncApproval.Target) => $($update.Title) - $LastChangeKB days"
            $update.Approve("Install", $targetGroup )  | Out-Null
          }
        }
        }
  }
}
write-host ""


# ------------ Disabled KB
write-host "********** Disable Superseded **********"
$workingUpdates = $updates | Where { -Not $_.IsDeclined} | Where { $_.IsSuperseded}
Foreach ($update in $workingUpdates){
  write-host "$($update.Title)"
  Foreach ($Supersede in $update.GetRelatedUpdates([Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesThatSupersedeThisUpdate)){
    write-host "----- Replace By : $($Supersede.Title)"
    $approvals = $Supersede.GetUpdateApprovals()

    Foreach ($approval in $approvals) {
      Foreach ($targetGroup in $targetGroups){
        If (( $targetGroup.Id -eq $approval.ComputerTargetGroupId ) -and ($($approval.Action) -eq "Install")) {
          write-host "---------- $($targetGroup.Name)"
          if ($($targetGroup.Name) -like "*GLOBAL*ODD*") {
            Write-host "---------- Decline : $($update.Title)"
            $update.Decline()

          }
        }
      }
    }
  }

}
write-host ""


# ----------------- Cleanup Server ----------------
write-host "********** Cleanup WSUS **********"
$cleanupInterface = $wsusServer.GetCleanupManager();
$cleanupScope = new-object 'Microsoft.UpdateServices.Administration.CleanupScope';
$cleanupScope.DeclineSupersededUpdates = $True;
$cleanupScope.DeclineExpiredUpdates = $True;
$cleanupScope.CleanupObsoleteComputers = $True;
$cleanupScope.CleanupObsoleteUpdates = $True;
$cleanupScope.CompressUpdates = $True;
$cleanupScope.CleanupUnneededContentFiles = $True;
$cleanupInterface.PerformCleanup($cleanupScope)
write-host ""

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 < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
    or (f.page_count > 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 < 0 BREAK;

    SELECT
        @objectname = QUOTENAME(o.name)
        , @schemaname = QUOTENAME(s.name)
    FROM
        sys.objects AS o
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE
        o.object_id = @objectid;

    SELECT
        @indexname = QUOTENAME(name)
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
    FROM
        sys.indexes
    WHERE
        object_id = @objectid AND index_id = @indexid;

    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
    ELSE IF @numrows >= 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