July 7, 2017 at 8:58 am
I'm afraid bmg002 has a point, it IS a huge DB, and its part of the WSUS product, so if you have that in your organisation, you could query that DB.
https://msdn.microsoft.com/en-us/library/bb410149%28VS.85%29.aspx?f=255&MSPPError=-2147217396
and hold that data against
Get-HotFix | sort InstalledOn -Descending
.... yeah... i know... but its a start, right?
July 7, 2017 at 9:34 am
Thank you!
It turns out that we do have an internal connection to the database where I work.
I need to check with my boss to see if if will be helpful to
412-977-3526 call/text
July 7, 2017 at 9:36 am
Adding to Theo Ekelmans's comment, I did do a little snooping to see how this could work and found this powershell module:
https://gallery.technet.microsoft.com/scriptcenter/2d191bcd-3308-4edd-9de2-88dff796b0bc
This would be using a different methodology for this than the original script. The original script was going out to the web and getting a list of the latest CU's and SP's for SQL Server and comparing those results to what you currently have installed. Using the above script, you would be looking at the local machines that you manage and generating a list of missing updates to install.
you'd install then import that module then run "get-WUlist -WindowsUpdate -ComputerName <computer name>" (which on my system was a slow process to run) and that'll tell you all of the OS patches missing on the system it was run against. If you replace "WindowsUpdate" with "MicrosoftUpdate" it will include all microsoft products such as office and SQL.
So, you'd need to run that powershell against all computers in your domain that you manage.
Downside to it is that it will return some driver update recommendataions that you may or may not want to install. But having that loop through a list of computers from a text file (or AD) shouldn't be too tricky to code up. Then take the result of that and store it in a table in SQL and you can query which machines are missing which updates pretty easily. Not sure how many computers you need to watch this on, but if the list is large, you may want to break it up into multiple lists and have multiple jobs manage it running at different times.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 7, 2017 at 9:44 am
HA!
This is what i love @sqlservercentral, how everyone shares their piece of the puzzle, and all of us learn just that bit more then you could piece together yourself 🙂
I'm going to give that a try as well bmg, thanks for the pointer !
July 7, 2017 at 9:49 am
That will be a great resource if I get the green light.
My initial use case is as follows. We connect to our customer's server to troubleshoot an issue or pull/update data.
I already have a script that I run to create an utility database and a script to check free space on the local NTFS drives. I'd just pull the current version of the KB script and run the data against the pullback of KBs. It would produce a list of what is missing and what shouldn't be there.
We actually have GUI tool to handle the update, but sites turn it off for a number of reasons.
412-977-3526 call/text
July 9, 2017 at 3:19 pm
Theo, awesome work and very nice representation of data!
You right, http://sqlserverbuilds.blogspot.ru is number one resource but it have some disadvantages:
1. Too many broken outdated links for old patches (you cant quickly it check for example for 2005 version).
2. Its maintained maintained by the mysterious TZ without any license description
3. Its doesnt have some useful fields in tables.
You can try my alternative on github with open source MIT license: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Version.md (its maintained on regular basis with checkin links and have more fields)
July 9, 2017 at 3:19 pm
Theo, awesome work and very nice representation of data!
You right, http://sqlserverbuilds.blogspot.ru is number one resource but it have some disadvantages:
1. Too many broken outdated links for old patches (you cant quickly it check for example for 2005 version).
2. Its maintained by the mysterious TZ without any license description
3. Its doesnt have some useful fields in tables.
You can try my alternative on github with open source MIT license: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Version.md (its maintained on regular basis with checkin links and have more fields)
July 10, 2017 at 1:39 am
Thanks, Koisntantin, i will take a look !
The look and feel is possible because the sqlbuilds.ekelmans.com is built in ASP.NET with Visual Studio 2017, the Telerik AJAX controls and is running on an Intel NUC at my home 🙂
July 18, 2017 at 11:30 am
This is awesome! Really good stuff Theo!!
I am a DBA supporting over 200 SQL Servers (by myself). So...I thought why can't I pull the data from my servers and compare to the data collected from Theo's scripts. Well here you go!
Prereq's:
CMS - Central Management Server
SMTP - Enable and setup
Some working knowledge of Power Shell
Steps:
1. Run Theo's Create Table Script
2. Run Theo's script to capture data from http://sqlserverbuilds.blogspot.com/
3. Run mysqlpatchlevel_tbl.txt to create table in the serverinfo database created from Theo's scripts.
4. Copy from gathermysqlpatchescms_4_ps1.txt to create powershell script.
5. Run DetermineMissingPatches_5.txt
6. Put it together! Run steps 2, 3, 4 & 5 in a sql agent job where the serverinfo db is and include sendsmtpmissingpatchemail_6.txt as your last step to send email with missing patch information. Example of output is included in zip and Theo's scripts as well.
Happy Patching!!
July 18, 2017 at 1:24 pm
Thank you Jenny 🙂
It did not occur to me to use powershell for this, i used a central linked server to check all others!
Good stuff !!
July 18, 2017 at 3:48 pm
Theo Ekelmans - Tuesday, July 18, 2017 1:24 PMThank you Jenny 🙂It did not occur to me to use powershell for this, i used a central linked server to check all others!
Good stuff !!
Going to post my the sql job tomorrow and I took the email portion of your script out since I email the results at the end of my script. I only used powershell for the CMS part. Really great work on your part to put that info together!
July 28, 2017 at 2:27 am
Great post, great thread. Very helpful.
qh
August 17, 2017 at 8:55 am
Hi All
Just a quick question. Where do I get the 2 dll files
August 21, 2017 at 2:35 am
rob.masango 2159 - Thursday, August 17, 2017 8:55 AMHi AllJust a quick question. Where do I get the 2 dll files
Hi Rob,
You can download the wget tool from https://eternallybored.org/misc/wget/.
October 27, 2017 at 5:19 am
Great solution! I have been running it in production for a couple of months now and it works well. Your suggestion to add SQL EOL date columns would be really usefull and would be a good addition. I also would like to see information about end of life information for SP and CU updates.
Regards
Michael
Viewing 15 posts - 16 through 30 (of 74 total)
You must be logged in to reply to this topic. Login to reply