Laerte Junior takes us further into the world of an Exceptional PowerShell DBA, showing us how he uses PowerShell 2.0 to take all the headaches out of even more of his daily checklist. What could be better than having your morning checklist run itself?

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.

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 :

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:

$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 :

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>

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:

Get-MSSQLBackup 

• List all backup information on target server[s] (supplied in the text file):

Get-MSSQLBackup "C:\TEMP\Servers.txt"

• List all databases which haven't been backed up for one day:

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:

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":

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:

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 :

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:

Get-SQLLinkedServer 

• >List All the LinkedServers information for All target servers:

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:

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):

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:

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"

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:

... And the script itself looks like this:

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:

Get-MSSQLLogin 

• List All Logins information on All target servers:

Get-MSSQLLogin "c:\temp\servers.txt" 

• List All Logins information on All target servers, filter the data down to just the name and CreateDate:

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:

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":

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)') CreateDate
FROM dbo.Logins
CROSS 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:

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:

$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:

• Hey, Scripting Guy!
• Export Windows PowerShell Data to Excel

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!