Saturday, August 20, 2016

PowerShell - Send Email

# Send email
Send-MailMessage -SmtpServer "smtp.corp.org" -to "you@yourcorp.net" -from "powershell@hostname.domain.corp.org" -subject "Test PowerShell email 1" -body "This is my body"

# Send email with an attachment
$body = "This is the long-winded message."
$file = "c:\temp\file.txt"

Send-MailMessage -SmtpServer "smtp.corp.org" -to "you@yourcorp.net" -from "powershell@hostname.domain.corp.org" -subject "Test PowerShell email 2" -body $body -Attachments $file

PowerShell - Read the Event Log

# Retrieve ten most recent events in the system log
Get-EventLog -LogName System -Newest 10 -ComputerName "changeme"

# Retrieve entries from today
Get-EventLog -LogName System -ComputerName "changeme" -After ( [datetime]::Today )

# Retrieve entries from today and yesterday
Get-EventLog -LogName System -ComputerName "changeme" -After ( [datetime]::Today.AddDays(-1) )

# Another way
$time = [datetime]::Today.AddDays(-1)
Get-WinEvent –FilterHashtable @{logname=’system’; starttime=$time } –MaxEvents 10 -ComputerName "changeme"

# Now store events in an array

$time = [datetime]::Today.AddDays(-1)
$array = Get-WinEvent –FilterHashtable @{logname=’system’,'application'; starttime=$time } –MaxEvents 50 -ComputerName "changeme" 

Write-Host $('-' * 70)

# Display the first five events captured
$array[1..5]

PowerShell - Read and Write Text Files

# Write, append, and read files

# First set up some variables
$filename = "c:\temp\file.txt"
$myText = "Hello World"
$mySecondLine = "Goodbye World"
$myThirdLine = "My third line"
$myFourthLine = "My fourth line"

# Create file
$myText > $filename

# Another way
$myText | Set-Content $filename

# Another way
$myText | Out-File $filename

#################################

# Append to file
$mySecondLine >> $filename

# Another way
$myThirdLine | Add-Content $filename

# Another way
$myFourthLine | Out-File $filename -Append

#################################

# Read content

$myVar = Get-Content $filename

# Dashes
Write-Host $('-' * 70)

# Display content
$myVar

# Display first line
Write-Host $('-' * 70)
$myVar[0]

# Display second line
Write-Host $('-' * 70)
$myVar[1]

# Display first three lines
Write-Host $('-' * 70)
$myVar[0..2]

# Display the last line
Write-Host $('-' * 70)
$myVar[-1]

# Display the last three lines
Write-Host $('-' * 70)
$myVar[-1..-3]

Friday, August 19, 2016

PowerShell - Manipulate files

# Declare variables
$timestamp = Get-Date -format "yyyy-mm-dd_HH-mm-ss"

$path = "c:\temp"

$filename = $timestamp + ".txt"
$fullFilename = $path + "\" + $filename

$fullFilename

# Create a file
New-Item -Path $path -Name $filename -ItemType "File"

# Does file exist?
Write-Host "Does file $fullFilename exist?"
Test-Path $fullFilename

# Remove it
rm $fullFilename

# Now check again
# Does file exist?
Write-Host "Does file $fullFilename still exist?"
Test-Path $fullFilename

# Re-create the file so we can play with it
New-Item -Path $path -Name $filename -ItemType "File"

# Add text to file
Add-Content $fullFilename "Hello World"

# Copy the file
$newFilename = $timestamp + ".new"
$newFullFilename = $path + "\" + $newFilename

Copy-Item $fullFilename $newFullFilename

# Remove the new file
rm $newFullFilename

# Move the file
Move-Item $fullFilename $newFullFilename

# Read the file
$stuff = Get-Content $newFullFilename

# Displays 'Hello World' (the content of the file)
Write-Host $stuff

rm $newFullFilename

PowerShell - Manipulate folders

# Creates a timestamp such as 2016-08-19_21-32-45 (August 19, 2016 at 9:32:45 PM)
$timestamp = $(get-date -f yyyy-MM-dd_HH-mm-ss)

$foldername = "c:\temp\" + $timestamp

mkdir $foldername

# Remove a folder
rmdir -Recurse $foldername

# Creates some subdirectories

mkdir "$foldername\child1"
mkdir "$foldername\child2"
mkdir "$foldername\child3"

mkdir "$foldername\child1\grandchildA"
mkdir "$foldername\child1\grandchildB"

# Dashes
Write-Host $('-' * 70)

# Retrieve subdirectories
Get-ChildItem $foldername | Where-Object PSIsContainer

# Dashes
Write-Host $('-' * 70)

# Check to see if the folder exists
Write-Host "Does $foldername exist:"
Test-Path $foldername

$bustedFolder = "c:\temp\banana"
Write-Host "Does $bustedFolder exist:"
Test-Path $bustedFolder

# Copy a folder
Copy-Item "$foldername\child1\grandchildA" "$foldername\child2\grandchildC" -Force

# Move a folder
Move-Item "$foldername\child2\grandchildC" "$foldername\child3"

# Cleanup
rmdir -Recurse $foldername

PowerShell - Display SQL Agent Jobs

Import-Module SQLPS -DisableNameChecking

$instanceName = "server2"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$jobs = $server.JobServer.Jobs

$jobs | Select-Object Name, OwnerLoginName, LastRunDate, LastRunOutcome | Sort-Object -Property Name | Format-Table -AutoSize

PowerShell - Drive size and free space

$servers = @("server1", "server2")
$myDrives = @()

Get-WmiObject Win32_Volume -ComputerName $servers -credential domain\username | ForEach-Object {

    $drive = $_

    $item = [PSCustomObject] @{

        Server = $drive.SystemName
        Name = $drive.Name
        DeviceType = switch ($drive.DriveType)
        {

            0 {"Unknown"}
            1 {"No Root Directory"}
            2 {"Removable Disk"}
            3 {"Local Disk"}
            4 {"Network Drive"}
            5 {"Compact Disk"}
            6 {"RAM"}
        }
        SizeGB = "{0:N2}" -f ($drive.Capacity/1GB)
        FreeSpaceGB = "{0:N2}" -f ($drive.FreeSpace/1GB)

    }

    # I only want to look at local disks (DriveType 3)
    # I also want to weed out tiny boot drives less than 1 GB

    if (($drive.DriveType -eq 3 ) -and ($drive.Capacity -gt 1000000000) )
    {
        $myDrives += $item
    }



}

# Dashes
Write-Host $('-' * 70)

$myDrives | Format-Table -AutoSize

PowerShell - List databases and check database backup times

# Import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Connect to the server
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$folder = "c:\temp"
$currdate = Get-Date -Format "yyyy-MM-dd_HHmm"
$filename = "$($instanceName)_db_$($currdate).csv"
$fullpath = Join-Path $folder $filename

# Create a dynamic array
$result = @()

# Go through each database and build the result set
$server.Databases | ForEach-Object {
    
    $db = $_
    
    $item = [PSCustomObject] @{
        DatabaseName = $db.Name
        RecoveryModel = $db.RecoveryModel
        LastBackupDate = $db.LastBackupDate
        LastLogBackupDate = $db.LastLogBackupDate
    }

    $result += $item

}

# Dashes
Write-Host $('-' * 70)


# Display result
$result | Format-Table -AutoSize


# Export result to CSV
$result | Export-Csv -Path $fullpath -NoTypeInformation

# Fires off Excel and displays the csv file
explorer $fullpath


############################################################################

# This approach is more concise:

$instanceName = "beuscbetlsql"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$server.Databases | Select-Object Name, RecoveryModel, LastBackupDate, LastLogBackupDate | Format-Table -AutoSize



PowerShell - Execute a SQL script file

# Import SQL Server module
Import-Module SQLPS -DisableNameChecking

# Not convinced this is needed
# Connect to the server
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName



# Not convinced this is needed
# Connect to the database
$dbName = "AdventureWorks2012"
$db = $server.Databases[$dbName]

# Run the query
Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName | Select-Object FirstName, LastName, ModifiedDate | Format-Table

# Dashes
Write-Host $('-' * 70)

# Capture the output in an array and display the second record
$output = Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName 
$output[1]

# Capture the output to a csv file
Invoke-Sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "$instanceName" -Database $dbName | Select-Object FirstName, LastName, ModifiedDate | Export-Csv -LiteralPath "c:\temp\output.csv" -NoTypeInformation

PowerShell - Functions

# Displays Hello World
function displayHello
{
    Write-Host "Hello World"
}
displayHello

# Pass a parameter
function displaySomething
{
    Write-Host $args
}
displaySomething 'Hello Again'

# Working with multiple parameters
function addNumbers
{
    Write-Host ($args[0] + $args[1])
}
# Displays 7
addNumbers 3 4

# Fancier way to work with multiple parameters
function AddNumbersAgain
{
    param( [int]$num1, [int]$num2 )
    write-host ($num1 + $num2)
}
# Displays 9
AddNumbersAgain 4 5


Thursday, August 18, 2016

PowerShell - Format Commands

# List all processes
Get-Process

# Display 70 dashes so it makes a line
# (This makes it easier to see different output)
Write-Host $('-' * 70)

# Show only SQL processes
Get-Process -Name *sql*

# Dashes
Write-Host $('-' * 70)

# Looks pretty much the same but with column headers
Get-Process -Name *sql* | Format-Table

# Unfortunately this truncates the output
Get-Process -Name *sql* | Format-Table -Property Path, Name, Id, Company

# Better because we can now see the Path but depending on console size Company is still truncated
Get-Process -Name *sql* | Format-Table -Property Path, Name, Id, Company -AutoSize

# Wraps output so we can see everything
Get-Process -Name *sql* | Format-Table -Wrap -Property Path, Name, Id, Company

# Groups all processes by Company 
Get-Process | Format-Table -Wrap -AutoSize -Property Path, Name, Id -GroupBy Company | more

# Displays all processes in wide format.
# Format-Wide only displays the default property by default.
# The net result is that this shows us each process in a multi column list.
Get-Process | Format-Wide

# Shows us the same thing. I guess Name is the default property.
Get-Process | Format-Wide -Property Name

# Still a multi-column list but this time company names.
Get-Process | Format-Wide -Property Company

# Shows each process with one line per property
Get-Process | Format-List

# Creates an array of processes
$processes = Get-Process
# Displays the fifth process in the array
$processes[4]

# Dashes
Write-Host $('-' * 70)

# Alias for Format-List
Get-Process | fl

# Alias for Format-Wide
Get-Process | fw

# Alias for Format-Table
Get-Process | ft

PowerShell - Error Control

$numerator = 3
$denominator = 0

try
{
    $myVar = $numerator / $denominator
}

catch
{
    Write-Host "Something went wrong:"
    Write-Host $error[0]
}

finally
{

    Write-Host "Do this regardless"
}


PowerShell - Loops

# While loop
# Counts from 1 to 3
$myVar = 1
while ($myVar -le 3)
{
    Write-Host $myVar
    $myVar++
}




# Do While loop
Write-Host "Another way to count from 1 to 3"
$myVar = 1
do
{
    $myVar
    $myVar++
} while ($myVar -le 3)




# For loop
Write-Host "And another way"
for ($myVar = 1; $myVar -le 3; $myVar++ )
    {
    $myVar
    }




# Now do it with a hash
$myArray = @(1, 2, 3)
Write-Host "Geting fancier"
foreach($myVar in $myArray)
{
    Write-Host "Value $myVar"
}

PowerShell - Syntax

Display output in the console:

Write-Host "Hello World"

Variable substitution:

$myvar = " World"
# Displays 'Hello World'
Write-Host "Hello$myvar"

<#
This is how I would
make a block comment
of several lines
#>

Write-Host "Goodbye$myvar"





If/Then/Else:

# This has some problems when the user enters an age less than 10.
# For the sake of learning, enter an age of 10 or higher when prompted.

# Your browser may not show it, but there is a space between $age and -lt

$age = Read-Host "How old are you?"

if ($age -lt 13)
{
    Write-Host "Child"
}
elseif ($age -lt 18)
{
    Write-Host "Teen"
}
else 
{
    Write-Host "Adult"
}



Switch (case):

# This has some problems when the user enters an age less than 10.
# For the sake of learning, enter an age of 10 or higher when prompted.

$age = Read-Host "How old are you?"

switch ($age)
{

    {$_ -lt 13 }
    {
        Write-Host "Child"
    }

    {($_ -lt 18) -and ($_ -gt 12) }
    {
        Write-Host "Teen"
    }

    default
    {
        Write-Host "Adult"
    }

}




Arrays:

# Empty array:
$myArray = @()

# Populated array:
$myArray = "baby", "toddler", "child", "teen", "adult"
$collectionOfAges = 0, 4, 13, 18

#Another way to define arrays:
$myArray = @("baby", "toddler", "child", "teen", "adult")

# Interact with arrays:
# child:
$myArray[2]

#
# This will not work because $myArray is a fixed array
# $myArray.Add("senior")

# So instead we need a dynamic-sized array.
# Here is how to define one:

$myArray = New-Object System.Collections.ArrayList
$myArray.Add("baby")
$myArray.Add("toddler")
$myArray.Add("child")
$myArray.Add("teen")
$myArray.Add("adult")
$myArray.Add("senior")

# senior:
$myArray[5]

Write-Host "non-adults:"
$myArray[0..2]



Hashes:

$myHash = @{
"ford" = "mustang"
"pontiac"  = "solstice"
"chevrolet" = "camaro"
}

# Displays 'mustang'
$myHash["ford"]

# Also displays 'mustang'
$myHash.ford


# Displays 3
$myHash.Count

PowerShell - SQL Module

How to load SQL PowerShell module:

Import-Module SQLPS


Wednesday, August 17, 2016

PowerShell - Help

Detailed help on cmdlet get-service:


get-help get-service -detailed

All content on cmdlet get-service:

get-help get-service -full

Show examples only:

get-help get-service -examples

List all the provider help topics such as FileSystem, Registry, or SQLServer:

get-help -category provider


PowerShell - Start here

List all cmdlets:

Get-Command

List all commands that retrieve information:

Get-Command Get*

List all commands having to do with SQL:

Get-Command *sql*

List all commands having to with services:

Get-Command *service*

Show all services:

Get-Service

Restarts the browser service:

Restart-Service browser

Gets documentation on specified cmdlet - in this case, get-service:

Get-Help get-service

Cmdlets can be interacted with by calling methods and setting or retrieving properties. By piping the output of Get-Service to Get-Member we can see all the methods and properties available from Get-Service:

Get-Service | Get-Member