Before you get to this point:
- You need to have successfully synchronized your active SUP with the latest and greatest software updates metadata and the WSUSContent directory 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.
Now that you know which updates are required, you have to figure out how to get them on to the non-Internet connected software update point so you can deploy them. The first part of all of this (part 1 of this post) is to query the site database to find all the required updates that you want to download. After you get that far, you'll need to use some handy .vbs scripts to actually do all the hard work and download the updates from a remote computer with an Internet connection—that will be part 2.
Technically, you don't have to create this view, but it makes the syntax of the script to come easier with the added bonus that you can use the view to create a Web report to view more information about the required updates other than just their download location.
NOTE: If you use this view to create a Web report, don't forget to grant the webreport_approle application role the required select permissions!
Here's the query that I'm using for the view and an explanation of what I'm actually querying on. Basically, what I'm after are required updates that haven't been downloaded yet and aren't expired (we can't deploy expired updates with ConfigMgr). If you create this view, make sure that you call it v_RequiredUpdates because that's what the script in the next post will query on:
TOP (100) PERCENT dbo.v_UpdateInfo.DatePosted, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Title,
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)
ORDER BY dbo.v_UpdateInfo.ArticleID DESC
Here's what the query does (WHERE statements):
Finds all the updates that are displayed as required (dbo.v_Update_ComplianceStatusAll.Status=2)
Finds all updates that are either English, or have no language-specific content locale:
- (dbo.v_UpdateContents.ContentLocales = 'English' OR dbo.v_UpdateContents.ContentLocales = ' ')
- You'll need to manually add in any additional languages that you want to download updates for here.
- Finds all updates that are not expired, because you can't download/deploy expired updates (dbo.v_UpdateInfo.IsExpired = 'False')
- Finds all of those updates that have not been downloaded yet ((dbo.v_UpdateContents.ContentProvisioned = 0)
- Orders the resulting update information by article ID (ORDER BY dbo.v_UpdateInfo.ArticleID DESC).
Here are some .sql files that you can use to make all of this easier. I've had to rename their file extensions from .sql to .txt to upload them, but you'll need to right-click and download them anyway because the database to use needs to be changed to your site database name. After modifying the database to use in the files, change the file extension back to .sql before running them:
- If you don't really want to go into SQL to create the view, here is a little .sql that will create it the easy way (make sure you change the USE line at the top to use your site database!): Create_v_RequiredUpdates.sql. This one is pretty long so I'm not going to post the contents of it here; you can see what is in there using Notepad.exe though.
If you just want to run the query real fast to see what it's supposed to get, run this: View_v_RequiredUpdates.sql
. Below are the contents of that .sql:
/*** Use this .sql to view the query results AFTER you create the v_RequiredUpdates view ***/
/*** Change the USE and FROM lines to use your site database! ***/
Now that you've got the view returning a list of required updates, the next post will show you how to use that view to actually download them.