Category Archives: PowerShell

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

 

All About Running commands Remotely

 

The Invoke-Command is a very powerful way to run commands remotely.

When running commands remotely, you are essentially opening a new session on the target computer and outputting the results back to the local console.

In order to run commands remotely, we need to have a list of computers, in a string form. For our examples we'll save that list on a variable $ServerList To populate the variable we can do this:

$ServerList = "Server1", "Server2", "Server3", "Server4"

If the servers are listed on a notepad (one server per line), and saved as "c:\Temp\inventory.txt", we do this:

$ServerList= Get-Content -Path "c:\Temp\inventory.txt"

We will explore four very basic techniques.

  1. Run a command remotely.
  2. Run a saved file (script or function) remotely
  3. Run a locally defined function remotely
  4. Run successive commands remotely.

If we need to display the Event Viewer and see the last 10 event for system

invoke-command -ComputerName $ServerList -ScriptBlock {Get-EventLog -LogName System -Newest 10}

If we have a file, let's say C:\PS\Get-SQLParam.ps1, and we need to run it remotely, even if the remote servers can't access your local C:\ drive (in other words, you don't need to save it on a share) we do the following:

invoke-command -ComputerName $ServerList -FilePath c:\PS\Get-SQLParam.ps1

If we have a function, Get-WhoInstalledSQL, already dot-sourced on our console, and we need to run it remotely:

invoke-command -ComputerName $ServerList -ScriptBlock ${Function:\Get-SQLParam}

If we have a series of commands to run remotely, you can get crafty with "one liners" or put them on a file… or you can open a new session, a persisted one, like this:

$s = New-PSSession -ComputerName Server01, Server02, Server03
Invoke-Command -Session $s -ScriptBlock {$p = Get-Process PowerShell}
Invoke-Command -Session $s -ScriptBlock {$p.VirtualMemorySize}

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

 

Delete files based on Age

The Get-ChildItem cmdlet is a powerful tool to search and find files on a Directory, such as old files (over certain age)

To delete all files on a directory based on age (remember to remove the -whatif safety), and filter based on extension:


# delete files older than date, recursively
# remove the whatif for execution

# define the age, in number of days.
$ts =5
# get file path to delete old files
$oldfilesPath = 'C:\Temp'
# for current directory you may use
# $oldfilesPath = '.'

# identify any type of filter. use *.* for all files
$extension = '*.bak'

Get-ChildItem -Path $oldfilesPath -Recurse -filter $extension | Where-Object {($_.LastWriteTime -lt ((Get-Date) - (New-TimeSpan -Days $ts)))} | remove-item -whatif

 

If you need to delete more than one extension (but not all of them), you would have to use this, less efficient command:


# delete files older than date, recursively
# remove the whatif for execution

# define the age, in number of days.
$ts =5
# get file path to delete old files
$oldfilesPath = "C:\temp"
# for current directory you may use
# $oldfilesPath = "."

$ManyExt = '.bak', '.trn'

Get-ChildItem -Path "$oldfilesPath" -Recurse | Where-Object {($_.Extension -in $ManyExt) -and ($_.LastWriteTime -lt ((Get-Date) - (New-TimeSpan -Days $ts)))} | remove-item -whatif

Notice that this time we filter based on extension, no wildcards needed

The above scripts will only delete files. After that operation is completed, you might need to delete empty folders

$LogPath = 'C:\temp' # replace this with your root path...
Get-ChildItem -LiteralPath $LogPath -Force -Recurse | Where-Object {
    $_.PSIsContainer -and `
    @(Get-ChildItem -LiteralPath $_.Fullname -Force -Recurse | Where { -not $_.PSIsContainer }).Count -eq 0 } |
    Remove-Item -Recurse -WhatIf

 

Searching the Event Viewer

 

Reading the Event Viewer is always useful to find error messages and poke around looking for clues to what's happening on the server.

The problem is that the interface is slow, not easy to filter results, and it's cumbersome to only see results corresponding to a specific timeframe. Once we get familiar with the powershell cmdlets, it becomes an efficient way to read the Event Viewer logs.

To read the event log, use the cmdlet Get-EventLog. Next choose if you want log you want to read using -LogName Application or -LogName System or -Logname Security

You can mix and match pretty much all of the parameters and techniques below:

  • If you want the 10 -newest events:
Get-EventLog -LogName Application -Newest 10
  • To actually read the messages, pipe the output to a Formatted list, and use the -Property to select what to see (use wild cards to see them all):
Get-EventLog -LogName Application -Newest 10 | Format-List -Property EntryType, Message, Source, TimeWritten
  • If you have too many results on your screen, you can use Out-Host -Paging, very similar to the "more" command in a Unix shell, to "page out" through your results
Get-EventLog -LogName Application -Newest 100 | Out-Host -Paging

 

Now that you can display your logs, we'll work on filtering the results:

  • The first set of useful filter is the -BEFORE and -AFTER parameters. Per example if you want the events between two dates:
Get-EVentLog -LogName Application -Before '02/22/2016 15:00' -After '02/22/2016 12:00'
  • I really like to use -BEFORE with -NEWEST, so I can get "all 10 previous events prior to the date in BEFORE".
Get-EVentLog -LogName Application -Before '02/22/2016 15:00' -Newest 10
  • The -EntryType is useful to only see certain type. It accepts entries such as Error, Information, FailureAudit, SuccessAudit, and Warning. For Example, if you want to see the entries, but not having to sift through the "Login Successful" entries, you can specify what to you want to see:
Get-EventLog -LogName Application -Newest 20 -EntryType 'Error','Information','FailureAudit','Warning'
  • To list events only from a specific -source, like MSSQLSERVER$
Get-EventLog -LogName Application -Newest 20 -EntryType 'Error','Information','FailureAudit','Warning' -Source MSSQL*
  • To search for a string, like "error" within the -message:
Get-EventLog -LogName Application -Newest 20 -EntryType 'Error','Information','FailureAudit','Warning' -Message '*error*'
  • However, if you want to search and filter out some strings, you will need to pipe the objects to a filter using where-Object
Get-EventLog -LogName Application -EntryType 'Error','Information','FailureAudit','Warning' -Newe
st 20 | Where-Object {$_.Message -notlike "Security policies*" -and $_.Message -notlike "Error reading*"}

  • Lastly, since most servers clear or recycle the event log, It's always important to know what's the earliest event recorded… and you know that by running:
Get-EventLog -LogName System -message '*cleared.'

or you can also filter based on source:

Get-EventLog -LogName System -Soruce '*Event'

Finding Location of ErrorLog

Note: While this script will find the errorlog on all servers with a single SQL instance, it requires Powershell version 3 when multiple versions are present (looking into fixing it). We cannot use the #Require -Version parameter because it's only available on versions 4 and higher.

 

Finding the Location of the ErrorLog on a server can be done by reading the start up parameters of SQL.

The start up parameters  are stored in the Registry, however the location can change based on the version of SQL server, whether there is a default instance or named instance, or even if there are many SQL instances present on the server.  However, we know that parameters are stored inside:

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

1-Registry

Once we find the exact Registry location, we need to consider that there maybe more than three entries there, depending on the number of start up parameters, which can be affected by the presence of trace flags, so we would want to return all the SQLArg entries.

The following commands return a PS Object with the Registry information:

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

# Stores a list of the SQLArg Registry Keys
$Param1 = Get-Item $RegKey | Select-Object -ExpandProperty property

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

2-Objet_Errorlog

If we want to filter down and only get the ErrorLog information, we would run the following instead:

# Searches for ALL SQL servers installed on machine
$RegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL??*\MSSQLServer\parameters"

#  -e for errorlog.  -d for master file
$Filter = "-e"

# Stores a list of the SQLArg Registry Keys
$Param1 = Get-Item $RegKey | Select-Object -ExpandProperty property
# Creates an Object, and filters those starting with -e
# Finally, removes -e from the string
$Param2 = $Param1 | ForEach-Object { New-Object psobject -Property @{"property"=$_; "Value" = (Get-ItemProperty -Path $RegKey -Name $_).$_}} 


IF ( (($param1 | Where-Object {$_ -eq "SQLArg0"}).count -gt 1) -and ($PSVersionTable.PSVersion.Major -lt 3) )

{ 
write-host "sorry, not supported" 
}

ELSE
{

(($Param2 | Where-Object {$_.Value -like ($Filter+"*") }).Value).Replace($Filter,"")
}



3-Just_Errorlog

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.