Polling for Backups utilization

[loganbest]

I'm wanting to poll the MSSQL database to get the backups utilization for each VM being backed up. Is there a good query for that information? We bill our clients based on utilization so having that information without the Veeam API is highly beneficial.

NOTE: We do not have the API and cannot get it.

[Vitaliy S.]

Hello,

Have you considered using Veeam ONE that has a predefined report called Backup billing that does expose information about the size of the backup files?

Thank you!

[loganbest]

We're on a very tight budget and can't currently afford anymore products, so no Veeam ONE has not been considered.

[tsightler]

It sounds like you're wanting to know the amount of space used by each specific VM in a backup job? Veeam doesn't really track this information as it's somewhat abstract when you have to account for deduplication of blocks across VM. It's pretty easy to get information about space for the backup job as a whole via Powershell, but per-VM data isn't readily available.

[loganbest]

Post dedupe values are fine since that's what we're actually using on the storage arrays. I found that I can poll for the "stored_size" column per "object_name" in Backup.Model.BackupTaskSessions table. Then I can loop through that to add up the values for the multiple job runs to get the final storage count.

Is that an accurate representation? Is there a better way of doing it without ONE or API?

[Vitaliy S.]

We generally do not support direct interaction with the configuration database, so we do not have the database structure documented in any public-facing documentation.

Also please be aware that relying on direct database queries for your billing solution might be not very reliable in the long term future. Every new version or a patch might change some data tables or even replace them with a new structure, so you will need to verify your solution every release/update cycle.

[loganbest]

Understood. However can you please verify my finding to confirm that as of this moment that is a correct assessment?

[foggy]

Support guys can verify this for you.

[loganbest]

And the community can't?

[Vitaliy S.]

Well... if community members know the database structure they could help, but, unfortunately, I'm not aware about tables to use to query this data (our support team is more experienced with this). Out of curiosity, why can't you go with a PowerShell route to get this info?

[loganbest]

Because we can't afford Enterprise Plus for the API.

[Vitaliy S.]

...but PowerShell command-lets are available in standard edition which should help you in this case.

[loganbest]

They don't provide per VM storage usage though.

[Vitaliy S.]

Yes, that's correct. Please be aware that neither API, nor PowerShell, nor direct SQL queries can provide you with per-VM storage usage in the backup file because of the reasons Tom has described above.

[loganbest]

According to my post yesterday about specific columns it looks like your statement is false but I need confirmation of that. Veeam is logging exactly how much data each VM is storing per job run. It's in the reports, it's in SQL. So combining that knowledge must mean that I can use it direct from SQL. If this is available in the API then it's going to be getting it from SQL most likely to keep calls fast instead of querying the filesystem everytime.

[Vitaliy S.]

This info is not available via RESTful API. I'm not sure what this means ("stored_size") in the database table, but you can do a quick test to verify that - just sum up all the values in the column and compare them with the total backup file size.