SQL/powershell report - Restore Points on Tape per VM

Management reporting and documentation

SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Mon Feb 15, 2016 7:04 pm

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.
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby Vitaliy S. » Mon Feb 15, 2016 7:37 pm

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!
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Mon Feb 15, 2016 8:03 pm

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.
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby Vitaliy S. » Mon Feb 15, 2016 8:05 pm

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.
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Mon Feb 15, 2016 8:19 pm

Image
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby Shestakov » Tue Feb 16, 2016 12:08 pm

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!
Shestakov
Veeam Software
 
Posts: 4856
Liked: 394 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Tue Feb 16, 2016 3:21 pm

So long as all the data can be exported into excel, that would be the most flexible.
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Tue Feb 16, 2016 8:46 pm

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-location-t23848.html#p181113
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

Re: SQL/powershell report - Restore Points on Tape per VM

Veeam Logoby ccatlett1984 » Tue Apr 05, 2016 9:20 pm 1 person likes this post

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)
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett


Return to Reporting



Who is online

Users browsing this forum: No registered users and 2 guests