ところが、このメジャーな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 ""
２番手は、クリーンアップのスクリプト２種です。中身理解してません。
きれいにして
[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 USEstatement 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
1 件のコメント:
コメントを投稿