以前のはWSUS 3.0 SP2では動いていたが、接続時に使う文字列が、WSUS 4.0? (Windows 2012で動くWSUS)では、変更になったのでスクリプトを修正。
Windows Internal Database を使用している場合、WSUS 3.2 の場合、クエリは \\.\pipe\mssql$microsoft##ssee\sql\query、Windows Server 2012 の WSUS の場合、\\.\pipe\Microsoft##WID\tsql\query になる。
# tested with perl2exe-16.00-Win and strawberry-perl-5.16.3.1-32bit
# install / uncompress strawberry perl
# path=%PATH%;;C:\strawberry\perl\bin;C:\strawberry\c\bin
# cpan
# > install Win32::OLE
# > exit
# perl2exe.exe WSUS_report.pl
#perl2exe_include "unicore/Heavy.pl";
use overloading; # this is for perl2exe
use strict;
use utf8;
use POSIX;
#print setlocale(LC_ALL);
setlocale(LC_ALL, 'Japanese_Japan.932');
#print setlocale(LC_ALL);
sub get_fields($){
my $type = shift @_;
my @out;
@out = split /,/, "updateID,LegacyName,Title,Description,Category,KBArticleID,SecurityBulletinID,MoreInfoURL,RevisionID,SupersedeUpdateCount,SupersededUpdateCount";
return \@out if $type eq "all_patches";
@out = split /,/, "EventInstanceID,EventID,TimeAtTarget,TimeAtServer,StateID,SeverityID,MessageTemplate,ReplacementStringsXml,TargetID,ComputerID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,updateID,Title,Category,KBArticleID,SecurityBulletinID";
return \@out if $type eq "error_patches";
@out = split /,/, "TargetGroupID,TargetID,IsExplicitMember,TargetID,ComputerID,SID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,IsRegistered,LastInventoryTime,LastNameChangeTime,EffectiveLastDetectionTime,ParentServerTargetID,LastSyncResult,TargetGroupTypeID,Name,Description,TargetGroupID,OrderValue,IsBuiltin,ParentGroupID,GroupPriority";
return \@out if $type eq "host_groups";
@out = split /,/, "TargetID,ComputerID,SID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,IsRegistered,LastInventoryTime,LastNameChangeTime,EffectiveLastDetectionTime,ParentServerTargetID,LastSyncResult,TargetID,OSMajorVersion,OSMinorVersion,OSBuildNumber,OSServicePackMajorNumber,OSServicePackMinorNumber,OSLocale,ComputerMake,ComputerModel,BiosVersion,BiosName,BiosReleaseDate,ProcessorArchitecture,LastStatusRollupTime,LastReceivedStatusRollupNumber,LastSentStatusRollupNumber,SamplingValue,CreatedTime,SuiteMask,OldProductType,NewProductType,SystemMetrics,ClientVersion,TargetGroupMembershipChanged,OSFamily,OSDescription";
return \@out if $type eq "hosts";
@out = split /,/, "targetID,FullDomainName,IPAddress,category,Title,SummarizationState,LastReportedStatusTime,SecurityBulletinID,KBArticleID,updateID,RevisionID,SupersedeUpdateCount,SupersededUpdateCount";
return \@out if $type eq "needed_patches";
@out = split /,/, "UpdateApprovalId,UpdateID,ComputerTargetGroupId,Title,GroupName,Action,Deadline,CreationDate,AdministratorName,IsOptional,IsStale";
return \@out if $type eq "patch_approvals";
return undef;
}
sub get_sql($){
my $type = shift @_;
return qq{
SELECT
RevisionID,
COUNT(SupersededUpdateID) AS counter
INTO #X1
FROM
SUSDB.dbo.tbRevisionSupersedesUpdate
GROUP BY
RevisionID;
SELECT
COUNT(RevisionID) AS counter,
SupersededUpdateID
INTO #Y1
FROM
SUSDB.dbo.tbRevisionSupersedesUpdate
GROUP BY
SupersededUpdateID;
SELECT
B.updateID,
B.LegacyName,
A.Title,
A.Description,
D.Title AS Category,
F.KBArticleID,
G.SecurityBulletinID,
H.MoreInfoURL,
A.RevisionID,
#X1.counter AS SupersedeUpdateCount,
#Y1.counter AS SupersededUpdateCount
FROM
SUSDB.dbo.tbPreComputedLocalizedProperty A
INNER JOIN
SUSDB.dbo.tbUpdate B ON A.UpdateID = B.UpdateID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory C ON A.RevisionID = C.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty D ON C.CategoryID = D.CategoryID
INNER JOIN
SUSDB.dbo.tbRevision E on E.localUpdateID = B.localUpdateID
LEFT JOIN
SUSDB.dbo.tbKBArticleForRevision F ON F.RevisionID = E.RevisionID
LEFT JOIN
SUSDB.dbo.tbSecurityBulletinForRevision G ON G.RevisionID = E.RevisionID
INNER JOIN
SUSDB.dbo.tbMoreInfoURLForRevision H ON H.RevisionID = E.RevisionID
LEFT JOIN
#X1 ON A.RevisionID = #X1.RevisionID
LEFT JOIN
#Y1 ON B.updateID = #Y1.SupersededUpdateID
WHERE
A.ShortLanguage = 'en' AND
D.CategoryType = 'UpdateClassification' AND
D.ShortLanguage = 'en' AND
E.islatestRevision = 'True' AND
H.ShortLanguage = 'en'
ORDER BY
G.SecurityBulletinID
} if ($type eq 'all_patches');
return qq{
SELECT
B.updateID,
B.LegacyName,
A.Title,
A.Description,
D.Title AS Category,
F.KBArticleID,
G.SecurityBulletinID,
H.MoreInfoURL,
A.RevisionID
INTO #X1
FROM
SUSDB.dbo.tbPreComputedLocalizedProperty A
INNER JOIN
SUSDB.dbo.tbUpdate B ON A.UpdateID = B.UpdateID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory C ON A.RevisionID = C.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty D ON C.CategoryID = D.CategoryID
INNER JOIN
SUSDB.dbo.tbRevision E on E.localUpdateID = B.localUpdateID
LEFT JOIN
SUSDB.dbo.tbKBArticleForRevision F ON F.RevisionID = E.RevisionID
LEFT JOIN
SUSDB.dbo.tbSecurityBulletinForRevision G ON G.RevisionID = E.RevisionID
INNER JOIN
SUSDB.dbo.tbMoreInfoURLForRevision H ON H.RevisionID = E.RevisionID
WHERE
A.ShortLanguage = 'en' AND
D.CategoryType = 'UpdateClassification' AND
D.ShortLanguage = 'en' AND
E.islatestRevision = 'True' AND
H.ShortLanguage = 'en'
ORDER BY
G.SecurityBulletinID
SELECT
distinct B.ComputerID,
D.updateID
INTO #X2
FROM
SUSDB.dbo.tbUpdateStatusPerComputer A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbUpdate D ON A.LocalUpdateID = D.LocalUpdateID
WHERE
A.SummarizationState = 5
SELECT
E.EventInstanceID,
E.EventID,
E.TimeAtTarget,
E.TimeAtServer,
E.StateID,
E.SeverityID,
E.MessageTemplate,
E.ReplacementStringsXml,
C.TargetID,
C.ComputerID,
C.LastSyncTime,
C.LastReportedStatusTime,
C.LastReportedRebootTime,
C.IPAddress,
C.FullDomainName,
U.updateID,
U.Title,
U.Category,
U.KBArticleID,
U.SecurityBulletinID
FROM
SUSDB.dbo.vwEventHistory E
INNER JOIN
SUSDB.dbo.tbComputerTarget C ON E.ComputerID = C.ComputerID
INNER JOIN
#X1 U ON E.updateID = U.updateID
WHERE
eventID in (161, 163, 182, 185, 186, 192, 195, 198, 364) AND
E.ComputerID IN (SELECT ComputerID FROM #X2) AND
E.updateID IN (SELECT updateID FROM #X2) AND
E.TimeAtServer = (SELECT MAX(TimeAtServer) FROM SUSDB.dbo.vwEventHistory E2
WHERE E.updateID = E2.updateID AND
E.ComputerID = E2.ComputerID)
} if ($type eq 'error_patches');
return qq{
select A.*, B.*, C.*
from
SUSDB.dbo.tbTargetInTargetGroup as A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbTargetGroup C ON A.TargetGroupID = C.TargetGroupID
; } if ($type eq 'host_groups');
return qq{
SELECT distinct C.*, D.*
FROM
SUSDB.dbo.tbTargetGroup A
LEFT OUTER JOIN
SUSDB.dbo.tbTargetInTargetGroup B ON B.TargetGroupID = A.TargetGroupID
RIGHT OUTER JOIN
SUSDB.dbo.tbComputerTarget C ON B.TargetID = C.TargetID
INNER JOIN
SUSDB.dbo.tbComputerTargetDetail D ON C.TargetID = D.TargetID
} if ($type eq 'hosts');
return qq{
SELECT
RevisionID,
COUNT(SupersededUpdateID) AS counter
INTO #X1
FROM
SUSDB.dbo.tbRevisionSupersedesUpdate
GROUP BY
RevisionID;
SELECT
COUNT(RevisionID) AS counter,
SupersededUpdateID
INTO #Y1
FROM
SUSDB.dbo.tbRevisionSupersedesUpdate
GROUP BY
SupersededUpdateID;
SELECT
distinct B.targetID,
B.FullDomainName,
B.IPAddress,
H.Title AS category,
C.Title,
A.SummarizationState,
B.LastReportedStatusTime,
F.SecurityBulletinID,
I.KBArticleID,
D.updateID,
E.RevisionID,
#X1.counter AS SupersedeUpdateCount,
#Y1.counter AS SupersededUpdateCount
FROM
SUSDB.dbo.tbUpdateStatusPerComputer A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbUpdate D ON A.LocalUpdateID = D.LocalUpdateID
INNER JOIN
SUSDB.dbo.tbPreComputedLocalizedProperty C ON C.updateID = D.updateID
INNER JOIN
SUSDB.dbo.tbRevision E ON D.localUpdateID = E.localUpdateID
LEFT OUTER JOIN
SUSDB.dbo.tbSecurityBulletinForRevision F ON E.RevisionID = F.RevisionID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory G ON E.RevisionID = G.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty H ON G.CategoryID = H.CategoryID
LEFT OUTER JOIN
SUSDB.dbo.tbKBArticleForRevision I ON E.RevisionID = I.RevisionID
LEFT JOIN
#X1 ON E.RevisionID = #X1.RevisionID
LEFT JOIN
#Y1 ON D.updateID = #Y1.SupersededUpdateID
WHERE
(H.ShortLanguage = 'en') AND
(C.ShortLanguage = 'en') AND
(H.CategoryType = 'UpdateClassification') AND
(NOT (A.SummarizationState IN (1, 4)));
} if ($type eq 'needed_patches');
return qq{
SELECT distinct d.DeploymentGuid AS UpdateApprovalId, u.UpdateID, d.TargetGroupID AS ComputerTargetGroupId,
C.Title, g.Name as GroupName,
(CASE d .ActionID WHEN 0 THEN N'Install' WHEN 1 THEN N'Uninstall' WHEN 2 THEN N'NotApproved' WHEN 3 THEN N'NotApproved' ELSE NULL END)
AS Action, d.Deadline, d.DeploymentTime AS CreationDate, d.AdminName AS AdministratorName, ~ d.IsAssigned AS IsOptional,
~ r.IsLatestRevision AS IsStale
FROM SUSDB.dbo.tbUpdate AS u
INNER JOIN
SUSDB.dbo.tbRevision AS r ON u.LocalUpdateID = r.LocalUpdateID
INNER JOIN
SUSDB.dbo.tbProperty AS p ON r.RevisionID = p.RevisionID
INNER JOIN
SUSDB.dbo.tbDeployment AS d ON d.RevisionID = r.RevisionID
INNER JOIN
SUSDB.dbo.tbPreComputedLocalizedProperty AS C ON C.updateID = u.updateID
INNER JOIN
SUSDB.dbo.tbTargetGroup AS g ON d.TargetGroupID = g.TargetGroupID
LEFT OUTER JOIN
SUSDB.dbo.tbSecurityBulletinForRevision AS b ON r.RevisionID = b.RevisionID
LEFT OUTER JOIN
SUSDB.dbo.tbKBArticleForRevision AS k ON r.RevisionID = k.RevisionID
INNER JOIN
SUSDB.dbo.tbRevisionInCategory AS rc ON r.RevisionID = rc.RevisionID
INNER JOIN
SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty AS cl ON rc.CategoryID = cl.CategoryID
WHERE
((p.ExplicitlyDeployable = 1) AND (d.TargetGroupTypeID = 0) AND (d.ActionID IN (0, 1, 3)) OR
(p.ExplicitlyDeployable = 1) AND (d.TargetGroupTypeID = 0) AND (d.ActionID = 2) AND
(d.TargetGroupID <> (select TargetGroupID from SUSDB.dbo.tbTargetGroup where Name = 'All Computers')))
AND (C.ShortLanguage = 'en') AND(cl.ShortLanguage = 'en') AND (cl.CategoryType = 'UpdateClassification')
} if ($type eq 'patch_approvals');
return 1;
}
sub get_TimeField ($){
my @fields = @{shift @_};
my %output;
foreach my $i (@fields){
if ($i =~ m/time/io || $i =~ m/BiosReleaseDate/o || $i =~ m/CreationDate/o){
$output{$i} = 1
}else{
$output{$i} = 0;
}
}
return \%output;
}
sub convert_time ($){
my $init_str = shift;
return "" if ($init_str =~ /^\s*$/);
my @data = split / /, $init_str;
my @date = split /\//, $data[0];
my @time = split /:/, $data[1];
$time[0] = 0 if ($time[0]==12);
$time[0] += 12 if $data[2] eq 'PM';
return sprintf("%04d/%02d/%02d %02d:%02d:%02d", $date[2], $date[0], $date[1], @time);
}
use Win32::OLE;
# datetime型のデータが文字列で取得できる。
use Win32::OLE::variant;
# 指定タイプライブラリのコンスタントが参照可能
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.0 Library';
use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG :DATE :TIME);
# エラー時に本処理を中止し、Perlがエラーメッセージを出力し、本プロセスが終了する。
Win32::OLE->Option(Warn => 3);
use Encode;
my $usage = qq{WSUS report retrieval tool.
Usage: $0 [Options]
Options:
-A Retrieve all supported reports. (Same as "-a -e -g -h -n -p")
-a List of all patches.
-e Error patch list.
-g Hosts list with their group name.
-h host list.
-n List of patches need to be applied.
-p Approval Status report.
-H Show this message.
-P Prefix for output filename.
-3 Use with WSUS ver 3.0 SP2. (default)
-4 Use with Windows 2012 WSUS (ver 4.0?) (takes priority if both of -3 and -4 were set.)
-t timeout (sec) (0 = connection not to timeout. Default is 30 seconds.)
The output filename will be set as below.
-a: all_patches_yyyymmdd-HHMM.csv
-e: error_patches_yyyymmdd-HHMM.csv
-g: host_groups_yyyymmdd-HHMM.csv
-h: hosts_yyyymmdd-HHMM.csv
-n: needed_patches_yyyymmdd-HHMM.csv
-p: patch_approvals_yyyymmdd-HHMM.csv
};
die $usage if ( $#ARGV < 0 );
use Getopt::Std;
use vars qw($opt_A $opt_a $opt_e $opt_g $opt_h $opt_n $opt_p $opt_H $opt_P $opt_3 $opt_4 $opt_t);
getopts('AaeghnpHP:34t:') or die $usage;
die $usage if $opt_H;
die $usage if ( $#ARGV != -1 );
if ($opt_A){
$opt_a = 1;
$opt_e = 1;
$opt_g = 1;
$opt_h = 1;
$opt_n = 1;
$opt_p = 1;
}
$opt_t = 30 if (not defined $opt_t);
my %requests;
$requests{all_patches} = 1 if ($opt_a);
$requests{error_patches} = 1 if ($opt_e);
$requests{host_groups} = 1 if ($opt_g);
$requests{hosts} = 1 if ($opt_h);
$requests{needed_patches} = 1 if ($opt_n);
$requests{patch_approvals} = 1 if ($opt_p);
my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime();
#秒 分 時 日 月 年 曜日 年初か 夏時間を
# らの 適用して
# 経過日 いるか
$year += 1900; # localtime関数からは1900年から数えた年が返却される。
$mon++; # 月は0から始まるので、表示するときは1を加える。
my @day_of_week = qw( 日 月 火 水 木 金 土 ); # 日曜日は0になります。
my $date_str=sprintf("%04d%02d%02d-%02d%02d", $year, $mon, $mday, $hour, $min);
$opt_P = '' if (! $opt_P);
my %filenames;
$filenames{all_patches} = $opt_P . "all_patches_${date_str}.csv";
$filenames{error_patches} = $opt_P . "error_patches_${date_str}.csv";
$filenames{host_groups} = $opt_P . "host_groups_${date_str}.csv";
$filenames{hosts} = $opt_P . "hosts_${date_str}.csv";
$filenames{needed_patches} = $opt_P . "needed_patches_${date_str}.csv";
$filenames{patch_approvals} = $opt_P . "patch_approvals_${date_str}.csv";
# DBサーバー名とDB名設定
my $server;
if ($opt_4) {
$server = 'np:\\\\.\pipe\Microsoft##WID\tsql\query';
}else{
$server = 'np:\\\\.\pipe\mssql$microsoft##ssee\sql\query';
}
my $db = 'SUSDB';
# WINDOWS 認証設定
my $connStr = "Provider=sqloledb;".
"Data Source=$server;".
"Initial Catalog=$db;".
"Integrated Security=SSPI;";
#print "$connStr\n";
# SQL Server 認証設定
#my $id = "testid";
#my $ps = "testps";
#my $connStr = "Provider=sqloledb;".
# "Data Source=$server;".
# "Initial Catalog=$db;".
# "User ID=$id;".
# "Password=$ps;";
# DB接続
my $objDB = Win32::OLE->new("ADODB.Connection");
print "Connection test to Windows Internal Database.....\n";
$objDB->Open($connStr);
$objDB->{Errors}->{Count} and die "cannot connect '$connStr'";
print "success!\n";
$objDB->{CommandTimeOut} = $opt_t;
# テーブル有無チェック
my $rs = Win32::OLE->new("ADODB.Recordset");
print "Access test to WSUS Database.....\n";
$rs->Open("Select count(*) existance From sysobjects Where NAME = 'tbUpdate'", $objDB);
if ($rs->{existance}->{Value} == 0) {
die "Accessing DB is not for WSUS.";
}
print "success!\n";
$rs->Close();
$objDB->Close();
foreach my $type (keys %requests){
print "Extracting to " . $filenames{$type} . "\n";
print "$type:\t";
$objDB->Open($connStr);
$objDB->{Errors}->{Count} and die "cannot connect '$connStr'";
$objDB->{CommandTimeOut} = $opt_t;
open OUTFILE, ">:utf8", $filenames{$type} or die "cannot open ".$filenames{$type};
# データ読み出し
$rs = Win32::OLE->new("ADODB.Recordset");
$rs->{CursorLocation} = adUseClient;
my $sql_statement = qq{
SET NOCOUNT ON;
SET DATEFORMAT mdy
} . get_sql($type) . ";";
#print $sql_statement . "\n";
$rs->Open($sql_statement, $objDB);
my @Fields = @{get_fields ($type)};
my %isTime = %{get_TimeField (\@Fields)};
print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @Fields;
print OUTFILE "\x0a";
my $counter = 0;
my $counter_length = 0;
$| = 1;
if ($type ne 'error_patches'){
while(!$rs->EOF and $rs->{RecordCount} != 0){
$counter++;
if ($counter % 100 == 0){
print "\b" x $counter_length;
$counter_length = length $counter;
print $counter;
}
my @items = ();
foreach my $field (@Fields){
my $item =$rs->{Fields}->{$field}->{Value};
$item =~ s/^\s*//gmo;
$item =~ s/\s*$//gmo;
if ($isTime{$field}){
if (ref( $rs->{Fields}->{$field}->{Value}) eq "Win32::OLE::Variant"){
$item = $rs->{Fields}->{$field}->{Value}->Date("yyyy/MM/dd") . " " .
$rs->{Fields}->{$field}->{Value}->Time("hh:mm:ss");
}else{
$item = convert_time ($rs->{Fields}->{$field}->{Value});
}
}
push @items, $item;
}
print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @items;
print OUTFILE "\x0a";
$rs->MoveNext();
}
}else{
while(!$rs->EOF and $rs->{RecordCount} != 0){
$counter++;
if ($counter % 100 == 0){
print "\b" x $counter_length;
$counter_length = length $counter;
print $counter;
}
my @items = ();
my $i = -1;
foreach my $field (@Fields){
$i++;
my $item = $rs->{Fields}->{$field}->{Value};
$item =~ s/^\s*//gmo;
$item =~ s/\s*$//gmo;
if ($i == 7){
my @tmp = split /\n/, $item;
$tmp[2] =~ s/^\s*(.*)<\/string>.*$/\1/go;
$item = $tmp[2];
}
if ($isTime{$field}){
if (ref( $rs->{Fields}->{$field}->{Value}) eq "Win32::OLE::Variant"){
$item = $rs->{Fields}->{$field}->{Value}->Date("yyyy/MM/dd") . " " .
$rs->{Fields}->{$field}->{Value}->Time("hh:mm:ss");
}else{
$item = convert_time ($rs->{Fields}->{$field}->{Value});
}
}
push @items, $item;
}
print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @items;
print OUTFILE "\x0a";
$rs->MoveNext();
}
}
print "\b" x $counter_length;
print $counter , " records found.\n\n";
$rs->Close();
# DB切断
$objDB->Close();
}
ついでに、接続タイムアウトが変更できるようにした。ADOの標準では30秒でタイムアウトするので、巨大なWSUS DBからのデータダウンロードではタイムアウトしてしまうため。
.bat化するなら、こんな感じ。
set output_dir=.\report if not exist %output_dir% (mkdir %output_dir%) .\WSUS_report.exe -A -P %output_dir%\%computername%_ -4
Perl2ExeがPerl 5.16.3までしかサポートしていない2013年版しか公開されていないのに、Active PerlのCommunity版は5.16.3が公開されていなかったので、strawberry-perlでExe化したせいか、libgcc_s_sjlj-1.dllとlibstdc++-6.dllがないとエラーが出る。C:\strawberry\c\binにある同ファイルを持ってきておけば良いが、新しいPerl2Exeが出たら、Active Perlでコンパイルしなおしてみよう。
WSUS_report_20151209.zip
0 件のコメント:
コメントを投稿