Please help me add a few more columns to my report

[nickl99]

I am trying to make a simple summary report for the month for each Job with: Each Server:Successes, Failures, Number of Restore Points and oldest Full/synthetic backup, Total size GB

I have got this so far working nicely, but need to add 3 more columns as shown at bottom:

Code: Select all

asnp VeeamPSSnapin

$now = Get-Date -Format yyyyMMdd_HHmm
$jobs = Get-VBRJob | Where {$_.JobType -eq "Backup"}
$results_array = @()

ForEach ($job in $jobs) {  
    $results_array += [ordered]@{JobName=$job.Name; BackupMethod=$job.Options.BackupTargetOptions.Algorithm -replace "Syntethic","Reversed"; ActiveFulls=$job.BackupStorageOptions.EnableFullBackup; SynthFulls=$job.BackupTargetOptions.TransformFullToSyntethic; RestorePoints=$job.Options.BackupStorageOptions.RetainCycles}
    }
       
#Export to CSV file
$CSVreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".csv"
$results_array | % {New-Object PSObject -Property $_} | Export-CSV $CSVreportfilename

#Export to HTML file
$HTMLreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".html"
$results_array | % {New-Object PSObject -Property $_} | ConvertTo-Html | Out-File $HTMLreportfilename

#Display a table on screen
$results_array | % {New-Object PSObject -Property $_} | ft -AutoSize

But need help formatting out to Grouped by Job listed with servers ie:

Success Failure Success % Restore Points Oldest Full Backup Size
Job 1

Server 1
Server 2
Server 3

Thanks for the all the help!!

[veremin]

Can you provide a screenshot of desired output? From plain text it's not clear what formatting you're after. Thanks.

[nickl99]

So I need one csv run against age in days (30)

Thanks! If you beat me to it I will buy you a beer (somehow)

[seanjoo]

Nick99,

Did you able to update your PowerShell script to the way you wanted? If you do, can you share the script?

[billcouper]

Hi,

This is a bit late reply perhaps but I saw this post and thought I'd have a go at solving some of the questions.
I'm not sure what 'Server' is for, since you can only connect to a single VBR server at a time, so I added a loop to go through multiple VBR servers.

Creating a pscustomobject within the loop means you don't need to do it for each export line, and can then sort by multiple properties.
Since the amount of points on disk won't always match the job settings, I added a column for that so it can be compared.

edit: I ran this against one of my veeam servers with 268 vm's in 42 jobs and it did take a minute, so if your environment is larger be patient

Code: Select all

asnp VeeamPSSnapin

$credential = get-credential # Replace this line with a secure way to get credentials automagically

$VeeamServers = @(
    'Server01',
    'Server02'
)

$now = Get-Date -Format yyyyMMdd_HHmm
$results_array = @()

foreach ($server in $VeeamServers) {
    Connect-VBRServer -server $Server -Credential $credential
    $jobs = Get-VBRJob | ? {$_.IsBackup}
    ForEach ($job in $jobs) {
        $lastsession = $job.FindLastSession()
        foreach ($tasksession in $lastsession.GetTaskSessions()) {
            $objResult = [pscustomobject][ordered]@{
                Server = $server
                JobName = $job.Name
                VmName = $tasksession.Name
                LastResult = $job.GetLastResult()
                BackupMethod = $job.Options.BackupTargetOptions.Algorithm -replace "Syntethic","Reversed"
                RestorePoints = $job.Options.BackupStorageOptions.RetainCycles
                PointsOnDisk = (get-vbrbackup -Name $job.Name | Get-VBRRestorePoint -Name $tasksession.Name | Measure-Object).Count
            }
        $results_array += $objResult
        }
    }
}
       
#Export to CSV file
$CSVreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".csv"
$results_array | sort Server,JobName,VmName | Export-CSV $CSVreportfilename

#Export to HTML file
$HTMLreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".html"
$results_array | sort Server,JobName,VmName | ConvertTo-Html | Out-File $HTMLreportfilename

#Display a table on screen
$results_array | sort Server,JobName,VmName | ft -AutoSize

[farhad090]

@billcouper

Latest Script is excellent.

2 Problems:
Is It possible without this statement? "$credential = get-credential # Replace this line with a secure way to get credentials automagically" cause It requires username password every time

And how to add
"Backup-Start" (Latest start time)
"Runtime" for ex: 02:10
"Backup Size" MB

Thanks In Advance.

[billcouper]

Hi farhad090

#1:
There is a reason I added the comment "Replace this line with a secure way to get credentials automagically". There are many ways to do this. A simple method would be to save the password to encrypted file. There may be some risk depending where you save the text file and who has access to make a copy of it. Google search 'powershell encrypt password in text file' and use one of the search results, such as the one from Altaro: https://www.altaro.com/msp-dojo/encrypt ... owershell/

#2a:
Use a time-span object, such as:

Code: Select all

$startTime = get-date
Run-Potato
$endTime = get-date
$timeSpan = $endTime - $startTime
write-output "The potato ran for $($timeSpan.TotalSeconds) seconds"

Review the properties of the $timeSpan object and you will be able to work it out.

#2b:
Pulling the backup size is trickier. I would suggest searching this forum first and have a go at it. Come back if you need assistance, with whatever code you have come up with that isn't working.

[billcouper]

Oh. It seems I didn't read/understand your post correctly, about the start time, run time etc etc. Look at this example:

Code: Select all

$Job = Get-VBRJob -Name "Job Name"
$Session = $job.FindLastSession()
$JobStartTime = $Session.CreationTime
$TimeSpan = $Session.EndTime - $Session.CreationTime
$BackedUpData = $Session.Info.BackupTotalSize/1GB
$BackupSize = $Session.Info.BackedUpSize/1GB

Again, evaluate the $TimeSpan object properties to get what you need from it. Do some math operations on the backup size info to make it more readable, such as rounding to 2 decimal places as an example:

Code: Select all

[math]::round($BackupSize,2)

[albertwt]

Hi Bill,

Thanks for sharing the script here.

Did you finally manage to update the script?

[albertwt]

Finally, I was able to modify the script into the below:

Code: Select all

Add-PSSnapIn VeeamPSSnapin

$credential = Get-Credential # Replace this line with a secure way to get credentials automagically

$VeeamServers = @(
    'S1PRDBUV-1', #Veeam Server 1
    'S2PRDBUV-1' #Veeam Server 2
)

$now = Get-Date -Format yyyyMMdd_HHmm
$results_array = @()

foreach ($server in $VeeamServers) {
    Connect-VBRServer -server $Server -Credential $credential
    $jobs = Get-VBRJob | Where-Object {$_.IsBackup}
    ForEach ($job in $jobs) {
        $lastsession = $job.FindLastSession()
        foreach ($tasksession in $lastsession.GetTaskSessions()) {
            $objResult = [pscustomobject][ordered]@{
                Server          = $server
                JobName         = $job.Name
                VmName          = $tasksession.Name
                LastResult      = $job.GetLastResult()
                BackupMethod    = $job.Options.BackupTargetOptions.Algorithm -replace "Syntethic", "Reversed"
                RestorePoints   = $job.Options.BackupStorageOptions.RetainCycles
                PointsOnDisk    = (Get-VBRBackup -Name $job.Name | Get-VBRRestorePoint -Name $tasksession.Name | Measure-Object).Count
                JobStartTime    = $lastsession.CreationTime
                TimeSpan_HHMMSS = [System.TimeSpan]::Parse($lastsession.EndTime - $lastsession.CreationTime)
                BackedUpDataGB  = [Math]::Round(($lastsession.Info.BackupTotalSize / 1GB), 2)
                BackupSizeGB    = [Math]::Round(($lastsession.Info.BackedUpSize / 1GB), 2)
            }
            $results_array += $objResult
        }
    }
}

#Export to CSV file
$CSVreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".csv"
$results_array | Sort-Object Server, JobName, VmName | Export-CSV $CSVreportfilename -NoTypeInformation

#Export to HTML file
$HTMLreportfilename = "C:\temp\BackupReport-Method-Retention-" + $now + ".html"
$results_array | Sort-Object Server, JobName, VmName | ConvertTo-Html | Out-File $HTMLreportfilename

#Display a table on screen
$results_array | Sort-Object Server, JobName, VmName | Format-Table -AutoSize

Output column is now:

Server JobName VmName LastResult BackupMethod RestorePoints PointsOnDisk JobStartTime TimeSpan_HHMMSS BackedUpDataGB BackupSizeGB

[ian.salgado]

Hi,

To the table of values below:

Code: Select all

$objResult = [pscustomobject][ordered]@{
                Server          = $server
                JobName         = $job.Name
                VmName          = $tasksession.Name
                LastResult      = $job.GetLastResult()
                BackupMethod    = $job.Options.BackupTargetOptions.Algorithm -replace "Syntethic", "Reversed"
                RestorePoints   = $job.Options.BackupStorageOptions.RetainCycles
                PointsOnDisk    = (Get-VBRBackup -Name $job.Name | Get-VBRRestorePoint -Name $tasksession.Name | Measure-Object).Count
                JobStartTime    = $lastsession.CreationTime
                TimeSpan_HHMMSS = [System.TimeSpan]::Parse($lastsession.EndTime - $lastsession.CreationTime)
                BackedUpDataGB  = [Math]::Round(($lastsession.Info.BackupTotalSize / 1GB), 2)
                BackupSizeGB    = [Math]::Round(($lastsession.Info.BackedUpSize / 1GB), 2)

how can i add to check of each job to see if
1. backup job is enable or disable ?
2. Name of local Repository the backup is going into (i have 2x repos)

[Mildur]

Hi Ian

Can you try it with the following commands?

1) JobEnabled = $job.IsScheduleEnabled
2) RepositoryName = Get-VBRBackupRepository | where-object {$_.id -eq $job.info.TargetRepositoryId} | select name

Thanks
Fabian

[ian.salgado]

Thanks Fabian,

That worked !!

cheers

[ian.salgado]

Hi All,

one other question

1. i would like to see which proxy is used for each job

2. when the next schedule is for the job

cheers

[Mildur]

A vm backup job can have two configurations. Auto proxy or manual proxy assignment.
I added an IF - Else query to get the right output for either of the configurations:

1)

Code: Select all

ProxyName = If ($job.SourceProxyAutoDetect -eq $True) {"Automatic proxy selection is enabled."} else {($job | Get-VBRJobProxy).name}

2)

Code: Select all

JobNextrun = ($job | Get-VBRJobScheduleOptions).NextRun

Thanks
Fabian

[ian.salgado]

Hi

ok something similar now but for REPLICATION jobs ?

could you please help ?

cheers

[Mildur]

Hi Ian

What properties do you need to know from replica jobs?
Get-VBRJob | Where-Object {$_.IsReplica} will save replica jobs to the variable $jobs.
From there you have to decide which properties you want to have in your report.
Some properties from the backup job script might work for replicas too. Some additional ones could be required.

Scripting is always a try and error process.

Thanks
Fabian

[iwan23]

how to get data (Name backup, Data transferred, summary duration and job started, next run, last result and type) from veeam backup and replication?