Windows 2012 WSUSからパッチのステータスを取り出すスクリプト

SUSからパッチのステータス一覧等々必要なデータを取り出すスクリプト。
以前のは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