How should I design a database to record MS patch info?

By phil.bowers ·
I have a script that periodically runs on our company PCs and uses WMI to check what MS hotfixes are installed. I would like to turn this info into a useful little tool to monitor our patching success/progress, with the idea being that I could get lists of all the PCs missing a given list of patches, or all the patches installed/missing from a given PC, as well as some nice summary figures for the boss.

Since there are a lot of MS patches for XP, Office etc and we have a few thousand PCs a simple sorted Excel spreadsheet isn't going to do the job and so I thought I should get the data into a database (probably SQL Server Express), but I'm not sure how to design it. I don't have a lot of experience at designing databases, just dabbling at reading info from them, so all I can think of is a large single table that has a row for each PC and a column for each patch with a true/false for each bit of data.

This approach just strikes me as being too simplistic to be an efficient way of doing things so I was wondering if anyone had ever done something similar before or had any thoughts? Am I on the right lines or showing my ignorance?!

Any comments appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by retro77 In reply to How should I design a dat ...

Why not just install WSUS? Its free and it does all that for you.

More info:

Collapse -


by phil.bowers In reply to WSUS

Hi retro77. Funny you should say that. We are planning to move to WSUS for our patch distribution (currently using a creaking SMS2) but from what we've seen of it so far the reporting didn't seem that good or flexible. We only have a virtual WSUS with 2 or 3 clients at the moment so maybe we don't have enough data to really get to grips with it. I did have in mind that with the database I described we could validate how well WSUS was working, but maybe I'm making life more difficult than it needs to be! I will spend a bit more time looking at WSUS. Thanks for your suggestion.

Collapse -


by retro77 In reply to RE: WSUS

Well WSUS writes to a DB so you might be able to design a webpage to pull the data you want. Also in this day and age, I just want to go in, approve the updates and at 3:00am, while I'm sleeping or playing Guild Wars, all the computers are updating. Its painless and I never bother with reports.

I've got other stuff to do, like post here, than worry about reports on updates. Then again my philosophy is to touch it once, automate it, never touch it again. My backups are the same way, I get an email if it failed, my AV updates are the same way. Automagic!

edit: thanks for the thumbs up!

Collapse -


by phil.bowers In reply to Database

Ah how I dream of the 'set it and forget it' IT world you describe! And that is exactly the way it should be done if you can. Unfortunately in our place nobody worries about reports until something goes wrong and the boss is asked for evidence that we have been doing our jobs. Then we are supposed to have anticipated the need to prove this and have the analysis ready. I'm just learning from experience here!

Collapse -

Love it!

by retro77 In reply to Nirvana

I like the freedom to be able to do other things, like post on here! Haha. Also a spot check here and there never hurts.

Good luck in your WSUS deployment! You have the setting in a GPO to tell the client what WSUS server to go to right?

Collapse -


by rahouseholder In reply to How should I design a dat ...

There is an product called Harris stat scanner that is capable of scanning all the computers on your network and give you the reports your are looking for. I do not know what the cost involved is, but you can look at it and then search for comparable products.

Related Discussions

Related Forums