Powershell generated CSV file?

[mikedimunno]

Hi,
Frequently, the company I work for finds the need to generate reports regarding success vs failure rates of our Veeam backup jobs, in Excel format. Presently, someone manually creates an Excel file and records the current date, the name of the server being backed up, the number of successful backups, the number of failed backups, and calculates the percentage of success. I am seeking to automate this process as much as possible. Can anyone with more powershell experience than myself assist with creating a script that will generate a CSV file containing the information that we collect manually at this time?

Thanks,

Mike

[veremin]

I think Export-Csv commandlet would be the best option here. Thanks.

[mikedimunno]

I think Export-Csv commandlet would be the best option here. Thanks.

Thanks - yes that makes sense but I am having trouble pulling the number of sucessful backups and the number of failed backups. So far I have the following script which I located somewhere that does part of the job (the name of the server):

Code: Select all

$Collection = @()
$jobs = Get-VBRjob
Foreach ($job in $jobs){
$objects = $job.GetObjectsInJob()
                foreach ($object in $objects){
                $Details = New-Object PSObject
                $Details | Add-Member -Name "Veeam Job" -MemberType NoteProperty -Value $job.name
                $Details | Add-Member -Name "Server" -MemberType NoteProperty -Value $object.name
                $Collection += $Details
                
                        }
}
$Collection | Export-CSV C:\report.csv

Mike

[veremin]

What particular time period this script should cover?

What do you consider as successful and as failed backup? For instance, if a backup job finished successfully, but during a third retry only, was it a successful pass or not? If yes, should the script disregard previous failed sessions?

Thanks.

[mikedimunno]

What particular time period this script should cover?

What do you consider as successful and as failed backup? For instance, if a backup job finished successfully, but during a third retry only, was it a successful pass or not? If yes, should the script disregard previous failed sessions?

Thanks.

Vladimir,
Thank you very much for the response! We would consider any successful attempt at the job, even if it's on the third retry, to be a success for the job itself. So if it takes all 3 retries to get the job to succeed but in the end it does run and complete without error, we would consider that successful pass. In that case, we would want to disregard any failures that were part of the same backup attempt.

We would be looking at the last 30 days worth of backups.

Mike

[veremin]

Mike,

In the way I see it, for one job monthly sessions, as well as, failed and successful ones can be found in the following way (the failed retry sessions are disregarded, because they don't contribute to the whole picture anyhow):

Code: Select all

$Job = Get-VBRJob -Name "Name of your job"
$MonthlySessions = Get-VBRBackupSession | where {$_.origJobname -eq $Job.name} | where {$_.creationtime -ge (Get-Date).AddDays(-30)}
$FailedSessions = $MonthlySessions | where {($_.result -eq "Failed") -and ($_.isretrymode -eq $False)}
$SuccessfulSessions = $MonthlySessions | where {($_.result -eq "Success")}

Thanks.

[mikedimunno]

Thanks again for the reply and the info! Powershell is not my strong suit - can you help me integrate the number of failed/successful jobs with the original script that I provided earlier in this thread which generated a CSV file with the name of the backup job and the server name? Ideally I would like one CSV file with the name of any jobs, the server that they are backing up, and the number of successes and failures over the past 30 days.

[veremin]

I've changed your code slightly and added the required portion. Take a look at the example provided below and see whether it meets your expectations:

Code: Select all

$Collection = @()
Foreach ($job in Get-VBRjob){
$Details = New-Object PSObject 
$Details | Add-Member -Name "Veeam Job" -MemberType NoteProperty -Value $job.name
$Details | Add-Member -Name "Servers" -MemberType NoteProperty -Value ($job.GetViOijs().name -join ",")

$MonthlySessions = Get-VBRBackupSession | where {$_.origJobname -eq $Job.name} | where {$_.creationtime -ge (Get-Date).AddDays(-30)}
$FailedSessions = $MonthlySessions | where {($_.result -eq "Failed") -and ($_.isretrymode -eq $False)}
$SuccessfulSessions = $MonthlySessions | where {($_.result -eq "Success")}

$Details | Add-Member -Name "Number of Successful Sessions" -MemberType NoteProperty -Value $SuccessfulSessions.count
$Details | Add-Member -Name "Number of Failed Sessions" -MemberType NoteProperty -Value $FailedSessions.count

$Collection += $Details
}
$Collection

Be aware, code takes some time to execute. So, I believe, it can be optimized further.

Thanks.

[tsightler]

Here's another small modification to move the Get-VBRBackupSession outside of the loop since that's a slow call and is probably what would eat the most time. This improves the run time in my lab setup from >1 minute to ~15 seconds. Should save even more time for a large environment with a lot of jobs.

Code: Select all

$MonthlySessions = Get-VBRBackupSession | where {$_.creationtime -ge (Get-Date).AddDays(-30)}
$Collection = @()
Foreach ($job in Get-VBRjob){
$Details = New-Object PSObject 
$Details | Add-Member -Name "Veeam Job" -MemberType NoteProperty -Value $job.name
$Details | Add-Member -Name "Servers" -MemberType NoteProperty -Value ($job.GetViOijs().name -join ",")

$MonthlyJobSessions = $MonthlySessions | where {$_.origJobname -eq $Job.name}
$FailedJobSessions = $MonthlyJobSessions | where {($_.result -eq "Failed") -and ($_.isretrymode -eq $False)}
$SuccessfulJobSessions = $MonthlyJobSessions | where {($_.result -eq "Success")}

$Details | Add-Member -Name "Number of Successful Sessions" -MemberType NoteProperty -Value $SuccessfulJobSessions.count
$Details | Add-Member -Name "Number of Failed Sessions" -MemberType NoteProperty -Value $FailedJobSessions.count

$Collection += $Details
}
$Collection

[veremin]

While writing the last sentence, I hoped Tom would chime in and provide some optimization tricks. Glad you haven't failed me.

[tsightler]

While writing the last sentence, I hoped Tom would chime in and provide some optimization tricks. Glad you haven't failed me.

I thought you had to be teasing me with that code, leaving a heavy hitter call like that inside the loop. You had to know I'd go after that!

[veremin]

Sure, one cannot simply leave heavy hitter call inside the loop, not knowing that Tom would go after that.

[mikedimunno]

Thanks very much guys - this is great! We are 1 step away from exactly what I need.

I just realized now that this does not count any jobs that end with a status of "Warning". I tried to add this in myself and was unsucessful. Can we add anything that ends as a "warning" status to the "success" count? So for our purposes, both "success" and "warning" would count as a successful job - the only thing that would count as a failure would be an outright "failed" status.

Thanks very much again! You've been of great help.

[veremin]

I don't have any warning sessions at hand, nevertheless, I believe the following code should answer your expectations:

Code: Select all

    $MonthlySessions = Get-VBRBackupSession | where {$_.creationtime -ge (Get-Date).AddDays(-30)}
    $Collection = @()
    Foreach ($job in Get-VBRjob){
    $Details = New-Object PSObject
    $Details | Add-Member -Name "Veeam Job" -MemberType NoteProperty -Value $job.name
    $Details | Add-Member -Name "Servers" -MemberType NoteProperty -Value ($job.GetViOijs().name -join ",")

    $MonthlyJobSessions = $MonthlySessions | where {$_.origJobname -eq $Job.name}
    $FailedJobSessions = $MonthlyJobSessions | where {($_.result -eq "Failed") -and ($_.isretrymode -eq $False)}
    $SuccessfulJobSessions = $MonthlyJobSessions | where {($_.result -eq "Success")}
    $WarningJobSessions = $MonthlyJobSessions | where {($_.result -eq "Warning")}

    $Details | Add-Member -Name "Number of Successful Sessions" -MemberType NoteProperty -Value $SuccessfulJobSessions.count
    $Details | Add-Member -Name "Number of Warning Sessions" -MemberType NoteProperty -Value $WarningJobSessions.count
    $Details | Add-Member -Name "Number of Failed Sessions" -MemberType NoteProperty -Value $FailedJobSessions.count

    $Collection += $Details
    }
    $Collection

Thanks.

[mikedimunno]

All,
Thanks very much for your assistance here so far - we're very close to having what we need.

We've taken this a step further and a colleague of mine and myself modified the provided script to include a test file restore. Each month as part of our review, we restore a file as a test from the Veeam backup. This script now automates that, but we've run into one snag.

When we go to assign the path to the file we wish to restore by referencing a mounted restore point, we are coming across some situations where we can't identify the Windows drive letter from a restore point name. For instance, we do this with this code:

Code: Select all

$file = "C:\VeeamFLR\" + $vmName + "\Volume0" + (Split-Path $origfile -NoQualifier)

However, this references "Volume0" as being the name of the mounted restore point. In some cases, "Volume0" may not be drive C (the location where we have placed our test file). We need a way of doing this whereby Veeam always references the appropriate volume containing the test file (this would be whatever volume is associated with drive C).

Our full script follows for reference:

Code: Select all

#Add-PSSnapin "VeeamPSSnapIn"
$MonthlySessions = Get-VBRBackupSession | where {$_.creationtime -ge (Get-Date).AddDays(-30)}
$ReviewDate = Get-Date
$Collection = @()
$origfile = "c:\boot.ini"
Foreach ($job in Get-VBRjob){
$Details = New-Object PSObject 

# Puts Job Name in the report
$Details | Add-Member -Name "Veeam Job Name" -MemberType NoteProperty -Value $job.name
# Puts List of Servers in each job into report 
$Details | Add-Member -Name "Servers" -MemberType NoteProperty -Value (($job.GetObjectsInJob() | foreach { $_.Name }) -join "," ) 

# Pulls Monthly Results Failed / Warning / Success
$MonthlyJobSessions = $MonthlySessions | where {$_.origJobname -eq $Job.name}
$FailedJobSessions = $MonthlyJobSessions | where {($_.result -eq "Failed") -and ($_.isretrymode -eq $False)}
$WarningJobSessions = $MonthlyJobSessions | where {($_.result -eq "Warning")}
$SuccessfulJobSessions = $MonthlyJobSessions | where {($_.result -eq "Success")}

# Puts review date in report
$Details | Add-Member -Name "Date of Review" -MemberType NoteProperty -Value $ReviewDate

# Adds Success / Warning / Fail to report
$Details | Add-Member -Name "Number of Successful Sessions" -MemberType NoteProperty -Value $SuccessfulJobSessions.count
$Details | Add-Member -Name "Number of Warning Sessions" -MemberType NoteProperty -Value $WarningJobSessions.count
$Details | Add-Member -Name "Number of Failed Sessions" -MemberType NoteProperty -Value $FailedJobSessions.count

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ restoration~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#pulls VM Name
$vmName  = ($job.GetObjectsInJob() | foreach { $_.Name })

#Sets restoration destination
$destPath = "c:\test\" + $vmName + "\"
new-item $destPath PowerShell -type directory -force

#initiates restoration
$result = Get-VBRBackup | Get-VBRRestorePoint | sort CreationTime -Descending | where {$_.VMName -eq "$vmName"} | select -First 1 | Start-VBRWindowsFileRestore

#points to mounted restore point, need to pull location of C drive volume automatically instead of static
$file = "C:\VeeamFLR\" + $vmName + "\Volume0" + (Split-Path $origfile -NoQualifier)

#copies specified file
Copy-Item $file $destPath -Force
write-host "Server = " $vmName "Restored File Path = " $file "Destination= " $destPath

#adds details to the report
$Details | Add-Member -Name "Restored Server" -MemberType NoteProperty -Value $vmName
$Details | Add-Member -Name "Restored Path" -MemberType NoteProperty -Value $file
$Details | Add-Member -Name "Restored Destination" -MemberType NoteProperty -Value $destPath
Stop-VBRWindowsFileRestore $result

$Collection += $Details
}
$Collection | Export-CSV BackupReview.csv

Thanks so much in advance for any additional help you could provide.

[veremin]

What about using something similar? (see "$_.Driveletter -eq "C:"" portion). Mountsession parameter was unintentionally removed in version 8, and has been added back in Update 2. So, as long as, you're leveraging VB&R v8 Update 2, you should be ok with the proposed script. Thanks.