November 4, 2002 at 9:04 am
How can i get a listing of all the patches that are installed on SQL Server.
Thanks
November 4, 2002 at 10:53 am
You might take a look at either HFNetChk or the Microsoft Baseline Security Analyzer, both available from Microsoft's Security site at http://www.microsoft.com/security/ and they should give you the info you need. HFNetChk is command-line while MBSA has a GUI interface. MBSA can also check for weak passwords and the like. Both are built on technology from Shavlik, and so you can the more robust versions of the HFNetChk package at http://www.shavlik.com/
HTH.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 4, 2002 at 10:57 am
Be careful of the MSBSA, has some bugs.
Not sure of the best way. Still searching for a good one here. NGSSoftware has one that checks for vulnerabilities, but doesn't show patches compared to the MS database.
Steve Jones
November 4, 2002 at 2:39 pm
Take a look at UpdateExpert - (St. Bernards Software) if you need to manage patching on multiple servers
Tim
November 4, 2002 at 3:50 pm
The big problem that I see with most of these is the way MS does their patching sometimes. First MS has three types of general patches, single patches (mostly vuneralbilities and big bugs), cumulative patches (bunch of the littles ones together), and the Service Packs (the cumulative patches but minor bug fixes and tweaks to problem areas). Most apps use registry keys to validate the files installed, the problem is if another install stepped on a file but not the key entry, occasionally (but more often than not) the files work fine but the bug exists again. Some verify the file versions as well, and these are far stronger products, not sure who does this anymore thou and they get pricey soemtimes.
Now HFNetChk I have been using for awhile now. I have not run into any issues other than versioning that has occurred with bug fixes that were reported improperly in HFNetChk (file there but I had a newer version someone forgot to include as covering the fix or some other issue like that). Best of all it is free and not too complicated to use if you are used to command line interfaces.
Edited by - antares686 on 11/04/2002 3:51:49 PM
July 5, 2015 at 6:35 pm
If you need to do this with a SQL query, you can use xp_cmdshell to run the appropriate wmic command. To clean it up for querying, dump the results into a temporary table, create a second temporary table to clean up the text into a couple columns, and you will be able to view the KB numbers for the patches installed on that machine. Unfortunately, to identify the patches as belonging to SQL Server, you'd need to create and populate another table with a list of existing SQL Server patches, then cross-reference with joins.
Here's some SQL code to get you started...
First, create a temporary table (named #patchtemp in this example) with one column (named "results" in this example). This temp table will allow you to dump the raw results from a windows command ('wmic qfe list brief' for these purposes)...
create table #patchtemp
(
results varchar(1000)
)
Next, run this insert statement to add to this first temporary table, the results of "exec xp_cmdshell 'wmic qfe list brief'". xp_cmdshell procedure will run this command, which is a windows command line WMI query that will return a list of installed patches on the host server.
insert into #patchtemp (results)
exec xp_cmdshell 'wmic qfe list brief';
Now create a second temporary table (named #patch in this example), so you can clean up the results and store only the patch type and KB number...
create table #patch
(
ptype varchar(25),
pnum varchar(10)
)
Then, run an insert statement on #patch temp table to pull in the raw command line results from the first temp table, #patchtemp, clean the text up a bit and break the rows up into relevant strings to represent the patch type, and KB number.
insert into #patch (ptype,pnum)
select left(results,charindex(' ',results)-1),
ltrim(rtrim(substring(results,charindex('KB',results),9) ))
from #patchtemp
where results like '%KB%';
Now you can query the #patch temp table for a list of patches installed on the server.
select * from #patch;
Identifying the product to which a given patch applies will take a bit more work in the way of cross-referencing, in whatever way you come up with. Good news, though, is this will give you a foundation for listing installed patches with a SQL query. Not sure if that's quite what you're looking for, but it has helped me with a particular solution in the past, so might be worth messing with. Hope this helps, if only a little.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply