.\MySQL_Functions.ps1
Add-PSSnapin VeeamPsSnapIn
function UploadCSV_SQL() {
$Dir=”C:\CSVLogs”
#ftp server
$ftp = ‘ftp://FTPServer’
$user = ‘UserName’
$pass = ‘PassWord’
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
#list every sql server trace file
foreach($item in (dir $Dir “*SQL – $date.csv”)){
“Uploading $item…”
$uri = New-Object System.Uri($ftp+$item.Name)
$webclient.UploadFile($uri, $item.FullName)
}
}
$Jobs = Get-VBRJob | ?{$_.Name -match “”}
$date = get-date -Format ddMMyyyy
foreach ($job in $Jobs) {
$Name = $job.Name
$tabName = “SampleTable”
#Create Table object
$table = New-Object system.Data.DataTable “$tabName”
#Define Columns
$col1 = New-Object System.Data.DataColumn HostName,([string])
$col2 = New-Object system.Data.DataColumn JobName,([string])
$col3 = New-Object system.Data.DataColumn JobCreated,([string])
$col4 = New-Object system.Data.DataColumn LastRunTime,([string])
$col5 = New-Object system.Data.DataColumn Retention,([string])
$col6 = New-Object system.Data.DataColumn NextRunTime,([string])
$col7 = New-Object system.Data.DataColumn IsScheduled,([string])
$col8 = New-Object system.Data.DataColumn LastResult,([string])
$col9 = New-Object system.Data.DataColumn Status,([string])
#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)
$table.columns.add($col8)
$table.columns.add($col9)
$result = $job.IsRunning
$latrun = $job.ScheduleOptions.LatestRunlocal
$runin = $job.IsRunning
$laststat = $job.GetLastState()
$lastres = $job.GetLastResult()
$nextrun = $job.ScheduleOptions.NextRun
$enabled = $job.IsScheduleEnabled
if($result -eq $True){
$status = “Job Running”
}
elseif ($latrun -gt $bckuwindow){
$status = “$($lastres) on $($latrun.ToShortDateString()) at $($latrun.ToShortTimeString())”
}
elseif ($nextrun -ne “”){
$nextruntd = [datetime]$nextrun
$status = “Next Run $($nextruntd.DayOfWeek) $($nextruntd.Day) at $($nextruntd.ToShortTimeString())”
}
elseif ($enabled -eq $false){
$status = “Job Disabled”
}
elseif ($nextrun -eq “”){
$status = “Job Not Scheduled”
}
else {
$status = “Error Loading Job Details – Investigate”
}
#Create a row
$row = $table.NewRow()
#Enter data in the row
$row.HostName = $env:COMPUTERNAME
$row.JobName = $job.Name
$row.JobCreated = $job.ScheduleOptions.StartDateTimeLocal
$row.LastRunTime = $job.ScheduleOptions.LatestRunlocal
$row.Retention = $job.BackupStorageOptions.RetainDays
$row.NextRunTime = $job.ScheduleOptions.NextRun
$row.IsScheduled = $job.IsScheduleEnabled
$row.LastResult = $job.GetLastResult()
$row.Status = $status
#Add the row to the table
$table.Rows.Add($row)
$table | ft -AutoSize
$tabCsv += $table
# $table | Export-Csv -Path “C:\$env:COMPUTERNAME.csv” -noType
}
#Display the table
#$table
#NOTE: Now you can also export this table to a CSV file as shown below.
$tabCsv | Export-Csv -Path “C:\CSVLogs\BackupServerName_SQL – $date.csv” -NoTypeInformation
Start-Sleep -s 10
UploadCSV_SQL