#!/usr/bin/perl -w
#
# Name: WSUSExtractor.pl
#
# Purpose: Extract latest or all updates from WSUS.
#
# Syntax: WSUSExtractor.pl
#
# Version: 1.0
#
# Requirements: Windows 2000+
# ActivePerl 5.8.7 buld 815 or higher from www.ActiveState.com
# osql.exe Utility (included in MSDE)
# WSUS on MSDE
#
# Author: biakus
#
use strict;
use locale;
use IO::File;
use File::Path;
use Win32::Registry;
$|=1;
my $ppid;
my $fpid=open_pid_file("WSUSExtractor.pid");
print $fpid $$;
close($fpid);
# warn("Starting script (pid=$$)\n");
###########################################################################################
# Tunable params
###########################################################################################
my $root = "C:\\WSUS\\FtpRoot"; # your dir for files (volume must be the same at WSUS content folder for NTFS hardlinks)
my $is_latest = 1; # Is all updates extract or latest only
my $update_langs = " 0,1033,1049 "; # comma separated languageIDs of extracting updates (0-all,1033-en,1049-ru,..%lang)
my $descr_lang = "ru"; # short language of titles and descriptions of updates (en,ar ..)
my $db_name = "SUSDB";
my $content_dir = ""; # WSUS content folder
my $server_name = ""; # WSUS SqlServerName
my $tmpfile = "query.sql";
###########################################################################################
my @rec = ();
my $col;
my $query;
my $cmd;
###########################################################################################
# Huge SQL query temporary save to file
###########################################################################################
my $latest_str = "";
if ($is_latest) { $latest_str = " AND tbRevision.IsLatestRevision = 1 ";
print "\nStart extracting latest updates\n"; }
else { print "\nStart extracting all updates\n"; }
open (SQL, ">$tmpfile");
print SQL qq {
SELECT C2.CategoryID, C3.CategoryID, RC4.CategoryID, tbRevisionLanguage.LanguageID,
tbFile.FileName, tbFile.FileDigest
FROM tbCategory AS C1
INNER JOIN tbCategory AS C2 ON C1.CategoryID = C2.ParentCategoryID
INNER JOIN tbCategory AS C3 ON C2.CategoryID = C3.ParentCategoryID
INNER JOIN tbCategory AS C4 ON (C4.ParentCategoryID IS NULL AND C4.CategoryID != 7 )
INNER JOIN tbRevisionInCategory AS RC3 ON ( C3.CategoryID = RC3.CategoryID )
INNER JOIN tbRevisionInCategory AS RC4 ON ( RC4.RevisionID = RC3.RevisionID AND RC4.CategoryID = C4.CategoryID )
INNER JOIN tbBundleDependency ON RC4.RevisionID = tbBundleDependency.BundledRevisionID
INNER JOIN tbPreComputedLocalizedProperty ON ( tbPreComputedLocalizedProperty.RevisionID = tbBundleDependency.RevisionID )
INNER JOIN tbRevision ON ( tbRevision.RevisionID = RC4.RevisionID $latest_str )
INNER JOIN tbFileForRevision ON ( tbFileForRevision.RevisionID = RC4.RevisionID )
INNER JOIN tbRevisionLanguage ON ( tbRevisionLanguage.RevisionID = tbFileForRevision.RevisionID )
INNER JOIN tbFile ON ( tbFile.FileDigest = tbFileForRevision.FileDigest )
WHERE ( tbRevisionLanguage.Expanded = 0 AND tbRevisionLanguage.LanguageID IN ( $update_langs )
AND tbPreComputedLocalizedProperty.ShortLanguage like '$descr_lang' )
ORDER BY tbFile.Modified };
close(SQL);
###########################################################################################
# Find WSUS content folder and SQL server address
###########################################################################################
my $reg;
$::HKEY_LOCAL_MACHINE->Open("SOFTWARE\\Microsoft\\Update Services\\Server\\Setup", $reg)
or die "Can't open registry: $^E";
$reg->QueryValueEx("ContentDir", $col, $content_dir) or die "Cannot find WSUS content folder in registry: $^E";
$content_dir = $content_dir."\\WsusContent";
# print "Here's a dir: $content_dir\n";
$reg->QueryValueEx("SqlServerName", $col, $server_name) or die "Cannot find WSUS content folder in registry: $^E";
$server_name =~ s/%computername%/$ENV{computername}/;
# print "Here's a server name: $server_name\n";
###########################################################################################
# Put to hash Microsoft WSUS languages
###########################################################################################
my %lang = ();
$query= "SELECT LanguageID, ShortLanguage FROM tbLanguage";
$cmd = "osql.exe -w 500 -h-1 -s# -n -E -d $db_name -S $server_name -Q \"$query\" 2>nul |";
open (DAT, $cmd);
while(<DAT>) {
chomp;
# print $_."\n";
if ( @rec = split(/#/) )
{ foreach $col (@rec) { if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
else { last; }
$lang{$rec[0]} = $rec[1];
# print $rec[0]."\t".$rec[1]."\n";
}
close(DAT);
###########################################################################################
# Put to hash Microsoft WSUS categories
###########################################################################################
my %ctg = ();
$query= "SELECT CategoryID, Title FROM tbPrecomputedCategoryLocalizedProperty WHERE ( ShortLanguage like 'en') ";
$cmd = "osql.exe -w 500 -h-1 -s# -n -E -d $db_name -S $server_name -Q \"$query\" 2>nul |";
open (DAT, $cmd);
while(<DAT>) {
chomp;
if ( @rec = split(/#/) )
{ foreach $col (@rec) { if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
else { last; }
$rec[1] =~ s/\s+|\//_/g;
$rec[1] =~ s/\W//g;
$ctg{$rec[0]} = $rec[1];
}
close(DAT);
###########################################################################################
# Processing Microsoft WSUS database and save files
###########################################################################################
my $srcfile;
my $dstfile;
my $path;
my $counter = 0;
$cmd = "osql.exe -w 5000 -h-1 -s# -n -E -d $db_name -S $server_name -i $tmpfile 2>nul |";
print "\nProcessing..\n";
open (DAT, $cmd);
while(<DAT>) {
chomp;
# print $_."\n";
if ( @rec = split(/#/) )
{ foreach $col (@rec) { if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
else { last; }
if ($rec[4] =~ m/.(\w+)$/ ) { $srcfile = $1; }
if ($rec[5] =~ m/^0x(\w{38})(\w{2})$/){ $srcfile = "$content_dir\\$2\\$1$2.$srcfile"; }
$path = $root."\\".$ctg{$rec[0]}."\\".$ctg{$rec[1]}."\\".$ctg{$rec[2]}."\\".$lang{$rec[3]};
if (! -e $path) { mkpath($path, 0, 0777) or die "Can't create dir: $^E\n";}
$dstfile = $path."\\".$rec[4];
if (hardlink($srcfile, $dstfile, 1)) { $counter += 1; }
}
close(DAT);
print "\nTotal matching updates in database: $counter\n";
unlink($tmpfile) or die "Cannot unlink tmpfile: $^E";
warn "\nSuccess\n";
###########################################################################
###########################################################################################
# POSIX NTFS hard link to file
###########################################################################################
sub hardlink {
my $exist_file=shift;
my $new_file=shift;
my $is_rewrite = shift;
if(-e $new_file && $is_rewrite)
{ unlink ($new_file) or return 0; }
link ( $exist_file, $new_file ) or return 0;
return 1;
}
###########################################################################################
# pid processing
###########################################################################################
sub open_pid_file {
my $file=shift;
if(-e $file) {
my $fh=IO::File->new($file) or die "Can't open PID file $file: $^E";
$ppid=<$fh>;
die "Invalid PID file" unless $ppid=~ /^(\d+)$/;
die "Process already running with PID $ppid" if(kill 0 => $ppid);
warn "Removing PID file for defunct process ($ppid).\n";
die "Can't unlink PID file $file" unless -w $file && unlink $file;
}
$ppid=$$;
return IO::File->new($file,O_WRONLY|O_CREAT|O_EXCL,0644)
or die "Can't create $file: $^E\n";
}
###########################################################################################
END {
unlink "WSUSExtractor.pid" or die "Can't unlink PID file: $^E";
# warn("Stoping script (pid=$$)\n");
}
__END__
|
