Category Archives: Basic One Liners

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}

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"


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 $_).$_}} 


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" 


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


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:


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 $_).$_}

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:


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:


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.


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.


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.


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:


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 $_).$_}

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.


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:


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:


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.