Perl script to extract updates from WSUS content folder by — Alexey Biznya

This script by Alexey Biznya will Extract and sort by Categories latest or all updates from \WSUSContent folder. The script will find \WSUSContent folder and SQL Server name from the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup.


[WSUS stores updates in \WSUSContent folder using SHA1 hash for file names and this script will query WSUS SQL/MSDE database and then exports those updates and categories.]


Requirements



  1. Windows 2000+
  2. ActivePerl 5.8.7 build 815 or higher from www.ActiveState.com
  3. osql.exe Utility (included in MSDE)

Tunable params


my $root = “C:\\WSUS\\FtpRoot”; Make sure \WSUSContent folder and the folder where it will extract the files should be on the same volume. For instance, I have WSUSContent folder in ‘D:\WSUS\WSUSContent and the folder to extract updates in ‘D:\WSUS\FtpRoot’



  1. my $update_langs = ” 0,1033,1049 “; use 1033 for English.
  2. my $descr_lang = “ru”; use en for English.
  3.  my $content_dir = “”; The script will search in registry so you don’t have to input the values here.
  4. my $server_name = ” ; The script will search in registry so you don’t have to input the values here.

Syntax: WSUSExtract.pl


# ======== start of script ========


#!/usr/bin/perl -w
#
# Name:           WSUSExtractor.pl
#
# Purpose:        Extract and sort by categories latest or all updates from WSUS .
#
# Syntax:         WSUSExtract.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:         Alexey Biznya
#



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__


# ======== end of script ========

Leave a Reply

Your email address will not be published. Required fields are marked *