2020年7月20日月曜日

WSUSの管理

 Windows Update から最新のセキュリティパッチを当てたいけど、サーバからのインターネット・アクセスは遮断したいよね?とか、会社の全パソコンから Microsoft Update にパッチ取りに行くのはナンセンスだよね?とか思うと、WSUS を運用する事になります。

ところが、このメジャーなWSUSの管理に関しては、ほとんど情報が無い。何故なのか?わかりません。理解に苦しみます。
しかも、このWSUS。メンテナンスを怠るとクソ重たくなって動かなくなる始末。何回、再インストールをした事か?
ほんと、素晴らしいツールである事に間違いは無いのですが、メンテナンスがしんどくて、こいつのお守りにリソースを食われます。

ここで紹介するツールは、Microsoft のブログで紹介された WSUS管理のためのScriptで、拾い物です。
ネットで検索しても見つからないので、ここに紹介します(著作権がわからないので、掲載を取りやめるかもしれません)

トップバッターは、古い更新を拒否して、新しい更新を許可するスクリプトです。残念ながら Windows Defender 関連のアップデートが対象外となってます。どなたか、わかりましたら教えてください
# ===============================================
# Script to decline superseeded updates in WSUS.
# ===============================================
# It's recommended to run the script with the -SkipDecline switch to see how many superseded updates are in WSUS and to TAKE A BACKUP OF THE SUSDB before declining the updates.
# Parameters:

# $UpdateServer             = Specify WSUS Server Name
#$UpdateServer = "wsus.your.domain";
# $UseSSL                   = Specify whether WSUS Server is configured to use SSL
# $Port                     = Specify WSUS Server Port
#$Port = 8530;
# $SkipDecline              = Specify this to do a test run and get a summary of how many superseded updates we have
# $DeclineLastLevelOnly     = Specify whether to decline all superseded updates or only last level superseded updates

# Supersedence chain could have multiple updates. 
# For example, Update1 supersedes Update2. Update2 supersedes Update3. In this scenario, the Last Level in the supersedence chain is Update3. 
# To decline only the last level updates in the supersedence chain, specify the DeclineLastLevelOnly switch

# Usage:
# =======

# To do a test run against WSUS Server without SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530 -SkipDecline

# To do a test run against WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -SkipDecline

# To decline all superseded updates on the WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531

# To decline only Last Level superseded updates on the WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -DeclineLastLevelOnly

[CmdletBinding()]
Param(
 [Parameter(Mandatory=$True,Position=1)]
    [string] $UpdateServer,
 
 [Parameter(Mandatory=$False)]
    [switch] $UseSSL,
 
 [Parameter(Mandatory=$True, Position=2)]
    $Port,
    [switch] $SkipDecline,
    [switch] $DeclineLastLevelOnly
)

Write-Host ""

if ($SkipDecline -and $DeclineLastLevelOnly) {
    Write-Host "Using SkipDecline and DeclineLastLevelOnly switches together is not allowed."
 Write-Host ""
    return
}

$outPath = Split-Path $script:MyInvocation.MyCommand.Path
$outSupersededList = Join-Path $outPath "SupersededUpdates.csv"
$outSupersededListBackup = Join-Path $outPath "SupersededUpdatesBackup.csv"
"UpdateID, RevisionNumber, Title, KBArticle, SecurityBulletin, LastLevel" | Out-File $outSupersededList

try {
    
    if ($UseSSL) {
        Write-Host "Connecting to WSUS server $UpdateServer on Port $Port using SSL... " -NoNewLine
    } Else {
        Write-Host "Connecting to WSUS server $UpdateServer on Port $Port... " -NoNewLine
    }
    
    [reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
    #$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($UpdateServer, $UseSSL, $Port);
 $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
}
catch [System.Exception] 
{
    Write-Host "Failed to connect."
    Write-Host "Error:" $_.Exception.Message
    Write-Host "Please make sure that WSUS Admin Console is installed on this machine"
 Write-Host ""
    $wsus = $null
}

if ($wsus -eq $null) { return } 

Write-Host "Connected."

$countAllUpdates = 0
$countSupersededAll = 0
$countSupersededLastLevel = 0
$countDeclined = 0

Write-Host "Getting a list of all updates... " -NoNewLine

try {
 $allUpdates = $wsus.GetUpdates()
}

catch [System.Exception]
{
 Write-Host "Failed to get updates."
 Write-Host "Error:" $_.Exception.Message
    Write-Host "If this operation timed out, please decline the superseded updates from the WSUS Console manually."
 Write-Host ""
 return
}

Write-Host "Done"

Write-Host "Parsing the list of updates... " -NoNewLine
foreach($update in $allUpdates) {
    
    $countAllUpdates++
    
    if ($update.IsDeclined) {
        $countDeclined++
    }
    
    if (!$update.IsDeclined -and $update.IsSuperseded) {
        $countSupersededAll++
        
        if (!$update.HasSupersededUpdates) {
            $countSupersededLastLevel++
        }
        
        "$($update.Id.UpdateId.Guid), $($update.Id.RevisionNumber), $($update.Title), $($update.KnowledgeBaseArticles), $($update.SecurityBulletins), $($update.HasSupersededUpdates)" | Out-File $outSupersededList -Append       
        
    }
}

Write-Host "Done."
Write-Host "List of superseded updates: $outSupersededList"

Write-Host ""
Write-Host "Summary:"
Write-Host "========"

Write-Host "All Updates =" $countAllUpdates
Write-Host "Any except Declined =" ($countAllUpdates - $countDeclined)
Write-Host "All Superseded Updates =" $countSupersededAll
Write-Host "    Superseded Updates (Intermediate) =" ($countSupersededAll - $countSupersededLastLevel)
Write-Host "    Superseded Updates (Last Level) =" $countSupersededLastLevel
Write-Host ""

if (!$SkipDecline) {
    
    Write-Host "SkipDecline flag is set to $SkipDecline. Continuing with declining updates"
    $updatesDeclined = 0
    
    if ($DeclineLastLevelOnly) {
        Write-Host "  DeclineLastLevel is set to True. Only declining last level superseded updates." 
        
        foreach ($update in $allUpdates) {
            
            if (!$update.IsDeclined -and $update.IsSuperseded -and !$update.HasSupersededUpdates) {
                
                try 
                {
                    $update.Decline()
                    # Write-Host "Declined update $($update.Id.UpdateId.Guid)"
                    Write-Progress -Activity "Declining Updates" -Status "Declining update $($update.Id.UpdateId.Guid)" -PercentComplete (($updatesDeclined/$countSupersededLastLevel) * 100)
                    $updatesDeclined++
                }
                catch [System.Exception]
                {
                    Write-Host "Failed to decline update $($update.Id.UpdateId.Guid). Error:" $_.Exception.Message
                }            
            }
        }        
    }
    else {
        Write-Host "  DeclineLastLevel is set to False. Declining all superseded updates."
        
        foreach ($update in $allUpdates) {
            
            if (!$update.IsDeclined -and $update.IsSuperseded) {
                
                try 
                {
                    $update.Decline()
                    # Write-Host "Declined update $($update.Id.UpdateId.Guid)"
                    Write-Progress -Activity "Declining Updates" -Status "Declining update $($update.Id.UpdateId.Guid)" -PercentComplete (($updatesDeclined/$countSupersededAll) * 100)
                    $updatesDeclined++
                }
                catch [System.Exception]
                {
                    Write-Host "Failed to decline update $($update.Id.UpdateId.Guid). Error:" $_.Exception.Message
                }            
            }
        }   
        
    }
    
    Write-Host "  Declined $updatesDeclined updates."
    if ($updatesDeclined -ne 0) {
        Copy-Item -Path $outSupersededList -Destination $outSupersededListBackup -Force
  Write-Host "  Backed up list of superseded updates to $outSupersededListBackup"
    }
    
}
else {
    Write-Host "SkipDecline flag is set to $SkipDecline. Skipped declining updates"
}

Write-Host ""
Write-Host "Done"
Write-Host ""

2番手は、クリーンアップのスクリプト2種です。中身理解してません。
きれいにして
[System.Reflection.Assembly]::LoadWithPartialName('microsoft.updateservices.administration')

$wsus=new-object 'Microsoft.UpdateServices.Administration.AdminProxy'
$wsusrv=$wsus.GetUpdateServerInstance()

$cm=$Wsusrv.GetCleanupManager()
$cs=new-object 'Microsoft.UpdateServices.Administration.CleanupScope'

$cs.CleanupObsoleteUpdates = $True

$cm.PerformCleanup($cs)
圧縮するという感じでしょうか?
[System.Reflection.Assembly]::LoadWithPartialName('microsoft.updateservices.administration')

$wsus=new-object 'Microsoft.UpdateServices.Administration.AdminProxy'
$wsusrv=$wsus.GetUpdateServerInstance()

$cm=$Wsusrv.GetCleanupManager()
$cs=new-object 'Microsoft.UpdateServices.Administration.CleanupScope'

$cs.CompressUpdates = $True

$cm.PerformCleanup($cs)

おまけで、PowerShell の実行ポリシーを変更するスクリプトです。石橋を叩いて割るセキュリティには、うんざりです。このスクリプトも効果があるのか無いのか、よくわかりません。これもどっかからの拾い物だったかと…
@ECHO OFF

REM ==============================================================
ECHO Now Checking Windows x64/x86 version information......
ECHO .
ECHO ..
ECHO ...
ECHO ....
ECHO .....
ECHO.
IF EXIST %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe GOTO x64
GOTO x86

REM ==============================================================

:x64
REM ==============================================================

ECHO ** Your System was detected as x64bit Windows **
ECHO.
ECHO ----------------------------------------
Echo x86版 PowerShell の LocalMachine 実行ポリシー変更
ECHO ----------------------------------------

ECHO ** Currenct ExecutionPolisy **
%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

ECHO.
ECHO  ** Changing ExecutionPolisy **
%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe -Command "Set-ExecutionPolicy RemoteSigned -Scope LocalMachine -Confirm"

ECHO.
ECHO  ** Changed After ExecutionPolisy **
%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

ECHO ----------------------------------------
Echo x64版 PowerShell の LocalMachine 実行ポリシー変更
ECHO ----------------------------------------

ECHO  ** Currenct ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

ECHO.
ECHO  ** Changing ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Set-ExecutionPolicy RemoteSigned -Scope LocalMachine -Confirm"

ECHO.
ECHO  ** Changed After ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

GOTO EOF

REM ==============================================================

:x86
REM ==============================================================

ECHO ** Your System was detected as x86bit Windows **
ECHO.
ECHO ----------------------------------------
Echo x86版 PowerShell の LocalMachine 実行ポリシー変更
ECHO ----------------------------------------

ECHO  ** Currenct ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

ECHO.
ECHO  ** Changing ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Set-ExecutionPolicy RemoteSigned -Scope LocalMachine -Confirm"

ECHO.
ECHO  ** Changed After ExecutionPolisy **
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy -list"

GOTO EOF

REM ==============================================================

:EOF
PAUSE

最後は、SQL Server のメンテナンス用SQLです
やる必要があるのかどうかさえ、理解していません。製品内容が複雑で専門的すぎて、DBには精通している方だと思う自分にも何をしているのか理解できない代物です。データベースが重たすぎてファッキュン!って時に神頼みで実行します。
-- Microsoft SQL Server Management を開く
-- クエリーを作成、これを貼り付ける
-- データベースに「SUSDB」を選択し、クエリを実行する
-- http://www.sqlmusings.com
-- Ensure a USE  statement has been executed first.
SET NOCOUNT ON

-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online 
-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
 
-- =======================================================
-- || Configuration variables:
-- || - 10 is an arbitrary decision point at which to
-- || reorganize indexes.
-- || - 30 is an arbitrary decision point at which to
-- || switch from reorganizing, to rebuilding.
-- || - 0 is the default fill factor. Set this to a
-- || a value from 1 to 99, if needed.
-- =======================================================
DECLARE @reorg_frag_thresh   float  SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float  SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 80
DECLARE @report_only         bit   SET @report_only         = 1

-- added (DS) : page_count_thresh is used to check how many pages the current table uses
DECLARE @page_count_thresh  smallint SET @page_count_thresh   = 1000
 
-- Variables required for processing.
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @partitioncount bigint
DECLARE @schemaname     nvarchar(130) 
DECLARE @objectname     nvarchar(130) 
DECLARE @indexname      nvarchar(130) 
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @page_count     int
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float,
          page_count   int
                        )
 
-- Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats function and
-- convert object and index IDs to names.
INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
 [page_count]       AS page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
 AND
 page_count > @page_count_thresh
 AND
    index_id > 0
 
 
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var
 
-- Open the cursor.
OPEN partitions
 
-- Loop through the partitions.
WHILE (1=1) BEGIN
    FETCH NEXT
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count
 
    IF @@FETCH_STATUS < 0 BREAK
 
    SELECT
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
    FROM
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas as s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid
 
    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    -- Build the required statement dynamically based on options and index stats.
    SET @intentions =
        @schemaname + N'.' +
        @objectname + N'.' +
        @indexname + N':' + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
 
    IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' + @objectname +
            N' REORGANIZE; ' + 
            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
            N' ' + @indexname + ';'

    END
    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' +     @objectname +
            N' REBUILD'
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END
 
    -- Execute determined operation, or report intentions
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions     
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END
 PRINT @command

END
 
-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
 
GO