I confess that when I started working with PowerShell, I envisioned functions as behaving the same way as Visual Basic or C#, i.e. everything being done within the single function. Thus it was big and full of conditions. Yet one of the great features of PowerShell is consistency; Everything in PowerShell is an object, and the output from one cmdlet can be easily “piped” into the input of another. This means your functions are generic, and over just two or three lines of code you can filter, apply summations, dictate output format, manage inserts into a SQL Server table etc…
As a result, PowerShell is an incredibly versatile scripting language which makes proactive database administration potentially pain-free. In my previous article, I demonstrated how to deal with orphaned users using PowerShell and SMO. This time around, I’ll take you through some more of my daily checklists and management tasks which have been ‘PowerShelled’ to make my life infinitely easier! Bear in mind that, as with my last article, my scripts require PowerShell 2.0, and all I’ll be giving you today are my scripts (which you can download from the top of this article) and examples on how to use them. I think they’re incredibly useful, and if you have any questions about any of them, feel free to leave a comment. So, without further ado:
Recording log information
Let’s start by talking a little more about Save-MSSQLMsg. It is one of most important functions here, because it will generate a log of all messages, be they error messages or simply information. As a result, I will always use this function in conjunction with others to ensure I know exactly what is going on in my servers. It takes 6 parameters, and only the first two (file and server names) are mandatory. The other 4 are not informed of the default values, as they are used by the script to insert information into the log files.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Function Save-MSSQLMsg () { #requires -Version 2 <# .SYNOPSIS Save log in file .PARAMETER NamePS1 File Name .PARAMETER Server Server name .PARAMETER databasename DatabaseName .PARAMETER Message default Message To Log .PARAMETER PathFileLog default Path to generate file Default = C:\temp .PARAMETER TodayDate Date to Log default getdate #> [CmdletBinding()] Param ( [Parameter(position=1,Mandatory = $true )][String] $NamePS1, [Parameter(position=2,Mandatory = $true )][String] $Server, [Parameter(position=3,Mandatory = $false )][String] $DatabaseName = ", [Parameter(position=4,Mandatory = $false )][String] $Message = " , [Parameter(position=5,Mandatory = $false )][String] $PathFileLog = "C:\temp", [Parameter(position=6,Mandatory = $false )][String] $TodayDate = (Get-Date -Format "yyyyMMddhhmmss") ) process { #Test if path wich will contains the error file exists. If not, # create if (!(Test-Path -path $PathFileLog)) { try { New-Item $PathFileLog -itemtype directory -ErrorAction Stop | Out-Null } catch { Write-Host "Can not create log file path" break; } } $NameFileFull = Join-Path $PathFileLog "$NamePS1$TodayDate.log" $TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2) + "-" + $TodayDate.Substring(6,2) "Server : " + $Server + " Database : " + $DatabaseName + " Date : " + $TDate + " Message: " + $Message | Out-file $NameFileFull -append } } |
So, for generating an error message or simply information, we use :
|
1 |
Save-MSSQLMsg "ScriptName1" "Server1" "Database1" "MessageToDisplay1" "C:\TEMP" |
But if we want only one file to be generated, we must constantly pass the same script name and date to the function, i.e., we need to set this in the parameters at the beginning of the function, and then we can use the function like this:
|
1 2 3 4 5 |
$ScriptName = "ScriptName1" $TodaydateMSG = Get-Date -Format "yyyyMMddhhmmss" Save-MSSQLMsg $ScriptName "Server1" "Database1" "MessageToDisplay1" "C:\TEMP" $TodaydateMSG Save-MSSQLMsg $ScriptName "Server2" "Database2" "MessageToDisplay2" "C:\TEMP" $TodaydateMSG Save-MSSQLMsg $ScriptName "Server3" "Database3" "MessageToDisplay3" "C:\TEMP" $TodaydateMSG |
Returning Backup Information
“Laerte, I need all backup information about backups in our databases, and if we had some of them without backup”
“Yes boss. Right away. How do you want that information? Email ? Snail Mail ? Smoke Signal ? Drums ?”
Not a problem; You’re an Exceptional DBA who has everything ready for your checks, and PowerShell under your command. As the heading suggests, this function returns information about your database backups. The properties return by the object are :
|
LineNumber |
(Int) |
|
Date |
(DateTime) |
|
ServerName |
(string) |
|
DatabaseName |
(string) |
|
LastBackupDate |
(DateTime) |
|
LastDifferentialBackupDate |
(DateTime) |
|
LastLogBackupDate |
(DateTime) |
Bear in mind that you can control which servers this script checks by supplying a list of targets in a flat text file – C:\TEMP\Servers.txt. If you don’t supply this file, the script will only check the default server. Take a look at the script below, and I’ll give some examples of how to use it after the jump>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
Function Get-MSSQLBackup () { #requires -Version 2 <# .SYNOPSIS Returns information about backups .DESCRIPTION Write to Application LocalComputer Eventlog .PARAMETER TxtServerList List with servers, if not pass default server is used .LINK www.laertejuniordba.spaces.live.com #> [CmdletBinding()] PARAM ( [Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="File Servers List")] [Alias("FullNameTXT")] [String] $TXTServersList = $env:COMPUTERNAME ) begin { [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null } Process { $verbosePreference="continue" [datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss" if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT") { try { $ServersList = get-content $TXTServersList } catch { $msg = $error[0] Write-Warning $msg break; } } else { $ServersList = $TXTServersList } $LineNumber = 1 $FinalResult = @() foreach ($svr in $ServersList ) { try { $DatabaseName = " $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr" $Data = $Server.Databases| where-object {$_.IsAccessible } | foreach { [datetime] $LastBackupDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.LastBackupDate [datetime]$LastDifferentialBackupDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.LastDifferentialBackupDate [datetime]$LastLogBackupDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.LastLogBackupDate [String] $DatabaseName = $_.name $Object = New-Object PSObject -Property @{ LineNumber = $LineNumber Date = $TodayDate ServerName = $svr DatabaseName = $DatabaseName LastBackupDate = $LastBackupDate LastDifferentialBackupDate = $LastDifferentialBackupDate LastLogBackupDate = $LastLogBackupDate } $FinalResult += $Object $LineNumber ++ } } catch { $msg = $error[0] Write-Warning $msg Save-MSSQLMsg "Get-MSSQLBackup" "$svr" "$DatabaseName" "$msg" continue } } Write-Output $FinalResult } } |
So, now that you’ve seen the function, here are some examples on how to use it :
- List all backup information on the default server:
|
1 |
Get-MSSQLBackup |
- List all backup information on target server[s] (supplied in the text file):
|
1 |
Get-MSSQLBackup "C:\TEMP\Servers.txt" |
- List all databases which haven’t been backed up for one day:
|
1 2 3 4 |
Get-MSSQLBackup "C:\TEMP\servers1.txt" | where-Object {( (get-Date) - ($_.LastBackupDate)).days -gt 1} | Select LineNumber,Date,ServerName, DatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate | Format-Table |
- List backup information for all databases, and store it in a SQL Server table:
|
1 2 3 4 5 6 7 8 |
create table BackupsXML (XMLCOL XML) (Get-MSSQLBackup "C:\TEMP\servers.txt" | Select LineNumber,Date,ServerName, DatabaseName,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate | ConvertTo-Xml -NoTypeInformation).save("c:\temp\Backups.xml") $XML = (Get-Content "C:\Temp\Backups.xml" ) -replace "'", "''" $SQL = "insert into BackupsXML (XMLCol) values ('$XML')" invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql |
- Generate TSQL to list all data from the “BackupsXML” table, where the ServerName condition = “Jupiter”:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Select t2.Col1.value('(Property[@Name="LineNumber"]/text())[1]', 'int') LineNumber, t2.Col1.value('(Property[@Name="Date"]/text())[1]', 'nvarchar(255)') Date, t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') ServerName, t2.Col1.value('(Property[@Name="DatabaseName"]/text())[1]', 'nvarchar(255)') DatabaseName, t2.Col1.value('(Property[@Name="LastBackupDate"]/text())[1]', 'nvarchar(255)') LastBackupDate FROM dbo.BackupsXML CROSS APPLY xmlcol.nodes('/Objects/Object') As t2(Col1) where t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') = 'Jupiter' |
Now that we’ve got this script working, let’s generate a CSV file and email it to our beloved leader:
|
1 2 3 4 5 6 |
Get-MSSQLBackup "C:\TEMP\Servers.txt" | Select Date,ServerName,DatabaseName,LastBackupDate | Export-Csv -Path "C:\temp\Backups.csv" -NoTypeInformation ââ¬"Force Send-MailMessage -To "YourBelovedBoss@contoso.com" -Subject "Database Without Backups My Beloved Boss" -from "ExceptionalPowershellDBA@contoso.com" -Attachments "C:\TEMP\backups.csv" -SmtpServer "10.0.0.10" |
Returns Linked Servers Information and Connectivity
“Laerteeeee (yes..he loves my name) , Some of our remote procedures are returning errors. What is happening?”
“I will check the linked servers again, my lord”
I know..I know.. PowerShell is my middle name.
This function returns an object with information about all your linked servers and your connectivity. As with the others functions, you can pass a file to the script with all the servers you want to test, or else leave it empty if you want the current server. Likewise, this script will also generate a log file if some server is off or another problem occurs.The properties returned are :
|
LineNumber |
(int) |
|
Date |
(datetime) |
|
ServerName |
(string) |
|
LinkedServerName |
(string) |
|
DataSource |
(string) |
|
DateLastModified |
(datetime) |
|
CollationCompatible |
(boolean) |
|
DataAccess |
(boolean) |
|
RPC |
(boolean) |
|
RpcOut |
(boolean) |
|
UseRemoteCollation |
(boolean) |
|
CollationName |
(string) |
|
ConnectionTimeOut |
(int) |
|
QueryTimeOut |
(int) |
|
Distributor |
(boolean) |
|
Publisher |
(boolean) |
|
Subscriber |
(boolean) |
|
LazySchemaValidation |
(boolean) |
|
EnablePromotionofDistributedTransactionsForRPC |
(boolean) |
|
ProviderName |
(string) |
|
ProductName |
(string) |
|
Connectivity |
(boolean) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
Function Get-MSSQLLinkedServer() <# #requires -Version 2 <# .SYNOPSIS Returns information about linkedservers. Also test connectivity too. .DESCRIPTION Returns information about linkedservers. Also test connectivity too. .PARAMETER TxtServerList List with servers, if not pass default server is used .LINK www.laertejuniordba.spaces.live.com #> #> { [CmdletBinding()] PARAM ( [Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true, HelpMessage="File Servers List")] [Alias("FullNameTXT")] [String] $TXTServersList = $env:COMPUTERNAME ) begin { [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null } Process { $verbosePreference="continue" [datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss" if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT") { try { $ServersList = get-content $TXTServersList } catch { $msg = $error[0] Write-Warning $msg break; } } else { $ServersList = $TXTServersList } $LineNumber = 1 $FinalResult = @() foreach ($svr in $ServersList ) { try { $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr" $Data = $Server.linkedservers| where-object {$_.State -eq "Existing"} | foreach { [datetime]$DateLastModified = "{0:yyyy-MM-dd hh:mm:ss}" -f [datetime] $_.DateLastModified try { $_.testconnection() $Connectivity = $true } catch { $Connectivity = $false } $Object = New-Object PSObject -Property @{ LineNumber = $LineNumber Date = $TodayDate ServerName = $svr LinkedServerName = $_.Name DataSource = $_.DataSource DateLastModified = $DateLastModified CollationCompatible = $_.CollationCompatible DataAccess = $_.DataAccess RPC = $_.RPC RpcOut = $_.RPCOut UseRemoteCollation = $_.UseRemoteCollation CollationName = $_.CollationName ConnectionTimeOut = $_.ConnectTimeOut QueryTimeOut = $_.QueryTimeOut Distributor = $_.Distributor Publisher = $_.Publisher Subscriber = $_.Subscriber LazySchemaValidation = $_.LazySchemaValidation EnablePromotionofDistributedTransactionsForRPC = $_.IsPromotionofDistributedTransactionsForRPCEnable ProviderName = $_.ProviderName ProductName = $_.ProductName Connectivity = $Connectivity } $FinalResult += $Object $LineNumber ++ } } catch { $msg = $error[0] Write-Warning $msg Save-MSSQLMsg "Get-MSSQLLinkedServer" "$svr" " "$msg" continue } } Write-Output $FinalResult } } |
… And just as before, some examples on how to use it:
- List All the LinkedServers information for the default Server:
|
1 |
Get-SQLLinkedServer |
- >List All the LinkedServers information for All target servers:
|
1 |
Get-SQLLinkedServer "c:\temp\servers.txt" |
- List All the LinkedServers information for All target servers, and filter the data down to just the name and connectivity test data:
|
1 |
Get-SQLLinkedServer "c:\temp\servers.txt" | select LineNumber,Date,ServerName,LinkedServerName,Connectivity |Format-Table |
- List All the LinkedServers information for All target servers, and filter the data down to just the name and connectivity test data for results where Connectivity = false):
|
1 2 3 |
Get-SQLLinkedServer "c:\temp\servers.txt" | where-object {!$_.Connectivity} | select LineNumber,Date,ServerName,LinkedServerName,Connectivity |Format-Table |
- List All the LinkedServers information for All target servers, filter the data down to just the name and connectivity test results where Connectivity = false), and store the data in a ‘LinkerServers’ SQL Server Table:
|
1 2 3 4 5 6 7 8 9 |
TSQL create table LinkedServers (XMLCOL XML) (Get-SQLLinkedServer "c:\temp\servers.txt" | where-object {!$_.Connectivity} | select LineNumber,Date,ServerName,LinkedServerName, Connectivity | ConvertTo-Xml -NoTypeInformation) .save("c:\temp\LinkedServers.xml") $XML = (Get-Content "C:\Temp\LinkedServers.xml" ) -replace "'", "''" $SQL = "insert into LinkedServers (XMLCol) values ('$XML')" invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql |
- Generate TSQL to list all the data from the ‘LinkerServers’ table where the ServerName condition = “Jupiter”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Select t2.Col1.value('(Property[@Name="LineNumber"]/text())[1]', 'int') LineNumber, t2.Col1.value('(Property[@Name="Date"]/text())[1]', 'nvarchar(255)') Date, t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') ServerName, t2.Col1.value('(Property[@Name="LinkedServerName"]/text())[1]', 'nvarchar(255)') LinkedServerName, t2.Col1.value('(Property[@Name="Connectivity"]/text())[1]', 'nvarchar(255)') Connectivity FROM dbo.LinkedServers CROSS APPLY xmlCOL.nodes('/Objects/Object') As t2(Col1) where t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') = 'Jupiter' |
Return Logins information
“Laerte, my Padawan, we need to know all the logins created in our servers during last week”
“Yes my Master. I´m an Exceptional DBA and The PowerShell is with me.”
This function returns an object containing:
|
LineNumber |
(int) |
|
Date |
(datetime) |
|
ServerName |
(string) |
|
LoginName |
(string) |
|
CreateDate |
(Datetime) |
|
DateLastModified |
(Datetime) |
|
LoginType |
(String) |
|
AsymmetricKeys |
(String) |
|
Certificate |
(String) |
|
DefaultDatabase |
(String) |
|
DenyWindowsLogin |
(Boolean) |
|
HasAccess |
(Boolean) |
|
IsDisable |
(Boolean) |
|
IsLocked |
(Boolean) |
|
IsPassowordExpired |
(Boolean) |
|
IsSystemObject |
(Boolean) |
|
Language |
(String) |
|
LanguageAlias |
(String) |
|
MustChangePassword |
(Boolean) |
|
PasswordExpirationEnabled |
(Boolean) |
|
PasswordPolicyEnforced |
(Boolean) |
… And the script itself looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
Function Get-MSSQLLogin() #requires -Version 2 <# .SYNOPSIS Returns information about Logins .DESCRIPTION Returns information about Logins .PARAMETER TxtServerList List with servers, if not pass default server is used .LINK www.laertejuniordba.spaces.live.com #> { [CmdletBinding()] PARAM ( [Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true, HelpMessage="File Servers List")] [Alias("FullNameTXT")] [String] $TXTServersList = $env:COMPUTERNAME ) begin { [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null } Process { $verbosePreference="continue" [datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss" if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT") { try { $ServersList = get-content $TXTServersList } catch { $msg = $error[0] Write-Warning $msg break; } } else { $ServersList = $TXTServersList } $LineNumber = 1 $FinalResult = @() foreach ($svr in $ServersList ) { try { $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr" $Data = $Server.logins | where-object {$_.State -eq "Existing"} | foreach { [datetime] $CreateDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.CreateDate [datetime] $DateLastModified = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.DateLastModified $Object = New-Object PSObject -Property @{ LineNumber =$LineNumber Date =$TodayDate ServerName =$svr LoginName =$_.Name CreateDate =$CreateDate DateLastModified =$DateLastModified LoginType =$_.LoginType AsymmetricKeys =$_.AsymmetricKeys Certificate =$_.Certificate DefaultDatabase =$_.DefaultDatabase DenyWindowsLogin =$_.DenyWindowsLogin HasAccess =$_.HasAccess IsDisable =$_.IsDisable IsLocked =$_.IsLocked IsPassowordExpired =$_.IsPassowordExpired IsSystemObject =$_.IsSystemObject Language =$_.Language LanguageAlias =$_.LanguageAlias MustChangePassword =$_.MustChangePassword PasswordExpirationEnabled =$_.PasswordExpirationEnabled PasswordPolicyEnforced =$_.PasswordPolicyEnforced } $FinalResult += $Object $LineNumber ++ } } catch { $msg = $error[0] Write-Warning $msg Save-MSSQLMsg "Get-MSSQLLogin" "$svr" " "$msg" continue } } Write-Output $FinalResult } } |
As before you can easily apply all the filters you want , this function works with logs, and the usage patterns are basically the same:
- List All Logins information on the default server:
|
1 |
Get-MSSQLLogin |
- List All Logins information on All target servers:
|
1 |
Get-MSSQLLogin "c:\temp\servers.txt" |
- List All Logins information on All target servers, filter the data down to just the name and CreateDate:
|
1 |
Get-MSSQLLogin "c:\temp\servers.txt" | select LineNumber,Date,ServerName, LoginName,CreateDate |Format-Table |
- List All Logins information on All target servers filter the data down to just the name and where the CreateDate is less than 7 days ago, and store the results in a SQL Server Table:
|
1 2 3 4 5 6 |
Get-MSSQLLogin | where-Object {((get-date) - ($_.createdate)).days -lt 7} | select LineNumber,Date,ServerName,LoginName,CreateDate |Format-Table TSQL create table Logins (XMLCOL XML): (Get-MSSQLLogin "c:\temp\servers.txt" | where-Object {((get-date) - ($_.createdate)).days -lt 7} | select LineNumber,Date,ServerName,LoginName,CreateDate | ConvertTo-Xml -NoTypeInformation).save("c:\temp\Logins.xml") $XML = (Get-Content c:\temp\Logins ) -replace "'", "''" $SQL = "insert into Logins (XML) values ('$XML')" invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql |
- Generate TSQL to list the data where the ServerName condition = “Jupiter”:
|
1 2 |
Select t2.Col1.value('(Property[@Name="LineNumber"]/text())[1]', 'int') LineNumber, t2.Col1.value('(Property[@Name="Date"]/text())[1]', 'nvarchar(255)') Date, t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') ServerName, t2.Col1.value('(Property[@Name="LoginName"]/text())[1]', 'nvarchar(255)') LoginName, t2.Col1.value('(Property[@Name="CreateDate"]/text())[1]', 'nvarchar(255)') CreateDateFROM dbo.LoginsCROSS APPLY xmlCOL.nodes('/Objects/Object') As t2(Col1)where t2.Col1.value('(Property[@Name="ServerName"]/text())[1]','nvarchar(max)') = 'Jupiter' |
Returning Jobs information
“Laerte, I see that every morning when you get to work, you have an excel list in your inbox listing the problematic backups on all the servers in the environment. Out of curiosity, how do you do that? I couldn’t find Database Mail configured, checking procedures or any other mechanism in place to audit jobs in servers.”
From my machine, with a few lines of code, I access all the servers’ lists of the jobs that failed, in XLSX format, and send that information to my email. I don’t have any procedures or mechanism on the servers;. the function is uniform, repetitive and centralized; it returns the following information:
|
LineNumber |
(int) |
|
Date |
(DateTime) |
|
ServerName |
(String) |
|
JobName |
(String) |
|
Lastrunoutcome |
(String) |
|
Isenabled |
(Boolean) |
|
lastrundate |
(DateTime) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
Function Get-MSSQLJob() #requires -Version 2 <# .SYNOPSIS Returns information about Jobs .DESCRIPTION Returns information about Jobs .PARAMETER TxtServerList List with servers, if not pass default server is used .LINK www.laertejuniordba.spaces.live.com #> { [CmdletBinding()] PARAM ( [Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true, HelpMessage="File Servers List")] [Alias("FullNameTXT")] [String] $TXTServersList = $env:COMPUTERNAME ) begin { [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null } Process { $verbosePreference="continue" [datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss" if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT") { try { $ServersList = get-content $TXTServersList } catch { $msg = $error[0] Write-Warning $msg break; } } else { $ServersList = $TXTServersList } $LineNumber = 1 $FinalResult = @() foreach ($svr in $ServersList ) { try { $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr" $Data = $Server.jobserver.jobs | foreach { $Object = New-Object PSObject -Property @{ LineNumber = $LineNumber Date = $TodayDate ServerName = $svr JobName = $_.name lastrunoutcome = $_.lastrunoutcome isenabled = $_.isenabled lastrundate = $_.lastrundate } $FinalResult += $Object $LineNumber ++ } } catch { $msg = $error[0] Write-Warning $msg Save-MSSQLMsg "Get-MSSQLJob" "$svr" " "$msg" continue } } Write-Output $FinalResult } } |
And now, once we’ve collected all that iinformation, we can create a XLSX and send to our email:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
$excel = New-Object -ComObject Excel.Application $excel.Visible = $false $workbook = $excel.Workbooks.add() $workbook.WorkSheets.item(1).Name = "Jobs" $sheet = $workbook.WorkSheets.Item("Jobs ") $sheet.cells.item(1,1) = "Date" $sheet.cells.item(1,2) = "ServerName" $sheet.cells.item(1,3) = "JobName" $sheet.cells.item(1,4) = "LastRunDate" $Line = 2 foreach ($Jobs in Get-MSSQLJob "C:\TEMP\servers.txt" where-Object {$_.lastrunoutcome –eq “Failed” –and $_.isenabled} ) { $sheet.cells.item($line, 1) = $Jobs.Date -f "{0:yyyy-MM-dd hh:mm:ss}" -f [datetime] $sheet.cells.item($line, 2) = $Jobs.ServerName $sheet.cells.item($line, 3) = $Jobs.JobName $sheet.cells.item($line, 4) = $Jobs.LastRunDate $Line++ } $excel.Visible = $true $workbook.SaveAs("C:\TEMP\Jobs.xlsx") $workbook.Close() $excel.Quit Send-MailMessage -To " ExceptionalPowershellDBA@contoso.com " -Subject "Jobs With Problems" -from "AuditDBA@contoso.com" -Attachments "C:\TEMP\Jobs.xlsx" -SmtpServer "10.0.0.10" |
If you want to see more about PowerShell exporting data to Excel, be sure to read this excellent content on TechNet:
Now it only remains for you to schedule this script for a job in SQL Server, or even Windows Task Scheduler. Keep in mind that these functions are just examples, and do not return all the properties of the SMO objects used. If you want a complete list of properties, I suggest using MSDN and studying the classes in question.
So, in this second part of my Exceptional PowerShell DBA posts you can see some of my daily checklist, and, more importantly, how PowerShell can help reduce the overhead of managing that checklist Please feel free to download my sample scripts from the top of this article and experiment with them. In the third and final part of these posts, we will see some more set ups we can do, like changing collation columns and rebuilding indexes. As always: don’t forget, you are an Exceptional DBA who loves PowerShell!
Load comments