function PSScriptRoot { $MyInvocation.ScriptName | Split-Path }

. "$(PSScriptRoot)\scripts\Community_Functions.ps1"

$instances = get-content "$(PSScriptRoot)\servers.txt"

$jobs = @()

foreach ($instance in $instances)
{
    $result = Invoke-Sqlcmd2 -ServerInstance $instance -Database msdb -Query "
              select command from sysjobsteps where step_name like 'DatabaseBackup%'"

    foreach ($row in $result)
    {
        foreach ($item in $row.command.Split("@"))
        {
            $itemset = $item.split("=")
    
            if ($itemset[0] -match "Databases") {$databases = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "Directory") {$directory = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "BackupType") {$backuptype = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "Verify") {$verify = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "Checksum") {$checksum = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "LogToTable") {$logtotable = $itemset[1].split("'")[1]}
            if ($itemset[0] -match "CleanupTime") {$CleanupTime = $itemset[1].split(",").split(" ")[1]}

        }

        $properties = @{
            Server = $instance;
            Databases = $databases;
            Directory = $directory;
            BackupType = $backuptype;
            Verify = $verify;
            Checksum = $checksum;
            LogToTable = $logtotable;
            CleanupTime = $CleanupTime
            }

        $job = New-Object PSObject -Property $properties
        $jobs += $job
    }
}

#cls

$jobs | Select Server, BackupType, Databases, Verify, Checksum, CleanupTime, Directory | Format-Table -AutoSize


$jobs | where CleanupTime -lt 72 | Select Server, BackupType, Databases, Verify, Checksum, CleanupTime, Directory | Format-Table -AutoSize


#$jobs | Export-Excel -Path C:\presentations\PoShOla\ola.xlsx -WorkSheetname Ola -AutoSize -AutoFilter -BoldTopRow -FreezeTopRow -show 
#$jobs | Export-Excel -Path C:\presentations\PoShOla\ola.xlsx  -WorkSheetname AnotherTab -AutoSize -AutoFilter -BoldTopRow -FreezeTopRow -show 















