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]

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