Jeff Gilbert's Web blog at myITforum.com

This posting is provided "AS IS" with no warranties, and confers no rights :-)
Getting Required Updates on Non-Internet Connected SUPs (Part 2)

[BEGIN EDIT]

I've discovered something interesting that might make the remainder of this post moot.

When I installed WSUS for the SUP on the disconnected network and used the WSUS local internal database I could not use the download software updates wizard to obtain updates from the local Wsuscontent directory on the SUP (thus this post below). However, after writing the below post I did some additional testing and during WSUS installation for the disconnected SUP if I configured WSUS to use the SQL instance hosting the site database, and then approved required updates in the Internet-connected WSUS server (which downloaded them to the local wsuscontent dir on the Internet-connected WSUS), and then copied the wsuscontent dir to the WSUS installation on the disconnected network I COULD use the wsuscontent directory as the local source to download required updates. 

I'm going to leave the remainder of this post here because it has some other useful information like running scripts to directly query the site database for information, but the bottom line is this:

From my additional testing after writing the below post, it seems that if you use the SQL instance to host the WSUS database on the disconnected SUP, you do not need to go through all of the effort below and can just download updates locally from a synchronized wsuscontent dir containing the updates as downloaded by WSUS.

[END EDIT]

 

 

Before you get to this point:

  • You need to have successfully synchronized your active SUP with the latest and greatest software updates metadata from an Internet-connected WSUS Server (see: this post).
  • Enabled the software updates client agent for the site.
  • Have clients that have run software updates scans and reported in updates as required.
  • Optionally, but recommended, created the v_RequiredUpdates view according to this post.

Now that you know which updates are required (part 1), you have to figure out how to get them on to the non-Internet connected software update point so you can deploy them. To do this, you can use a script to query directly against the site database. This example uses a .vbs script to write the required update source url properties stored in the site database for required updates to a .txt file. Then, on an an Internet-connected computer, that .txt file is used as a source file for another script that reads the .txt file and downloads all the updates listed in the file. After the updates are downloaded, they can then be copied somehow back to the non-Internet connected site and used as a local source to download software updates.

I'm running the query directly against the site database because the source url property is stored in a site database table and not a view. That makes it difficult to query using standard WMI scripts that go through the SMS Provider for information. I didn't want to change the site database any more than necessary so I figured that this would be the way to go, plus it's a great example of how to do it.

Note: I didn't write this script completely on my own, but instead modified one that I found here: http://www.technowledgebase.com/2007/06/12/vbscript-how-to-create-an-ado-connection-and-run-a-query/

I'm not going to post the entire script here (runSQLQuery.txt is located here for download—you'll need to modify it a little bit and rename the extension from .txt to .vbs before trying to use it), but here's what it does in a nutshell:

  • Connects to the site database using integrated security
  • Runs a query to find the source url property for required updates that haven't been downloaded yet (runs the query created in part 1)
  • Writes the source url properties to a .txt file called SourceURLs.txt
  • Displays a pop-up message box to tell you how many update source urls were written to the text file.

The only part of the script that you must change is the source database and server entries in the connection object line. Otherwise, the script will run 'as is', but if you want to change the default behavior up a little, there are really only two other lines that you will need to modify (if you choose to):

Required change(s):

  1. objCN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=SMS_XYZ;Data Source=SiteServer"
    The default security settings will connect to the site database using integrated security. If you want to use a username & password to connect to SQL, then add in this after Persist Security Info=False: User ID=<account>;Password=<password>; and take out the Integrated Security part.
    You also need to change the site database name (Initial Catalog), and the site database server name (Data Source) to match your requirements.

Optional changes:

  1. strSQL="SELECT DISTINCT dbo.v_RequiredUpdates.SourceUrl FROM dbo.v_RequiredUpdates"
    If you didn't create the view, you can just comment out the line about querying the view and uncomment the full SQL query. In case you missed it from part 1, here it is: (watch for word wrap, this must all be on one line when used in the script):

    strSQL="SELECT DISTINCT TOP (100) PERCENT dbo.CI_ContentFiles.SourceURL FROM dbo.v_Update_ComplianceStatusAll INNER JOIN dbo.v_UpdateContents ON dbo.v_Update_ComplianceStatusAll.CI_ID = dbo.v_UpdateContents.CI_ID INNER JOIN dbo.v_UpdateInfo ON dbo.v_UpdateContents.CI_ID = dbo.v_UpdateInfo.CI_ID INNER JOIN dbo.CI_ContentFiles ON dbo.v_UpdateContents.Content_ID = dbo.CI_ContentFiles.Content_ID WHERE (dbo.v_Update_ComplianceStatusAll.Status = 2) AND (dbo.v_UpdateContents.ContentLocales = 'English' OR dbo.v_UpdateContents.ContentLocales = ' ') AND (dbo.v_UpdateInfo.IsExpired = 'False') AND (dbo.v_UpdateContents.ContentProvisioned = 0)"

  2. strOutputFile = "SourceURLs.txt"
    By default, the script will write the download location for required updates to a .txt file named SourceURLs.txt and that file name is referenced when using the download script. If you want to use a different text file name for some reason, then just change the strOutputFile value to whatever you want to call it. You'll have to remember to use the new file name for the download script too.

OK, all that out of the way, just double-click the .vbs and it should reward you with a bunch of required updates source urls written to a text file in the same directory that the script was run from and display a message similar to:

Note: Open SourceURLs.txt and backspace the last line so there is no empty line at the end. If you don't do that the download script still works correctly, but throws an error at the end when it sees the empty line.

Now it is time to take that text file (SourceURLs.txt), go to a computer with an Internet connection, and run script number two. The second script is the one that does all the hard work for you. It reads the source urls text file and downloads the updates into the same directory the script was run from. Just in case you wanted to test this, I've included a sample SourceURLs.txt file with three update download locations here.

This script came from a few hours of me banging my head against the wall trying to figure out how to do it properly and then realizing that the other side of the wall was literally the office of one of our talented SMS SDK writers…problem solved. The download script doesn't require any modifications at all other than some error logging that I didn't add into the script and it can be found here.

This time you need to open a command prompt and use the following command: cscript downloadUpdates.vbs SourceURLs.txt.

If all goes to plan after running the script, you should see something like the below screen shot and the folder the script was run in should now contain all of the downloaded updates:

 

 

Now you just need to somehow get that directory full of source files to the non-Internet connected site, share it out, and then go through your normal ConfigMgr software updates processes. When it comes time to deploy those updates, just select the option to download source files from a location on the local network and point the wizard to your local source files share:

 

 

Some random last thoughts about these posts that I've neglected to mention so far:

  • Why do we need these scripts? Because you can't just synchronize a WSUS Server and then sync that WSUS Server's downloaded update content with the WSUS Server hosting the SUP site role…well, you can, but you won't be able to download updates using the WSUS content files for distribution via ConfigMgr because the software updates download wizard won't recurse through the various update directories that the WSUS Server creates when it stores downloaded updates. Same goes for using a different SUP server's package source directory (another script on the back burner for me).
  • This technique will download all updates that clients have determined that they require through software updates scanning. You might not want to deploy every update that a client has found it requires so this might end up just using up hard drive space on your server with a bunch of unnecessary updates. Of course, you could just delete the original source directory contents after you create the deployment package, but the next time you run through this process those same updates will reappear as they're still required and haven't been downloaded yet. I figured it was better to have all the updates there just in case you ever ended up needing them than to have to go through this process all the time for smaller groups of updates. That being said, another option is to only download required updates that haven't been downloaded yet that exist in defined update lists. I'm working on that, but it has taken me a while to get to this point and to be honest I really need to get back to my day job J
  • These scripts are really more of an example and they'll work 'as is', but you might want to refine them as needed later on.

I hope you've found these posts (or at least some part of them) helpful and this technique gets your disconnected SUPs sync'd, and your clients patched!

~Jeff

Published Wednesday, October 29, 2008 6:08 AM by jgilbert

Comments

No Comments