setup/powershell/AgentJobServer.ps1

<#
# ROB IS THIS SUPPOSED TO KEEP HISTORICAL INFORMATION? IF SO $UPDATE WILL ALWAYS BE FALSE

.SYNOPSIS
    Adds data to the DBA database for agent job results in a server list

.DESCRIPTION
    Connects to a server list and iterates though reading the agent job results and adds data to the DBA Database - This is run as an agent job on LD5v-SQL11n-I06

.NOTES
dbareports PowerShell module (https://dbareports.io, SQLDBAWithABeard.com)
Copyright (C) 2016 Rob Sewell

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.

#>

[CmdletBinding()]
Param (
    [Alias("ServerInstance", "SqlInstance")]
    [object]$SqlServer = "--installserver--",
    [object]$SqlCredential,
    [string]$InstallDatabase = "--installdb--",
    [string]$LogFileFolder = "--logdir--"
)

BEGIN
{
        # Create Log File
    $Date = Get-Date -Format yyyyMMdd_HHmmss
    $LogFilePath = $LogFileFolder + '\' + 'dbareports_AgentJobServer_' + $Date + '.txt'
    try
    {
        New-item -Path $LogFilePath -itemtype File -ErrorAction Stop 
        Write-Log -path $LogFilePath -message "Agent Job Server Job started" -level info
    }
    catch
    {
        Write-error "Failed to create Log File at $LogFilePath"
    }
    
    # Specify table name that we'll be inserting into
    $table = "info.AgentJobServer"
    $schema = $table.Split(".")[0]
    $tablename = $table.Split(".")[1]
    
    # Load up shared functions
    $currentdir = Split-Path -Parent $MyInvocation.MyCommand.Definition
    . "$currentdir\shared.ps1"
    . "$currentdir\Write-Log.ps1"
    
        # Connect to dbareports server
    try
    {
        Write-Log -path $LogFilePath -message "Connecting to $sqlserver" -level info
        $sourceserver = Connect-SqlServer -SqlServer $sqlserver -SqlCredential $SqlCredential -ErrorAction Stop 
    }
    catch
    {
        Write-Log -path $LogFilePath -message "Failed to connect to $sqlserver - $_" -level Error
    }

    # Get columns automatically from the table on the SQL Server
    # and creates the necessary $script:datatable with it
    try
    {
        Write-Log -path $LogFilePath -message "Intitialising Datatable" -level info
        Initialize-DataTable -ErrorAction Stop 
    }
    catch
    {
        Write-Log -path $LogFilePath -message "Failed to initialise Data Table - $_" -level Error
    }
}

PROCESS
{
    try
    {
        Write-Log -path $LogFilePath -message "Getting Instances from $sqlserver" -level info
        $sqlservers = Get-Instances
    }
    catch
    {
        Write-Log -path $LogFilePath -message " Failed to get instances - $_" -level Error
        break
    }
    
    # Get list of all servers already in the database
    try
    {
        Write-Log -path $LogFilePath -message "Getting a list of servers from the dbareports database" -level info
        $sql = "SELECT AgentJobServerID, InstanceID FROM $table"
        $table = $sourceserver.Databases[$InstallDatabase].ExecuteWithResults($sql).Tables[0]
        Write-Log -path $LogFilePath -message "Got the list of servers from the dbareports database" -level info
    }
    catch
    {
        Write-Log -path $LogFilePath -message "Can't get server list from $InstallDatabase on $($sourceserver.name). - $_" -level Error
    }
    
    foreach ($sqlserver in $sqlservers)
    {
        $sqlservername = $sqlserver.ServerName
        $InstanceName = $sqlserver.InstanceName
        $InstanceId = $sqlserver.InstanceId
        $update = $true
        
        # Checking for existing record and setting flag
        $record = $table | Where-Object { $_.InstanceId -eq $InstanceID }
        $key = $record.AgentJobServerID
        
        if ($key.count -eq 0)
        {
            $update = $false
        }

        if ($InstanceName -eq 'MSSQLServer')
        {
            $Connection = $sqlservername
        }
        else
        {
            $Connection = "$sqlservername\$InstanceName"
        }
        
        # Connect to Instance
        try
        {
            $server = Connect-SqlServer -SqlServer $Connection
            Write-Log -path $LogFilePath -message "Connecting to $Connection" -level info
        }
        catch
        {
            Write-Log -path $LogFilePath -message "Failed to connect to $Connection - $_" -level Warn
            continue
        }
        
        $jobs = $server.JobServer.jobs
        $JobCount = $jobs.Count
        $successCount = ($jobs | Where-Object { $_.LastRunOutcome -eq 'Succeeded' -and $_.IsEnabled -eq $true }).Count
        $failedCount = ($jobs | Where-Object { $_.LastRunOutcome -eq 'Failed' -and $_.IsEnabled -eq $true }).Count
        $UnknownCount = ($jobs | Where-Object { $_.LastRunOutcome -eq 'Unknown' -and $_.IsEnabled -eq $true }).Count
        $JobsDisabled = ($jobs | Where-Object { $_.IsEnabled -eq $false }).Count
        
        try
        {
            $null = $datatable.rows.Add(
            $key,
            $(Get-Date),
            $InstanceId,
            $JobCount,
            $successCount,
            $failedCount,
            $JobsDisabled,
            $UnknownCount,
            $Update)
        }
        catch
        {
            Write-Log -path $LogFilePath -message "Failed to add Job to datatable - $_" -level Error
            Write-Log -path $LogFilePath -message "Data = $key,
            $(Get-Date),
            $InstanceId,
            $JobCount,
            $successCount,
            $failedCount,
            $JobsDisabled,
            $UnknownCount,
            $Update"
 -level Error
            continue
        }
    }
    
    $rowcount = $datatable.Rows.Count
    
    if ($rowcount -eq 0)
    {
        Write-Log -path $LogFilePath -message "No rows returned. No update required." -level info
        continue
    }
    
    try
    {
        Write-Log -path $LogFilePath -message "Attempting Import of $rowcount row(s)" -level info
        Write-Tvp -ErrorAction Stop 
        Write-Log -path $LogFilePath -message "Successfully Imported $rowcount row(s) of Agent Job Server into the $InstallDatabase on $($sourceserver.name)" -level info
    }
    catch
    {
        Write-Log -path $LogFilePath -message "Bulk insert failed - $_" -level Error
    }
}

END
{
    Write-Log -path $LogFilePath -message "Agent Job Server Finished"
    $sourceserver.ConnectionContext.Disconnect()
}