Find Out Who Patched SQL


One important information to have is finding out when SQL was installed, and by whom.

This information is stored on the Setup Boot strap. If those files are available, we can find out this information using the function below.

No parameters needed. If you need to run this against multiple servers, see this article.

< #
   Searches all Summary.txt file and read their information
   Searches all Summary.txt file and read their information. It will list all instances on each summary.txt file
   If no patch ever, it'll ignore it

   Name              Value                                                                                                      
----                 ----                                                                                                        
1 Install Date     6/19/2015 12:05:16 PM                                                                                        
1 Instance 1       STAGING           ENTERPRISE                                                                              
1 Package Name     SQLServer2008R2-KB2630458-x64.exe                                                                           
1 Requested Action   Patch                                                                                                        
1 UserName           mquintana                                                                                                    
2 Install Date       6/19/2015 11:39:34 AM                                                                                        
2 Instance 1         MSSQLSERVER          Standard                                                                                
2 Package Name       SQLServer2012-KB2976982-x64.exe                                                                              
2 Requested Action   Patch                                                                                                        
2 UserName           mquintana 

   Another example of how to use this cmdlet
function Get-WhoPatchedSQL
    Param ( )

    Begin {  }
$Root = "C:\Program Files\Microsoft SQL Server\"
$File=Get-ChildItem -Recurse -Path $Root -Filter Summary.txt | select-String -Pattern "patch" | group path | select name

# $Results = @{}  Use this for Powershell v2.0
$Results =[ordered] @{}
ForEach ($SummaryFile in $File)

# Get information from summary text file

$Time      = Get-Content $SummaryFile.Name | select-string -pattern "Start time:" | Select -First 1
$RulesFile = Get-Content $SummaryFile.Name | Select-String -Pattern "Rules report file:"
$Action    = Get-Content $SummaryFile.Name | Select-String -Pattern "Requested action:" 
$Package   = Get-Content $SummaryFile.Name | Select-String -Pattern "PackageName:" | Select -First 1

# Finds the Rules Report file folder, and seek within that folder the location of install log
# next, looks inside file for install user
$InstallPath = Split-Path (($Rulesfile -split ("Rules report file:") | Select-Object -Last 1).Trim())

$InstallLog=Get-ChildItem ($InstallPath+"\*\sql_engine_core_inst_Cpu??_1.log")

# Get the user who install it, from the log file
$User=Get-Content $InstallLog  | Select-String -pattern "C:\\Users\\" | Select-Object -First 1

########### process the data #########

$UserName    =         (($User -split ("C:\\Users\\")       | select-object -last 1).split("\\") | Select-Object -First 1)

$InstallDate = Get-Date ($Time -split ("Start time:")       | Select-Object -Last 1).trim()

$RequestedAction =    ($Action -split ("Requested action:") | Select-Object -Last 1).trim()

$PackageName =       ($Package -split ("PackageName:")      | Select-Object -Last 1).trim()

# Gather results and display them

$a = Get-Content $SummaryFile.Name | Select-String -pattern "Updated product edition:"
$b = Get-Content $SummaryFile.Name | Select-String -Pattern "User Input Settings:"

# The information we are looking for is between lines $a and $b
# Which we save on $InstData, and add to our results object

$InstData = (Get-Content $SummaryFile.Name)[($a.LineNumber+1)..($b.LineNumber-3)]

# We add the info to the hash table

For ($i=0;$i -lt ($b.LineNumber-3 - $a.LineNumber+1)-1;$i=$i+1)
$Results += @{"$j Instance $($i+1)"= $Instdata[$i].trim() }

$Results+= @{"$j UserName"         = $UserName
            "$j Install Date"     = $InstallDate
            "$j Requested Action" = $RequestedAction
            "$j Package Name"     = $PackageName}


$Results.GetEnumerator() | Sort -Property Name



# Get-WhoPatchedSQL


Leave a Reply

Your email address will not be published. Required fields are marked *