Category Archives: Functions

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.


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

 

Find Out Who installed 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.



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

 

All the basics for running Scripts in Powershell

Basics for Running Scripts in PowerShell

Once you start creating and writing PowerShell commands, it can become quite troublesome to move them to a server or another machine, specially when the commands are long, complex, or they define functions intended to be reusable.

Let's take the following example

Below, we see a screenshot of a "one-liner" command to find the location of the ErrorLog on a SQL Server:

0-OneLiner

The Actual script looks like this:

((Get-Item -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL??*\MSSQLServer\parameters" | Select-Object -ExpandProperty property | ForEach-Object { New-Object psobject -Property @{"property"=$_; "Value" = (Get-ItemProperty -Path $RegKey -Name $_).$_}} | Where-Object {$_.Value -like "-e*"}).Value).Replace("-e","")

Wouldn't be difficult to type that in? or even to cut&paste into a console?

The obvious solution is to place them in a file. A file with a list of commands is sometimes called a script, and we'll use the term interchangeable in this article. PowerShell scripts are generally named <Verb>-<Noun>.ps1  (More Best Practices here )

Example:  Get-SQLParam.ps1

$RegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL??*\MSSQLServer\parameters"

$Param = Get-Item $RegKey | Select-Object -ExpandProperty property |
 ForEach-Object {New-Object psobject -Property @{"property"=$_; "Value" = (Get-ItemProperty -Path $RegKey -Name $_).$_}
                } 
$Param.Value

To call the file, while in the same directory, use the notation:

  .\Get-SQLParam.ps1 or . .\Get-SQLParam.ps1 , depending if you want to dot-source the file.

 

Setting Execution Policies

If you are an Administrator on a server, you should be able to run all commands on PowerShell directly, however, if you want to run commands saved on a file, you'll need to check if the execution policy allows it.

To check what is the current execution policy:

	Get-ExecutionPolicy


PowerShell has a number of execution policies that defines what files or scripts are allowed to run:

  •     Restricted: Default execution policy, does not run scripts, interactive commands only.
  •     All Signed: Runs scripts; all scripts and configuration files must be signed by a publisher that you trust; opens you to the risk of running signed (but malicious) scripts, after confirming that you trust the publisher.
  •     Remote Signed: Local scripts run without signature. Any scripts saved in an UNC path and downloaded scripts need a digital signature. Scripts saved locally are fine.
  •     Unrestricted:Runs scripts; all scripts and configuration files downloaded from communication applications such as Microsoft Outlook, Internet Explorer, Outlook Express and Windows Messenger run after confirming that you understand the file originated from the Internet; no digital signature is required; opens you to the risk of running unsigned, malicious scripts downloaded from these applications

See this link for more information regarding Execution Policies.

The default execution policy one is Restricted.  Attempting to run a script under this policy will yield an error:

File <path to the file> cannot be loaded because the execution of scripts is disabled on this system.

Changing the Execution Policy to "unrestricted" will certainly let you run all your scripts, however it will leave the system exposed because it might allow potential malware run scripts stored remotely. Strongly not recommended.

The most secure setting is "All Signed", but it's not practical when visiting one server and that's it. ( Click here to learn how to sign your scripts)

A a happy compromise regarding security is using RemoteSigned, where you can run your own scripts stored locally, but not those stored in a network location. It even allows you to run scripts on a remote system, as long as the scripts are stored local to your console.

To change the Execution Policy, run:

Set-ExecutionPolicy RemoteSigned -Force

The "-Force" parameter suppresses the confirmation screen.

In the screenshot below we see what happen when you try to run a script and the policy is set to "restricted". Next we change the policy and we are able to run our script:

1-SetPolicy

There are a number of ways to bypass the Execution Policy, but it will NOT set global variables or define functions. In other words, if you define a function or variables on a script and call it using the method below, it will not be available for the current session (the equivalent of not sourcing your script, more on that below).
You might want to bypass the policy when your script simply outputs information to the host and you do not need variables or functions later on.  In our example, we are simply outputting registry key values corresponding to the SQL Server start up parameters, so it's a good candidate to run without changing the execution policies:

	Get-Content .\Get-SQLParam.ps1 | PowerShell.exe -noprofile -
	

Please note the <dash><noprofile><space><dash> syntax.

2-ByPassExecPol

More about these methods in this link

 

Running Scripts

OK, so we are ready to run a file with commands, known as a PoweShell script.

 There are two ways of running the commands in a file. You either source the file, or you don't.

If you define a variable or function within a script, and are not interested in bringing that variable or function over to the current session, you do not need to source the script.

Per example, in the screenshot below, we first show the content of the file, and you can tell that we have stored our results, an object, in the $Param variable.

Next we run the script by typing .\Get-SQLParam.ps1  . After we execute the script, we check the value of $Param and see that it's empty.  Therefore, the object stored on $Param did not come to the our session, even though the script did output the needed information.

3-NotSourced

On the other hand, if you define a variable or function within a file, and want to bring over a variable or function, you source the script.

Per example, in the screenshot below we again first list the content of the script (we don't need to "Get-Content" every time but we do it for demonstration purposes), then run the sourced script, by typing dot-space-dot-slash-name:  . .\Get-SQLParam.ps1

After we execute the script, we check the value of $Param and see that is not empty this time: it has the value assigned within the script.

4-Sourced

Since in our script we actually defined a PowerShell Custom Object, and we are sourcing the script, we are bringing over the entire object to our current session:

5-Object

First, we show that the variable $Param is empty. Next we run a dot-sourced script. Then we show that the variable $Param is an actual PS Object.

Please note that variables defined inside a function will not be available, unless the function specifically returns such variables, as we explain below.

Running Functions defined on a Script

OK, let's transform our script into a function:

Function Get-SQLParam {
$RegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL??*\MSSQLServer\parameters"

$Param = Get-Item $RegKey | Select-Object -ExpandProperty property |
 ForEach-Object {
                New-Object psobject -Property @{“property”=$_; “Value” = (Get-ItemProperty -Path $RegKey -Name $_).$_}
                } 
$Param
}

Notice that we changed $Param.Value for $Param because we want the script to return an object, not a string (see best practices link above)

 

Below, we first list the content of the script, where you can see that now it is a function (again, no need for this step, but it's helpful for demonstration purposes)

Next we run the script, but do not source it. We will attempt to run the function defined inside the script, and we will encounter an error saying that the cmdlet is not recognized. To be expected when we do not source a script when running it.

6-FunctionNotSourced

We received the error because the function was defined inside the script but never sourced, therefore it does not exists within our session.

If we run the script again, but this time we source it, we will see that now the function is available for us to use:

7-FunctionSourced

Notice that this time we run (Get-SQLParam).Value because we are only interested in the "Value" field of the PowerShell object defined within the function.

Additionally, because the object $Param was defined inside the function, but it was not capture nor returned, then it's not available for the current session.

If you would like the entire object available for the current session, we can do the following:

8-PassingValueFunction

Here we assign the output of the Function to a variable $Results. Hence, storing the object on that variable for later use.

 

To Source or to Not Source

When is important to source a script?

If you have functions defined inside a file or create objects that you need in your current session, you must source the script.

Whenever you are running scripts with the intention to simply output information to the console, you may not source your scripts.