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.

< #
.Synopsis
   Searches all Summary.txt file and read their information
.DESCRIPTION
   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
.EXAMPLE
   Get-WhoPatchSQL

   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 

.EXAMPLE
   Another example of how to use this cmdlet
#>
function Get-WhoPatchedSQL
{
    [CmdletBinding()]
    Param ( )

    Begin {  }
    Process
    {
    
$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] @{}
$j=1
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
#
#  MSSQLSERVER  ENTERPRISE
#  SQLINST1     STANDARD
# 

$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}


$j=$j+1
}

$Results.GetEnumerator() | Sort -Property Name



    }

}

# Get-WhoPatchedSQL

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.

function Get-WhoInstalledSQL {
<#
.SYNOPSIS

.DESCRIPTION

.PARAMETER

.EXAMPLE

#>

[Cmdletbinding()]
    Param (
          
    ) 

       PROCESS {

        $r           = @()
        $UserName    = @()
        $InstanceName= @()
        $CreationTime= @()   
        $computername=$env:COMPUTERNAME

# $rootfolder
$r = "C:\Program Files\Microsoft SQL Server","C:\Program Files (x86)\Microsoft SQL Server"

# Finds all configurationfile.ini

$InstallFile = (Get-ChildItem -Recurse -path $r -include "configurationfile.ini" ) #| select fullname).fullname

# Test for existence of INI file, if empty we are done.
if (!$InstallFile) { write-Warning " The Setup Bootstrap folder does not have this info (no INI files)" }

# this ELSE essentially exits the function
ELSE {   # else 1

    $listconfig = @()

    FOREACH ($configfile in $InstallFile)
    {
    # Checks each file for two conditions. If met, add it to the array
    $Cond1 = get-content $configFile | Select-String -pattern "ACTION=`"Install*"
    $Cond2 = get-content $configFile | Select-String -pattern "FEATURES=SQLENGINE"

    if ($cond1 -and $cond2) {$listconfig += $configfile }
    }

    # Test that if we have INI file, it is for INSTALL actions.  if empty we are done.
    if (!$listconfig) {
      write-Warning " The Setup Bootstrap folder does not have this info (config found, but no install INI)" 
    } # end FOREACH
    
# this ELSE essentially exits the function
    ELSE {  # else 2

        # At this point, we have the configurationFile.ini 
        # There are log files at this folder level who keep information of the username used at the time of installation
        # The log for the engine is sql_engine_core_inst_cpu??_1.log. 
        # Where it can be 32 or 64 bits
        # Assuming that many config files could be found
        # But the FOrwill execute only for one , the first one found

        for ($i=0;$i -lt $listconfig.length;$i++)
        {
            $InstallPath = split-path $listconfig[$i] -parent
            $installLog = Get-ChildItem -Recurse -path $InstallPath -include "sql_engine_core_inst_Cpu??_1.log"  | sort LastWriteTime | select -First 1
            # Ok we got the file, do we have a match?
            # We do this check to make sure we have the right file, and it hasn't been deleted/modified
            # IF we do not find it, we cant know who installed it, we simply say that user not found
       
            if(!$installLog) { $UserName= "Not_Found" }
            
            ELSE {
                    # Get the user who install it, from the log file
                    $Line=Get-Content $installLog  | Select-String -pattern "C:\\Users\\" | Select-Object -First 1
                    $UserName += (($Line -split("C:\\Users\\")  | select-object -last 1).split("\\") | Select-Object -First 1)
                 }
        
           # SQL Instance Name from INI file
            $InstanceName +=(Get-Content $listconfig[$i] | Select-String -pattern "INSTANCEID=") -replace "INSTANCEID=","" -replace "`"",""
            
           # Get CreationTime attribute  from INI file
            $CreationTime +=(Get-ChildItem $listconfig[$i] | select creationtime).creationtime
         } # end for


    }  # closing the ELSE 1 statement  
    }  # closing the ELSE 2 statement

# Let's tally up our findings in a PSObject

for ($i=0;$i -lt $InstanceName.length;$i++)
{
 New-Object PSObject -Property @{
            "ComputerName"=$computername
            "InstanceName"=$InstanceName[$i]
            "CreationTime"=$CreationTime[$i]
            "UserName"    =$UserName[$i] 
            }
}

   $table

    }     # end process

}
# Get-WhoInstalledSQL | Format-List