Query database for VM Daily Protection Status report

[tedout]

Hello,

I need an outside source to get data that is present in this report https://helpcenter.veeam.com/docs/one/r ... l?ver=95u4 , specifically the data with the totals success/failures that is on the main graph and was wondering if there is any way to get those values with a query directly to the Database (which according to that doc should be the Veeam Backup & Replication servers database).

I can't seem to get those values from the API and so was thinking of doing it directly to the database as an alternative.

Has anyone done this query before?


Cheers

[wishr]

Hi Tedout,

This data is only available in Veeam ONE and cannot be queried from the database directly. Why not simply use Veeam ONE? What's your use-case?

Thanks

[veremin]

If you don't mind scripting, then, check this PowerShell report; might be exactly what you're looking for. Thanks!

[tedout]

Our use case is loading that data into Power Bi.

To have a database as the source is just that, a connection to the database refreshed whenever you want.

I don't mind doing it via powershell but that would mean schedule a script to run, have a place for it to be stored and run, have it have access to the power bi file we are using which is published, etc.

"Why not simply use Veeam ONE? What's your use-case?" What do you mean? is it possible externally to access that data on Veeam ONE?

Thank you all

[tedout]

Hi Tedout,

This data is only available in Veeam ONE and cannot be queried from the database directly. Why not simply use Veeam ONE? What's your use-case?

Thanks

The documentation says:

To create the report, Veeam ONE Reporter retrieves and analyses data from the following sources:

Historical data on restore points created during the specified reporting period (gathered from Veeam Backup & Replication servers)
List of VMs in virtual inventory (gathered from Veeam Backup & Replication servers)

Doesn't this mean that that data is on Veeam Backup & Replication servers database? I'm just fishing here

[wishr]

Any manual interventions into both VB&R and VONE databases are not supported and our support engineers will not be able to assist you if any issues arise because of such actions.

VONE does not take any data directly from the B&R database. Instead, it operates WMI to pull the data from B&R server for further operations with it.

Could you please let us know what is the reason for not going the most simple way implying pre-defined report usage? Also, the aforementioned alternate PowerShell solution provided by Veremin should fit your needs if the use of ONE does not for some reason.

Thanks!

[tedout]

Like I mentioned we need to publish that data on a Power Bi dashboard which has other information un-related to Veeam or backups but those totals of failed/sucesses, etc need to be added there.

We're collecting data from several sources related to infrastructure like monitoring data, network data, etc and by far the most difficult to access and gather on Power Bi has been Veeam data on the number of sucessfull and failed backups...and now you mention that collecting that from the database is not supported

[PetrM]

Hello!

As my colleague Fedor correctly said, manual data collection from configuration database is not supported officially: it means that our support engineers won't have a possibility to help you to resolve some issues which may happen during this querying.

However, you may take a look at this table: [Backup.Model.BackupTaskSessions], columns: status and object_name.
I think that the number of successfully processed objects would be the number of protected VMs.

Please make sure no entries are updated in Veeam database during external applications/scripts functioning.
And please keep in mind that sometimes names of some tables can be changed after upgrade.

Thanks!

[tedout]

No changes will be made for sure to the database as the user we have has read only privileges.

Wasn't my intention to mess with something that is not supported or standard. But I can't believe that we are the only ones that need this data to be sent to another source other than email or displayed on the Veeam web console.

Really wish the API would get this feature.

Thank you all for your help

[wishr]

Hi Ted,

We already offer two additional sources of information:
1. PowerShell
2. RESTful API, part of Enterprise Manager that comes along with B&R.

Both of these sources contain data about job sessions, job status, etc. You could check here for an example of what could be done using RESTful API.

There is another good example, but it's for Veeam Backup for Office 365, not for Backup & Replication.

Thanks!

[tedout]

But that was my initial attempt, how to get that data present on the reports from the API...

Everyone says that is not possible.

Is there any way to get those numbers through the API?

[wishr]

It's not recommended and not supported to query the database directly, but you are welcome to use any tools we provide that are intended for pulling B&R data. Please be aware that to use RESTful API you need Enterprise Manager with Enterprise Plus license.

Here is a list of possible requests. You may want to take a look at backup sessions, for example.

Thanks

[tedout]

We have that license but I thought we couldn't get that data from the API, the database query was an alternative option not our first option.

Is it possible to get these values from the API? I wasn't able to and was told it wasn't possible

[wishr]

Sure, please refer to the reference guide provided above. Thanks.

[vinack]

Hello,
instead of querying the database, I use the "Notifications" found in "Storage > Advanced settings" and create a "BackupStatus" VM Attribute.
Using this script (I use PRTG, but script should be easier modified) to check this attribure ...
Using arguments, you can ignore machines or folders, consider only specifics folders and/or powered on machine, specify age of last backup.
In my infrastructure, I use a special "NO-BACKUP" or "NO_BACKUP" Folder to place vm without backup; a flag allow to ignore this folder. Same case with a similar string found in the "note" of the VM.
Regards - Vincent

Code: Select all

# Original Script by https://kb.paessler.com/en/topic/29313-vmware-snapshots
# Modified by Nexpert AG for use with PRTG Network Monitor
# adapted by myself 

Param(
[string]$ComputerName = "vcenter",
[string]$address = '',
[string]$UserName = "administrator@vsphere.local",
[string]$user = '',
[string]$Password = 'xxxx',
[string]$IgnoreList = 'FOeFj5gc,asdflkj23C',
[string]$IgnoreFolders = 'asfasf,lkjlj',
[string]$OnlyFolders = '',
[string]$Age = 1,
[switch]$IgnorePoweredOff = $False,
[switch]$UseNoBackupInNotes = $False,
[switch]$ignoreNoBackupFolder = $False,
[switch]$v = $False
)
if ($address) {
	$ComputerName = $address
}
if ($user) {
	$UserName = $user
}
if ($v) {
	$VerbosePreference = "Continue"
}
#create credentials 
$SecPassword = ConvertTo-SecureString $Password -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential ($UserName, $secPassword)

#Import-Module VMware.VimAutomation.Core | Out-Null
if([System.IO.File]::Exists('C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Modules\VMware.VimAutomation.Sdk\VMware.VimAutomation.Sdk.psd1')) {
	Import-Module "C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Modules\VMware.VimAutomation.Sdk\VMware.VimAutomation.Sdk.psd1"
	Import-Module "C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Modules\VMware.VimAutomation.Common\VMware.VimAutomation.Common.psd1"
	Import-Module "C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Modules\VMware.VimAutomation.Cis.Core\VMware.VimAutomation.Cis.Core.psd1"
	Import-Module "C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Modules\VMware.VimAutomation.Core\VMware.VimAutomation.Core.psd1"
} else {
	if([System.IO.File]::Exists('C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules\VMware.VimAutomation.Sdk\VMware.VimAutomation.Sdk.psd1')) {
#		Add-PSSnapin VMWare.VIMAutomation.core | Out-Null
		Import-Module "C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules\VMware.VimAutomation.Sdk\VMware.VimAutomation.Sdk.psd1"
		Import-Module "C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules\VMware.VimAutomation.Cis.Core\VMware.VimAutomation.Cis.Core.psd1"
		Import-Module "C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\Modules\VMware.VimAutomation.Core\VMware.VimAutomation.Core.psd1"
	} else {
#		Add-PSSnapin VMWare.VIMAutomation.core | Out-Null
<#
		#error
		write-host "<prtg>"
		write-host "<error>"
		write-host "1"
		write-host "</error>"
		write-host "<text>"
		write-host "Initialize-PowerCLIEnvironment.ps1 not available"
		write-host "</text>"
		write-host "</prtg>"
		break
#>
	}
}

Try {	
Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false > $null
} Catch {}

Write-Verbose "Currently connected to $global:defaultviserver"

Write-Verbose "Try to connect to $ComputerName ..."

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Connect-VIServer -Server $ComputerName -Protocol 'https' -UserName $UserName -Password $Password | Out-Null

Write-Verbose "Currently connected to $global:defaultviserver"

if (! $global:defaultviserver) {
	#error
	write-host "<prtg>"
	write-host "<error>"
	write-host "1"
	write-host "</error>"
	write-host "<text>"
	write-host "Unable to connect to $ComputerName"
	write-host "</text>"
	write-host "</prtg>"
	break
}
$global:textvar = ""

$powerstate = [string]$Args[5]
$old_backup = 0

$IgnoreListSplit = $IgnoreList -Split ","
$IgnoreFoldersSplit = $IgnoreFolders -Split ","
$OnlyFoldersSplit = $OnlyFolders -Split ","

$vmProcessed = 0
$erroneousVm = 0

Get-VM -Location $Args[4] | Select-Object -ExpandProperty CustomFields -Property BackupStatus,Name,PowerState,Notes,Folder | Where {$IgnoreListSplit -notcontains $_.Name -and $IgnoreFoldersSplit -notcontains $_.Folder} | ForEach-Object {
	$vmName = $_.Name
	$vmFolder = $_.Folder
	$vmProcessed = $vmProcessed + 1
	Write-Verbose "Working on $vmName @ $vmFolder"
	if ($OnlyFolders -ne "" -and $OnlyFoldersSplit -notcontains $vmFolder) {
		Write-Verbose "		...not in $OnlyFolders, ignored"
	} else {
		$b=($_.Value -split ",")[1]
		$jobNameString = ($_.Value -split ",")[0]
		if ($_.PowerState -eq "PoweredOff" -and $IgnorePoweredOff) {
			Write-Verbose "		...Powered Off, ignored"
		} else {
			if (($_.Notes -match "NO_BACKUP" -and $UseNoBackupInNotes) -or ($_.Folder -match "NO_BACKUP" -and $ignoreNoBackupFolder) -or ($_.Folder -match "NO-BACKUP" -and $ignoreNoBackupFolder) ) {
				Write-Verbose "		...Noted as NO_BACKUP or located in a NO-BACKUP Folder, ignored"
			} else {
				if ($b) {
					$jobName=$jobNameString.Substring($jobNameString.IndexOf("[")+1,$jobNameString.IndexOf("]")-$jobNameString.IndexOf("[")-1)
					$c=$b.Substring($b.IndexOf("[")+1,$b.IndexOf("]")-$b.IndexOf("[")-1)
					$d=[datetime]::ParseExact($c,"dd.MM.yyyy HH:mm:ss",$null)
					Write-Verbose "		...Job Name is $jobName, Backup Time is $d"
					$zz = Get-Date
					$yy = $zz - $d
					$xx = $yy.days * 24 + $yy.hours
					Write-Verbose "Day Diff between $d and $zz is $yy or $xx hours"
					$AgeHours = [int]$Age * 24
					if ( $xx -gt $AgeHours ) {
					Write-Verbose "				...Backup too old, $xx hours"
						$old_backup = $old_backup + 1
						$global:textvar += $_.Name
						$global:textvar += "("
						$global:textvar += $jobName
						$global:textvar += "," 
						$global:textvar += $d
						$global:textvar += "," 
						$global:textvar += $_.PowerState
						$global:textvar += ")"
						$global:textvar += " - "
						$erroneousVm = $erroneousVm + 1
					}
				} else {
					$old_backup = $old_backup + 1
					$global:textvar += $_.Name
					$global:textvar += "("
					$global:textvar += "-"
					$global:textvar += "," 
					$global:textvar += "-"
					$global:textvar += "," 
					$global:textvar += $_.PowerState
					$global:textvar += ")"
					$global:textvar += " - "
					$erroneousVm = $erroneousVm + 1
				}
			}
		}
	}
}

Disconnect-VIServer -Server $ComputerName -Force -Confirm:$False

$x=[string]$old_backup+":"+$global:textvar

#write-host "$old_backup"

if ( $vmProcessed -eq 0 ) {
	#error
	write-host "<prtg>"
	write-host "<error>"
	write-host "1"
	write-host "</error>"
	write-host "<text>"
	write-host "BackupStatus probably not configured"
	write-host "</text>"
	write-host "</prtg>"
	break
}

if($old_backup -ne 0){
	write-host "<prtg>"
	write-host "<error>"
	write-host "1"
	write-host "</error>"
	write-host "<text>"
	write-host $vmProcessed VMs processed and $erroneousVm errors found: $global:textvar
	write-host "</text>"
	write-host "</prtg>"
}

if($old_backup -eq 0){
	write-host "<prtg>"
	write-host "<result>"
	write-host "<channel>Backup Status</channel>"
	write-host "<value>"
	write-host $old_backup
	write-host "</value>"
	write-host "<LimitMaxError>0</LimitMaxError>"
	write-host "<LimitMode>1</LimitMode>"
	write-host "</result>"
	write-host "<text>"
	write-host $vmProcessed VMs processed and no Backup older than $Age days found
	write-host "</text>"
	write-host "</prtg>"
}