Joe Webb’s Powershell Cookbook

If you haven’t already done so then please read the following http://tenbulls.co.uk/enlightenment/scripts/

Anyone who knows anything about SQL Server will have heard of Joe Webb (blog|twitter). He is the co-author of several very successful SQL Server books and most recently contributed towards the rather fabulous SQL Server MVP Deep Dives. If you haven’t bought that book yet then make sure you do, because it is packed full of useful tricks from some rather clever people. Joe is a currently serving MVP.

The Powershell Cookbook script was supporting material for Joe’s PASS Summit 2010 presentation and you can find the source script published here.

#
# The PowerShell CookBook
# by Joe Webb
# joew@webbtechsolutions.com
#
# Demonstrations
#
#

cls

################# demo 1
# cmdlets

#retrieve a list of services running on the local machine
Get-Service

#retrieve a list of process running on the local machine
Get-Process

#navigating the file system

#can use dos-like syntax
dir

#or unix-like syntax
ls

#both are an alias for a cmdlet
Get-ChildItem

#can check the present working directory
pwd

#and change folders
cd "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log"
ls

cd hklm:\
ls
cd software
ls

#examine the contents of a file
Get-Content ErrorLog;

#what are the other cmdlets?
Get-Command;

#getting help on a cmdlet
Get-Help Start-Job;

#getting more help
Get-Help Start-Job -detailed;

#getting more help about a cmdlet
Get-Service | Get-Member;

Get-Service | Format-Table Name, DisplayName, MachineName, Status

################# demo 2
# scripting basics

#using a variable
$var1 = "localhost";
$var1;

#populating at variable with a cmdlet (single file)
$file = "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG";
$log = Get-Content $file;
$log;

#populating an array with a cmdlet (multiple files)
$files = @("C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG",
"C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1"
"C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.2");
$log = Get-Content $files;
$log;

#filtering with the where object; stopped services
$services = Get-Service;
$services | where {$_.Status -eq "Stopped"};

#filtering with the where object; sql server services
$services | where {$_.Name -like "*SQL*" -and $_.Status -eq "Stopped"};

#formatting output in a list
$services | Format-List;

#formatting output in a table
$services | Format-Table

#formatting output in a table with additional attributes
$services | Format-Table Name, DisplayName, Status, CanStop;

#formatting output in a table with additional attributes and autosize
$services | Format-Table Name, DisplayName, Status, CanStop -autosize;

#foratting output with mulitple columns
$services | Format-Wide;

#sorting output
$services | Sort-Object Status, Name;

#exporting to csv files
$services | Export-Csv c:\demo\services.txt;
Notepad "c:\demo\services.txt";

#exporting to xml files
$services | Export-CliXml c:\demo\services.xml;
Notepad "c:\demo\services.xml";

#exporting to html files
$services | ConvertTo-Html | out-file c:\demo\services.html;
Notepad "c:\demo\services.html";

#clean up the files
Remove-Item "c:\demo\services.html";
Remove-Item "c:\demo\services.*";
ls "c:\demo";

################# demo 3
# parsing error Logs

#parsing the SQL Server error logs
$files = @("C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG",
"C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1"
"C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.2");
$log = Get-Content $files;
$log;

Get-Help Select-String;

$search = “Severity: \d{2}”
$log | Select-String -pattern $search

#parsing log files for failed login attempts
$search = "Login Failed";
$log | Select-String -pattern $search

################# demo 4
# parsing the event log

#parsing the event log
Get-Help Get-EventLog -detailed;

Get-EventLog Application;

Get-EventLog Application -Newest 10;

Get-EventLog Application -After "2010-09-30";

Get-EventLog Application -After "2010-09-30" -Before "2010-10-31";

Get-EventLog Application -EntryType Error;

Get-EventLog Application -EntryType Error -After "2010-09-30" -Before "2010-10-31";

Get-EventLog Application -EntryType Error -After (Get-Date).AddDays(-7);

Get-EventLog Application -Message "*failed*";

Get-EventLog Application | where {$_.EventId -eq 1309} | Format-Table -autosize;

Get-EventLog Application -ComputerName localhost;

################# demo 5
# discovering SQL Server Instances

#get a list of sql server instances
sqlcmd -Lc;

#write the list of servers to a text file
$serverlist = "c:\demo\servers.txt";
sqlcmd -Lc > $serverlist;
Notepad $serverlist;

################# demo 6
# retrieving server property information from a list of servers

#load the assembly and read the list of servers from a file
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null;
$servers = Get-Content $serverlist;
$servers;

#iterate through the list of servers to get properties
foreach ($servername in $servers) {
$servername = $servername.Trim();
if ($servername.Length -gt 0) {

$instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername;

Write-Host $servername;
Write-Host "Product: " $instance.Information.Product;
Write-Host "Edition: " $instance.Information.Edition;
Write-Host "Version: " $instance.Information.Version;
Write-Host "Product Level: " $instance.Information.ProductLevel;
}
}

#clean up by removing the serverlist file
Remove-Item "c:\demo\servers.txt";

################# demo 7
# collecting other sql server instance properties

#gather additional information
$servername = "localhost";
$instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername;

Write-Host $servername;
Write-Host "     Product: " $instance.Information.Product;
Write-Host "     Edition: " $instance.Information.Edition;
Write-Host "     Version: " $instance.Information.Version;
Write-Host "     Product Level: " $instance.Information.ProductLevel;
Write-Host "     Min. Server Memory: " $instance.Configuration.MinServerMemory.ConfigValue;
Write-Host "     Max. Server Memory: " $instance.Configuration.MaxServerMemory.ConfigValue;
Write-Host "     AWE Enabled: " $instance.Configuration.AweEnabled.ConfigValue;
Write-Host "     CLR Enabled: " $instance.Configuration.IsSqlClrEnabled.ConfigValue;
Write-Host "     XPCmdShell Enabled: " $instance.Configuration.XPCmdShellEnabled.ConfigValue;
Write-Host "     Number of Databases: " $instance.Databases.Count;
Write-Host "     Number of Logins: " $instance.Logins.Count;
Write-Host "     Number of Linked Servers: " $instance.LinkedServers.Count;
Write-Host "     Number of Jobs: " $instance.JobServer.Jobs.Count;
Write-Host "     OS Version: " $instance.Information.OSVersion;
Write-Host "     Physical Memory: " $instance.Information.PhysicalMemory;
Write-Host "     Platform: " $instance.Information.Platform;
Write-Host "     Number of Processors: " $instance.Information.Processors;
Write-Host "";
Write-Host "";

################# demo 8
# collecting database information

#get database information
$servername = "localhost";
$instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername;

Write-Host $servername;

$dbs = $instance.Databases;

foreach($db in $dbs){
Write-Host "";
Write-Host "Database Name " $db.Name;
Write-Host "";
Write-Host "Configuration Properties:"
Write-Host "    Database Owner " $db.Owner;
Write-Host "    Collation " $db.Collation;
Write-Host "    Primary File Path " $db.PrimaryFilePath;
Write-Host "    CompatibilityLevel " $db.CompatibilityLevel;
Write-Host "    AutoShrink " $db.AutoShrink;
Write-Host "    AutoClose " $db.AutoClose;
Write-Host "    Recovery Model " $db.RecoveryModel;
Write-Host "    Auto Create Statistics " $db.AutoCreateStatisticsEnabled;
Write-Host "    Auto Update Statistics " $db.AutoUpdateStatisticsEnabled;

$dbSpaceAvailable = $db.SpaceAvailable/1KB;
$dbDataSpaceUsage = $db.DataSpaceUsage/1KB;
$dbIndexSpaceUsage = $db.IndexSpaceUsage/1KB;

#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable;
$dbDataSpaceUsage = "{0:N3}" -f $dbDataSpaceUsage;
$dbIndexSpaceUsage = "{0:N3}" -f $dbIndexSpaceUsage;

Write-Host ""
Write-Host "Statistical Information: "
Write-Host "     Create Date " $db.CreateDate;
Write-Host "     Space Available " $dbSpaceAvailable " MB";
Write-Host "     Data Space Used " $dbDataSpaceUsage " MB";
Write-Host "     Index Space Used " $dbIndexSpaceUsage " MB";

Write-Host ""
Write-Host "Maintenance Information: ";
Write-Host "     Last Backup " $db.LastBackupDate;
Write-Host "     Last Differential Backup " $db.LastDifferentialBackupDate;
Write-Host "     Last Log Backup " $db.LastLogBackup;

Write-Host ""
Write-Host "Log File Information: ";
$logs = $db.LogFiles;
foreach ($log in $logs) {
Write-Host "     " $log.Name -nonewline;
Write-Host ", " $log.FileName -nonewline;
$logsize = $log.Size/1KB;
$logsize = "{0:N3}" -f $logsize
Write-Host ", " $logsize " MB";
}

Write-Host ""
Write-Host "Object Information:";
Write-Host "     Number of Tables " $db.Tables.Count;
Write-Host "     Number of Views " $db.Views.Count;
Write-Host "     Number of Stored Procedures " $db.StoredProcedures.Count;
Write-Host "     Number of User Defined Functions " $db.UserDefinedFunctions.Count;
Write-Host "     Number of Triggers " $db.Triggers.Count;

}

################# demo 9
# view job information for each server

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$servername = "localhost"
$instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername;

Write-Host $servername;
Write-Host "";

foreach($job in $instance.JobServer.Jobs){
Write-Host "Job Information: ";
Write-Host "    Job Name " $job.Name;
Write-Host "    Job Enabled " $job.IsEnabled;
Write-Host "    Last Run Date " $job.LastRunDate;
Write-Host "    Last Run Outcome " $job.LastRunOutcome;
}

################# demo 10
# delete old log files

Set-Location "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\"
foreach ($file in get-childitem) {
if ($file.LastWriteTime -lt (Get-Date).AddDays(-30)) {
Write-Host "Deleting File $file.Name";
del $File
}
}

################# demo 11
# sending emails

#variables & constants
$smtp_server = "localhost";
$to = "joew@webbtechsolutions.com";
$from = "joew@webbtechsolutions.com"
$subject = "Something bad happened!"
$body = "Something bad happened and your phone is about to start ringing."

$name="localhost";
$service = Get-WmiObject Win32_Service -Computername $name -filter "name='SQLBrowser'";
if ($service.State -eq "Stopped") {
#send-mailmessage -to $to -from $from -subject $subject -body $body -smtpserver $smtp_server;
Write-Host "sending an email";
}

################# demo 12
# using functions

1
function show-CurrentTime() {
    $current_time = [datetime]::Now.ToUniversalTime();
    Write-Host "The current UTC time is: " $current_time;
    }

show-CurrentTime;

################# demo 13
# calling stored procedures

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server=(local);Database=master;Integrated Security=True";

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = "sp_who";
$SqlCommand.Connection = $SqlConnection;

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCommand;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$SqlConnection.Close();
$DataSet.Tables[0] | Format-Table;

################# demo 14
# incorporating best practices

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null;
$servername = "localhost";
$instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername;

Write-Host $servername;
Write-Host "     Product: " $instance.Information.Product;
Write-Host "     Number of Log Files " -nonewline;

if ($instance.NumberOfLogFiles -lt 10)
     {
          Write-Host $instance.NumberOfLogFiles -ForegroundColor Red;
     }
      else
     {
          Write-Host $instance.NumberOfLogFiles;
     }
     Write-Host "     Version: " $instance.Information.Version;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s