Home
.. About WSUS Wiki

RSS

WSUS
.. WSUS FAQ
.. WSUS on SBS
.. WSUS Troubleshooting
.. WSUS News Groups
.. Known WSUS Issues
.. WSUS Links
.. WSUS Wish List

WSUS Documents
.. WSUS Deployment Guide
.. WSUS Installation Guide
.. WSUS Release Notes
.. WSUS Best Practice

SUS
.. SUS FAQ
.. What Is SUS
.. SUS Troubleshooting
.. SUS Links
.. SUS Known Issues
.. SUS FAQ
.. What Is SUS
.. SUS Troubleshooting
.. SUS Links
.. SUS Known Issues

Wiki Community

 .. Wiki Contributors
.. I Love WSUS
.. WSUS Wiki Diary
.. Wiki Statistics
.. To Do Page

Miscellaneous Stuff
.. Other Resources
.. Do You Know?
 


Site Meter


Terms of Use
Trademarks

Privacy Statement

 

wusfaq


I have a system that fails to install some updates and would like to run
them manually. I tried to find the files in the WSUS repository but the
naming convention of the files doesn't reflect the update name in WSUSADMIN
making the files hard to find.

Is there a way to find out the physical update file location from the
WSUSADMIN interface?

Can I find this information in one of the SQL tables?

Here’s one for the suggestion box. Not sure if it should be posted here
though.
A nice feature would be to have the physical file location listed with the path
 details in WSUSADMIN.

Comments:

From biakus - 2/3/06 5:50 AM

#!/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__

From maximillianx - 1/4/06 10:02 AM

I have an HTA-based tool that will accomplish this as well.  Check here: http://www.wsus.info/forums/index.php?showtopic=6685

From RCollewijn - 8/31/05 3:29 AM

I have found a web page solution for this issue; you can find this on
the following link.

http://wsus.collewijn.info/mai n.php?page=hotfixinfo_enu.php

Unfortunately is this the only page that's in English (the rest of
the web site is still under construction and in Dutch).

Robert

From torgeir - 8/8/05 3:39 AM

 

And from a second poster in the WSUS newsgroup:

From: "Alexander Suhovey" <asuhovey@mtu-net.ru>
Subject: Extract updates from WSUS content folder
Date: Sun, 7 Aug 2005 16:30:30 +0400
Message-ID: <#FsWUv0mFHA.764@TK2MSFTNGP14.phx.gbl>
Newsgroups: microsoft.public.windows.server.update_services

Hello everyone,

I wrote a batch script to 'extract' particular updates from WSUS content
folder. Thought that maybe it could be useful for somebody. Find script in
attachment.

The problem with content folder is that WSUS stores updates using SHA1 hash
for file names so it is difficult to determine what file belongs to what
update. The script determines a human-readable name of update file by
querying  WSUS SQL/MSDE database.

====================================
SYNTAX:   WSUSExtract.cmd <-f:search_string> [-l:lang] [-t:target]

-f:search_string - Part of update name to search for.

-l:lang               - Update language. If omitted, all languages will be
processes.

-t:target            - Target folder to copy updates to. If omitted, script
assumes 'View Only' mode.

EXAMPLES:
Copy downloaded english updates corresponding 896358 KB article to 'c:\temp'
folder:
    WSUSExtract.cmd -f:896358 -l:enu -t:c:\temp

View all downloaded updates corresponding to 896358 KB article:
    WSUSExtract.cmd  -f:896358
====================================


::*************************************************************************
:: Name:           WSUSExtract.cmd
::
:: Purpose:        Finds and copies particular update(s) from WSUS content folder.
::
:: Syntax:         WSUSExtract.cmd <-f:search_string> [-l:language] [-t:target_folder]
::
:: Version:        1.1
:: Technology:     NT Batch
::
:: Requirements:   Windows 2000+
::                 REG.EXE (in case of Windows 2000, install Support Tools)
::                 OSQL.EXE Utility (included in MSDE)
::
:: Authors:        Alexander Suhovey
::
:: History:        08/07/2005 - Database server address is now determined from registry
::                 08/05/2005 - First release.
::*************************************************************************
@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
Echo.

::========= OS Check ============
if not "%OS%" == "Windows_NT" goto BADOS
if "%APPDATA%" == "" goto BADOS

::====== Parse command line =====
If "%1"=="" GOTO USAGE
If "%1"=="/?" GOTO USAGE
If "%1"=="-?" GOTO USAGE
:PARSE
set arg=%1
shift /1
If not defined arg GOTO NEXT
If "%arg:~0,3%"=="-f:" (set fstr=%arg:~3%&GOTO PARSE)
If "%arg:~0,3%"=="-l:" (set lang=%arg:~3%&GOTO PARSE)
If "%arg:~0,3%"=="-t:" (set target=%arg:~3%&GOTO PARSE)
Echo ERROR: Unknown argument: %arg%
GOTO USAGE
:NEXT
If defined target (
    set target=%target:"=%\
    If not exist "%target%" echo ERROR: Cannot find target folder: "%target%" & goto :eof
) Else (
    set viewonly=1
    Echo Target folder not defined. Assuming View Only mode.
    echo.
)

::== Find WSUS content folder ===
::=== and SQL server address  ===
set regkey="HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup"
for /f "tokens=2* delims= " %%i in ('reg query %regkey% /v ContentDir ^| find "ContentDir"') do set source=%%j
If not defined source Echo ERROR: Cannot find WSUS content folder in registry. Check if WSUS is installed. & goto :eof
set source=%source%\WsusContent
for /f "tokens=2* delims= " %%i in ('reg query %regkey% /v SqlServerName ^| find "SqlServerName"') do set server=%%j
If not defined server Echo ERROR: Cannot find WSUS database server in registry. Check if WSUS is installed. & goto :eof
set osqlcommand=osql.exe -w 500 -h-1 -E -d SUSDB -S %server%

::====== Test SQL database ======
::========= connection ==========
for /f "delims=" %%i in ('%osqlcommand% -Q') do (
    Echo ERROR: Cannot connect to WSUS SQL server '%server%'
    goto :eof
)

::=== Format osql.exe query =====
set query=select FileDigest,FileName from tbFile
If defined fstr set query=%query% where FileName like '%%%fstr%%%'
If defined lang (
    If defined fstr (set query=!query! and) Else (set query=!query! where)
    set query=!query! FileName like '%%%lang%%%'
)

::======== Main section =========
set num1=0
set num2=0
echo ========================================
for /f "tokens=1,2" %%i in ('%osqlcommand% -Q "%query%" ^| find /v "rows affected"') do (
    set /a num1+=1
    set dstfile=%%j
    set srcfile=%%i
    set srcfilepath="%source%\!srcfile:~-2!\!srcfile:~2!.!dstfile:~-3!"
    set dstfilepath="%target%%%j"
    If exist !srcfilepath! (
        set /a num2+=1
        If defined viewonly (
            echo !dstfile!
        ) Else (
            If exist !dstfilepath! set dstfilepath="%target%%%~nj(Copy !random!)%%~xj"
            set /p foo="Copying !dstfile! ... "<nul
            copy !srcfilepath! !dstfilepath! >nul 2>&1
            If errorlevel 1 (echo FAILED.) Else (echo Done.)
        )
    )
)
echo ========================================
echo Updates found in content folder    : %num2%
echo Total matching updates in database : %num1%
goto :eof

::========= Echo syntax =========
:USAGE
echo.
Echo %~nx0
Echo Finds and copies particular update(s) from WSUS content folder.
Echo Renames updates to human-readable format using information
Echo obtained from local WSUS SQL database.
Echo.
Echo SYNTAX:   %~nx0 ^<-f:search_string^> [-l:lang] [-t:target]
Echo.
Echo -f:search_string - Part of update name to search for.
Echo                    Naming format for OS updates: OS-KBNUMBER-HW-LANG.EXT
Echo                    OS       - Operating system (eg 'WindowsXP')
Echo                    KBNUMBER - MSKB number (e.g 'KB896358')
Echo                    HW       - Hardware technology ('x86', 'ia64' etc)
Echo                    LANG     - OS language (see -l switch below)
Echo                    EXT      - Extention ('exe', 'cab' etc)
Echo -l:lang          - Update language. If omitted, all languages will
Echo                    be processes. Examples are:
Echo                    rus - russian
Echo                    enu - english
Echo                    fra - french
Echo                    deu - deutch
Echo -t:target        - Target folder to copy updates to.
Echo                    If omitted, script assumes 'View Only' mode.
Echo                    Do not use trailing slash.
Echo.
Echo EXAMPLES:
Echo.
Echo Copy downloaded english updates corresponding
Echo to 896358 KB article to 'c:\temp' folder:
Echo.
Echo     %~nx0 -f:896358 -l:enu -t:c:\temp
Echo.
Echo View all downloaded updates corresponding to 896358 KB article:
Echo.
Echo     %~nx0 -f:896358
Echo.
goto :eof

::===== If Bad OS detected ======
:BADOS
echo.
echo ERROR: This script needs Windows 2000 or better.
goto :eof

::======== End of script ========

 

 

From torgeir - 8/3/05 3:20 AM

From a previous newsgrop posting in the WSUS newsgroup:


Date: Thu, 28 Jul 2005 00:10:14 +0400
Subject: Re: copy update files on WSUS
From: "Aleksey \"Carcass\" Melnick" <d9-thc@mail.ru>
Message-ID: <op.suk8zcger50b11@carcass>
Newsgroups: microsoft.public.windows.server.update_services

<quote>
filename on wsus is a sha1 hash of the file itself, and the last two
symbols of the filename is the folder where is that file located.
WindowsServer2003-KB889101-SP1-x86-ENU.exe =
SHA1:CDF6552483A5A4491BAA195CA76781EEAA059091 =
WsusContent\91\CDF6552483A5A4491BAA195CA76781EEAA0 59091.exe.

so make the query with the "query analyser" on SUSDB database containing
somth like following:

select FileDigest,FileName from tbFile where FileName like '%889101%' and
FileName like '%enu%'

and u will recieve:
FileDigest                                 FileName
------------------------------------------  -------------------------------------------------------
0x46B565EDBB9811B724566C95CB9FEA213F47D909  WindowsServer2003-KB889101-SP1-Express-x86-ENU.exe
0xCDF6552483A5A4491BAA195CA76781EEAA059091  WindowsServer2003-KB889101-SP1-x86-ENU.exe

(2 row(s) affected)

</quote>



Last Modified 8/2/05 2:22 PM

Hide Tools