Commonly Used Powershell Commands

Since the most common questions I get are usually for the “most commonly used” commands I thought we could put them in one single page. I have used and continue to use these commands for various uses such as Administration or just collecting metrics for reporting.

Note: We will be adding to this page periodically when commands are found to be used more often than others.

As always you should review, and get used to reading, the Microsoft official documentation on these Powershell commands and the module release history.

Cls   ## Clear the screen
Clear
Clear-History

Writing To The ISE

Producing an output may be consfusing as the Powershell ISE will format things like objects differently when there are many properties. One value is simple enough to understand but in an object you will need to pull one value out and ignore the rest or pass it to another command. The Write-Host commandlet displays strings and variables a little different but I use it all the time to make the output more readable.

Write-Host "*** Just Displaying Some Characters ***"
Write-Host "*** Adding 'new line' switch, `n 2 lines ***"

Note: The new line switch ( `n ) is using a back tick, not a single quote.

$Var = "MyServer"
Write-Host "`r---[ The server is called $Var ]---`r"

Note: Notice the return (r) instead of new line (n) and there are no plus symbols (+) for concatenating the string.

Modules and Security

“RemoteSigned” is meant to protect the current machine from running an unsigned script from a remote computer. The local policy, which is your workstation, should be set to “RemoteSigned” unless you will be running scripts from another computer on this workstation.

In most cases you will be running commands remotely on servers and doing something with the data. Global policy and local policies can restrict changes to these policies too so understand your company policy before changing things. Most Administrators will use Unrestricted but this might not be advisable for your network security.

Use the following commands to check the policy and change it.

Get-ExecutionPolicy   ## Get this computers policy
Set-ExecutionPolicy Unrestricted -Force

Modules

Another important command to understand is the “import-module” command that can bring in modules when they are not imported by default in your Powershell version. We use the Invoke-Sqlcmd often to connect and run queries but it may not be available and you end up with “invoke-sqlcmd is not recognized”. We can use Get-Command to find out what

Get-Command  ##  returns all commands
Get-Command -Listimported  ## All commands in current session
Get-Command Invoke-Sqlcmd  ## details on the commandlet
Get-Command Select-Object

Helping with the command syntax..

Get-Help Get-ExecutionPolicy
Get-Help Get-Command   ## Details on the command and syntax
Get-Help Get-Command -online  ## opens online help
Get-Help Invoke-Sqlcmd
Get-Help Select-Object

System and Service Commands

The average Admin day is filled with logging in and checking resources, security, logs and troubleshooting issues. These commands can be executed on the servers or remotely in an automated script to achieve the same.

Services

Get-Service   ## All services
Get-Service -ComputerName ServeName
Get-Service  -Name "defragsvc"  # only one service
Get-Service -ComputerName ServeName -Name "defragsvc"

## Get all running services
Get-Service | Where-Object {$_.Status -eq "Running"}

Where-Object and Get-Process Commands

In that last example we filtered the services by using the Where-Object commandlet and choosing a field to filter by. This command is important to understand as we use it for filtering results as well as comparisons. We will use the Get-Process for some examples.

Get-Process  ## All processes and the CPU usage
Get-Process -ComputerName ServerName -Name "Notepad" 
Get-Process | Where-Object {$_.CPU -gt .01}
Get-Process | Where-Object {$_.CPU -lt 10.1}
Get-Process | Where-Object {$_.Name -eq "Notepad"}
Stop-Process -id 4276 

Get-Service | Where-Object {$_.DisplayName -like "Blue*"}
Get-Service | Where-Object {$_.DisplayName -notlike "Blue*"}

Start-Service -Name "xbgm"   ## May need to be Admin
Stop-Service -Name "xbgm"

The Where-Object has certain abbreviated filters and we only used a few common ones here. Be sure to check out all of the Where-Object filters and see what works best. We used the following above…

-gt	Greater Than
-lt	Less Than
-like	Matching Like and using the * as a wild card
-notlike	Matching Like and wild card but returning the opposite
-eq	Equal To

Get Logged on User & Environment

There are some default environment variables that can be used in your scripts when you need the current logged on user or the workstation name. These variables are related to the current environment so they can be changed too. Be sure to consider this when writing scripts for another machine or server.

$PSVersionTable
$env:UserName
$env:UserDomain
$env:USERPROFILE
$env:ComputerName
$env:JAVA_HOME
$env:NUMBER_OF_PROCESSORS
$env:windir
whoami   ## may not be installed

[Environment]::UserName
[Environment]::CurrentDirectory
[Environment]::MachineName
[Environment]::OSVersion
[Environment]::UserDomainName
[Environment]::SystemDirectory
[Environment]::SystemPageSize

Run Command On A Remote Computer

“Invoke-Command” allows you to execute code on the local or a remote server. This means that you can execute from a central location and connect to servers in your environment to return results from each. First you may need to get the server names, IP addresses or node names to connect.

Resolve-DnsName "10.11.22.101"

Invoke-Command -ScriptBlock { hostname } -ComputerName "ServerName"

Invoke-Command -ScriptBlock { Get-NetIPAddress } -ComputerName "ServerName"

Listing Files

Get-ChildItem -Path "C:\"  ## local

## execute this on a remote server
Invoke-command -ComputerName MyServerName -ScriptBlock {Get-ChildItem -Path "C:\"}

## Display the file contents
Get-Content -Path "C:\temp\mytextfile.txt"

Get Current Date and Time Using Get-Date

Get-Date                               ## current date and time
(Get-Date).Date                        ## Date with time set to midnight
(Get-Date).DayOfYear
(Get-Date).AddDays(23)                 ## Add days
(Get-Date).AddDays(-15)                ## Subtract Days
(Get-Date).ToUniversalTime()           ## Get UTC Date and Time

Get-Date | Select-Object DayofWeek
Get-Date -Format yyyymmdd

Powershell Connect To SQL Server

Similarly “Invoke-Sqlcmd” can execute SQL specific code on multiple SQL servers and return the results. We use this to collect data on our SQL environments or run queries and statements and centralize it for reporting or metrics.

Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('MachineName')" -ServerInstance "Server\Instance"

Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('ProductVersion')" -ServerInstance "Server\Instance"

Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('Edition')" -ServerInstance "Server\Instance"

Run A Query

We can also pass more complicated SQL statements with parameters using powershell variables. This is a statement that will return all of the user databases and exclude the system DB’s.

$systemdb = "'master','model','msdb','tempdb'"

$myquery = " SELECT name FROM master.sys.databases
WHERE name NOT IN (" + $systemdb + ");"

Invoke-Sqlcmd -Query $myquery -ServerInstance "Server\sqlinstance"

Create a DataTable

$Datatable = New-Object System.Data.DataTable

Add Column

$Datatable.Columns.Add("Column1") 
$Datatable.Columns.Add("Column2")
$Datatable.Columns.Add("Column3")

Remove Column

$Datatable.Columns.Remove("Column2")
$Datatable.Columns.Remove("Column3")

Copy Datatable

It’s not necessary to create a new Datatable, this will copy the object, schema and data.

$newTable = $Datatable.Copy()

Merge Datatables

$Datatable.Merge($newTable)

Loading The Datatable Using Foreach and Invoke-Sqlcmd

$results = Invoke-Sqlcmd -query "SELECT column, column2" -ServerInstance ServerName\SQLInstance01

foreach ($record in $results) { 
     $row = $Datatable.NewRow()
        $row.column1 = $record.column1
        $row.column2 = $record.column2
     $Datatable.Rows.Add($Row)
}

Azure – Install AZ Tools

Review the install instructions on Microsoft website for Powershell Az. With Powershell 7 you will use VS Code instead of ISE.
You may need to start VS Code as an Administrator.

$PSVersionTable.PSVersion  ##  Check your version

Get-ExecutionPolicy -List

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser  ## or use Unrestricted

Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Verbose  ## takes time, use Verbose

Ubuntu and VS Code – Install Az

For Ubuntu, the repository may not be configured and the PSGallery module is not installed. Keep in mind that VS Code will let you install the Powershell Extensions even if PS is not installed in Ubuntu yet.

Get-ExecutionPolicy

Register-PSRepository -default

Get-repostitory  ## should show PSGallery now

Install-Module -Name Az -Repository PSGallery -Verbose   ### install the Azure module

Connect-AzAccount   ## auth via browser

Connect-AzAccount -TenantId c2b3a45e-5111-41ce-99f2-331123456  ## may TenantID need under Ubuntu

---