Powershell generated CSV file?

PowerShell script exchange

Powershell generated CSV file?

Veeam Logoby mikedimunno » Wed Aug 12, 2015 3:35 pm

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
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Wed Aug 12, 2015 4:42 pm

I think Export-Csv commandlet would be the best option here. Thanks.
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby mikedimunno » Wed Aug 12, 2015 8:29 pm

v.Eremin wrote: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
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Thu Aug 13, 2015 9:40 am

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.
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby mikedimunno » Thu Aug 13, 2015 11:50 am

v.Eremin wrote: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
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Thu Aug 13, 2015 12:05 pm

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.
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby mikedimunno » Tue Aug 18, 2015 1:47 pm

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.
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Tue Aug 18, 2015 2:54 pm 1 person likes this post

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.
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby tsightler » Tue Aug 18, 2015 4:29 pm 1 person likes this post

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
tsightler
Veeam Software
 
Posts: 4772
Liked: 1740 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Wed Aug 19, 2015 10:12 am 1 person likes this post

While writing the last sentence, I hoped Tom would chime in and provide some optimization tricks. Glad you haven't failed me. :)
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby tsightler » Wed Aug 19, 2015 1:31 pm 1 person likes this post

v.Eremin wrote: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! :D
tsightler
Veeam Software
 
Posts: 4772
Liked: 1740 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Wed Aug 19, 2015 1:57 pm

Sure, one cannot simply leave heavy hitter call inside the loop, not knowing that Tom would go after that. :)
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby mikedimunno » Wed Aug 19, 2015 9:58 pm

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.
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Re: Powershell generated CSV file?

Veeam Logoby v.Eremin » Thu Aug 20, 2015 6:34 am

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.
v.Eremin
Veeam Software
 
Posts: 13295
Liked: 973 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Powershell generated CSV file?

Veeam Logoby mikedimunno » Wed Aug 26, 2015 9:39 pm

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.
mikedimunno
Novice
 
Posts: 7
Liked: never
Joined: Wed Aug 12, 2015 3:21 pm
Full Name: Michael DiMunno

Next

Return to PowerShell



Who is online

Users browsing this forum: No registered users and 8 guests