SCOM - How to Check When the Update Rollup SQL Scripts Were Last Run

If you've deployed SCOM into your environment, then you should be familiar with the quarterly process of deploying the latest Update Rollup's (UR's) from Microsoft - which generally contain a collection of bug fixes and new enhancements.

The Problem
The process of deploying these UR's can be a fraught journey to navigate - particularly when this process demands you manually run two SQL scripts against the SCOM databases and then import a number of new management packs.

You can deploy the UR payloads either manually or with your usual patch management tools such as Windows Update or ConfigMgr. The problem with using either option though, is that quite often, people forget to run the SQL scripts (which must be run manually regardless of the deployment method) and forget to import the new management packs. Omitting either of these steps mean that slowly but surely, bad things start to happen within the management group.

The Process
Now, I'm a big fan of R.T.F.M. and will always read through any associated guides or technical notes before deploying something new into my environment and to be fair to Microsoft, they do mention in the UR documentation that these SQL scripts need to be manually executed against each database and new management packs have to be imported as part of the UR deployment process.

Along with the official KB article, there's some excellent advice available on the web to ensure everything goes smoothly and the following high-level steps are how I typically deploy new UR's into my customer's SCOM environments:

1. Read through the full KB article associated with the new UR (the latest UR9 article can be found here).
2. Manually download the UR packages from the Microsoft download catalog.
3. Head straight over to Kevin Holman's blog (he's like the Grand Master of the SCOM community) and read through his step-by-step guide for the latest UR (the link to his UR9 one is here).
4. Follow the instructions exactly as laid out in Kevin's step-by-step guide to manually deploy the update.
5. Sit back with my coffee and rejoice at a happy upgrade deployment.

Tip: I prefer using the manual download and deployment method for these UR's as all your agents will then be placed into the 'Pending Management' view in the Administration workspace with a note informing you they are ready for upgrade. This doesn't happen when you use the Windows Update/ConfigMgr method and instead, you need to either carry out a repair of the agent from the console or use your patch management tools to directly upgrade the agent.

If you follow these high-level steps, then you shouldn't have any problems with your UR deployments but from time to time, you will no doubt come across environments where you need to validate if a full and proper UR deployment has been carried out.

It's this requirement that has led me to writing up this blog post.

The Solution
During a discussion with some of the monitoring ninja's in my MVP group, the question came up about how it would be possible to validate if the SQL scripts have been executed as part of the UR upgrade process. After some initial unsuccessful tests and more than a few emails firing around our inboxes, Kevin Holman threw his two cents into the mix and as a result, he's come up with a quite simple but very effective method of determining the last time a UR SQL script was run against the Operational database.

Here's what you need to do:

Launch SQL Management Studio, connect to the instance that hosts your SCOM databases and expand down to the Table-valued Functions folder under the OperationsManager database as shown in the screenshot below.

From here, right-click the dbo.fn_AlertView function and select Properties from the resultant menu. When this opens, as shown in the screenshot below, the Created Date should indicate when SCOM was first installed.

Now close this window, right-click the dbo.fn_AlertViewChanges function and select Properties from the menu. This time, when you check the Created Date value, you will see the last date that a UR script was run against that database (shown in the following screenshot).

If this date is OLDER than the release date for the UR that you think the environment should be running, then clearly, the script hasn't been run and will need to be run again.

Conclusion
As I mentioned earlier, if you follow all of the steps outlined in Kevin Holman's UR blog posts, then you won't go wrong but if by chance you come across an environment that you or your customers are not sure if the full UR process has been completed, then this little trick from should help you determine your next steps.

All credit and thanks to Kevin Holman for pointing this out to us :)