Download SQL Patch Info

  • 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?

  • 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 IM Renderer Page have a script that you could run to quickly determine which updates were missing by comparing the output of systeminfo.exe and the list of approved KBs for the OS.

    412-977-3526 call/text

  • 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.

  • 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 !

  • 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

  • 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)

  • 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)

  • 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 🙂

  • 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!!

  • 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 !!

  • Theo Ekelmans - Tuesday, July 18, 2017 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 !!

    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!

  • Great post, great thread.  Very helpful.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Hi All

    Just a quick question. Where do I get the 2 dll files

  • rob.masango 2159 - Thursday, August 17, 2017 8:55 AM

    Hi All

    Just 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/.

  • 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 75 total)

You must be logged in to reply to this topic. Login to reply