SQL/powershell report - Restore Points on Tape per VM

[ccatlett1984]

I need to pull some reporting for management, basically I need to list the dates we have valid restore points (on tape) for each of a list of vm's.
I'm Currently on the latest build of v8, was holding off on the v9 upgrade. (Would upgrade in a heartbeat if it would enable me to get this data.)

The built-in reporting is useless for this task, and the number of vm's I need to report on (~80) makes manually opening the restore point dialog not feasible.

I'm hoping someone might have been able to pull a report together and be willing to share.

I've found a list of restore points for each vm, but I haven't been able to limit it to restore points on tape.

Code: Select all

SELECT *
  FROM [VeeamBackup].[dbo].[WmiServer.VmRestorePointsView]
  ORDER BY vm_name,vm_restore_point_date

[VeeamBackup].[dbo].[Backup.Model.OIBs] also looks promising, but I'm not sure how to limit it to tape restore points only.

[Vitaliy S.]

Hi Chris,

It's quite surprising to hear that built-in backup reporting of Veeam ONE is useless. Actually, you're the first one that says this

Can you please provide a bit more details on what you're missing from our predefined reports > Tape Backups and Tape Vaults Overview?

Thanks!

[ccatlett1984]

For all vm's (~80) I need to list in a tabular format, every restore point that made it to tape.

For small numbers of tapes, maybe the reports would be usable, but I wrote to over 500 tapes in the last year.

Tape Backups: it will take hours to drill into every tape session to pull this data, since the drill-in is lost when saving to excel.

Tape Vault Overview: Same as tape backups report.

The data is there, its just not in a format that I can use to report the information I need.

[Vitaliy S.]

Ok, can you please let us know what format/what data and columns you would like to have? We are thinking on providing custom reporting for B&R data, so your feedback would be welcomed.

[ccatlett1984]

[Shestakov]

Thanks for the feedback, Chris!

There is always a trade-off between showing only basic info in the report body with detailed info put into drill-downs and showing all the info in the body.
First is faster and more convenient to read, second is more appropriate for cases like yours.

What do you think of an option to choose where you want the detailed info? Or if you can decide if you want the drill-downs in the exported excel file?
Thanks!

[ccatlett1984]

So long as all the data can be exported into excel, that would be the most flexible.

[ccatlett1984]

I found some powershell that gets me very close to the data I need, I just need to know how to filter to only see the restore points on tape.
powershell-f26/restore-point-storage-lo ... ml#p181113

[ccatlett1984]

Here is the Code I'm using:

Code: Select all

asnp "VeeamPSSnapIn"

remove-item "C:\veeam_restore_points_on_tape.csv" -force
$veeam_vms = Find-VBRViEntity | Where-Object {$_.id -like "*_vm*"} | sort name
$backupcheckdate = (get-date).AddDays(-31)
$colrestorepoints= @()
foreach($vm in $veeam_vms)
{
$veeamrestorepoints = $null
$vmname = $vm.name
#write-host "Checking $vmname"
write-host "working on $vmname"
$veeamrestorepoints = Get-VBRRestorePoint -Name $vmname | Where-Object {($_.GetBackup().JobType -eq "VmTapeBackup") -AND ($_.creationtime -ge $backupcheckdate)} | Sort-Object $_.creationtime -Descending
$colrestorepoints += $veeamrestorepoints
}

$colrestorepoints | export-csv "c:\veeam_restore_points_on_tape.csv" -NoTypeInformation

Then I run a 2nd script that populates that into an excel sheet for me.

Code: Select all

$currentdate = Get-Date -Format yyyy-MM-dd
$year = Get-Date -Format yyyy
$initialDirectory ="c:\VM Restore Points on Tape"
$filename = "VM_Restore_Points_Check_$currentdate.xlsx"
$file =  "$initialDirectory\$filename"

#Declare Functions
## function to close all com objects
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

$colrestorepoints = Import-Csv "c:\veeam_restore_points_on_tape.csv"



$vms1 = ($colrestorepoints | group {$_.name} | select name | sort name).name


#Select backup info
$backupinfo = $colrestorepoints | select Name, creationtime, isconsistent | sort Name, creationtime

#Set to $True to see the work happening.
$debug = $false
#This starts excel and loads the file
$Excel = New-Object -Com Excel.Application
#do you want excel visible? (Set to yes for troubleshooting.)
$Excel.Visible = $debug
#do you want to be able to interact?
$Excel.DisplayAlerts = $debug
$Excel.ScreenUpdating = $debug
$Excel.Visible = $debug
$Excel.UserControl = $debug
$Excel.Interactive = $debug
$Excel.SheetsInNewWorkbook = 1
$ExcelWorkBook= $Excel.Workbooks.Add()
#Makes the first sheet active
$ExcelWorkSheet = $ExcelWorkBook.worksheets.item(1)
$ExcelWorkSheet.Activate()

#Start working with cells
$ExcelCells = $ExcelWorkSheet.Cells

#Populate the First Column
$ExcelCells.item(1,1) = "Virtual Machines"
$ExcelCells.item(1,1).font.bold=$True
$ExcelCells.item(1,1).font.size=12
$a = 2
foreach ($vm in $vms1)
{
$ExcelCells.item($a,1) = $vm
$a = $a+1
}

#Get the last 30 days into a variable
$coldatestofill = @()
for ($i = 1; $i -le 31; $i++)
{
 $d = ((Get-Date).AddDays(-$i))
 $d2 = $d.ToString("MM/dd/yyyy")
 $coldatestofill +=$d2
}

#populate the First Row with the last 30 days
$b = 2
$xlCenter = -4108
$coldatestofill = $coldatestofill | sort -Descending
foreach ($date in $coldatestofill)
{
$ExcelCells.item(1,$b) = $date
$ExcelCells.item(1,$b).NumberFormat = "[$-en-US]dddd, mm/dd/yyyy"
$ExcelCells.item(1,$b).HorizontalAlignment = $xlCenter
$b = $b+1
}

#Create Arrays
$colvms = @()
$coldates = @()

#Populate the arrays
$dates = ($ExcelWorkSheet.UsedRange | Where-Object {($_.column -ne 1) -AND ($_.row -eq 1)} | select row, column, value2, text)
foreach ($date in $dates)
{
$datevalue = $date.value2
$datecolumn = $date.column
$daterow = $date.row
$dateformatted = get-date ([datetime]::FromOADate($datevalue)) -Format 'yyyy/MM/dd'
$dateinfo = new-object System.Object
$dateinfo | Add-Member -MemberType NoteProperty -name 'Date' -Value $dateformatted
$dateinfo | Add-Member -MemberType NoteProperty -name 'Column' -Value $datecolumn
$dateinfo | Add-Member -MemberType NoteProperty -name 'Row' -Value $daterow
$coldates += $dateinfo
}
#Ignore empty date fields
$coldates = $coldates | Where-Object {$_.date -ne "1899/12/30"}

$vms = ($ExcelWorkSheet.UsedRange | Where-Object {($_.column -eq 1) -AND ($_.row -ne 1)} | select row, column, value2, text)
foreach ($vm in $vms)
{
$vmname = $vm.text
$vmrow = $vm.row
$vmcolumn = $vm.column
$vminfo = new-object System.Object
$vminfo | Add-Member -MemberType NoteProperty -name 'name' -Value $vmname
$vminfo | Add-Member -MemberType NoteProperty -name 'Column' -Value $vmcolumn
$vminfo | Add-Member -MemberType NoteProperty -name 'Row' -Value $vmrow
$colvms += $vminfo
}
#Ignore empty database fields
$colvms = $colvms | Where-Object {$_.name -ne ""}

#update cells
foreach ($vm1 in $backupinfo)
{
write-output $vm1.name
$creationdate = (Get-Date $vm1.CreationTime -Format yyyy/MM/dd).ToString()
write-output $creationdate
write-output $vm1.isconsistent
$row = ($colvms | Where-Object {$_.name -eq $vm1.name}).row
write-output "row $row"
$column = ($coldates | Where-Object {$_.date -eq $creationdate}).column
write-output "column $column"
$ExcelCells.item($row,$column) = $vm1.IsConsistent
}
#Auto Fit all the columns
[void]$ExcelCells.entireColumn.Autofit()

#Save the file
$ExcelWorkBook.SaveAs($file)
$ExcelWorkBook.Close()
$Excel.quit()

## close all object references
Release-Ref($ExcelWorkSheet)
#Release-Ref($ExcelWordBook)
Release-Ref($Excel)