Get data of report via RestAPI or powershell

[sandsturm]

Hi all

Is there a way to gather VeeamONE data, which are used to create reports, via RestAPI or powershell? As an example, i want to get the data from the Change rate estimation report (https://helpcenter.veeam.com/docs/one/r ... ml?ver=120) and pass it to a script to be able to create alerts if a defined threshold is exceeded.

thx,
sandsturm

[RomanK]

Hello sandsturm,

Veeam ONE reports do some math to show the data and do not store this calculated data. So the answer is no.

The virtual disks GB data is in the RestAPI but there is no write rate estimation for the predictions. Probably, you may do some math on your own, collect and store the data over time and then estimate.

Thanks

[jorgedlcruz]

Hello,
The official answer is not yet, although it is in our plans to facilitate that data in the future so you can put it to work, it will surely be API, not PowerShell.

However, as a workaround and not supported. You can follow the next steps:

• https://jorgedelacruz.uk/2024/01/26/vee ... supported/

This works perfectly, and it contains all the required data if I am not mistaken, take a quick look with your report:


I might get some script for you tomorrow, if you are interested, but of course as said, this is not supported. It might help you anyways.

Thank you!

[jorgedlcruz]

Hello,
I put together a very simple, unsupported script, based on that blog post, and of course based on current stored procedure. This works for me, on VONE v12.1, but it might not work for you on other versions, or even in the future, but let's give it a try for the shake of the art of the possible (this is using native Microsoft auth to connect to VONE Database):

Code: Select all

# Specify the SQL Server and database
$SQLServer = "VEEAMONE\VEEAMSQL2017"
$SQLDBName = "VEEAMONE"

# Build the connection string
$ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"

# Instantiate a new SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString

# Instantiate a new SQL Command for the new stored procedure
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 86400
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandText = "reportpack.rsrp_Backup_VMChangeRateEstimation"

# Add the parameters
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ScopeVI", [Data.SqlDbType]::NVarChar, 4000))).Value = "<root><id>1000</id></root>"
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ScopeFL", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ScopeBV", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PeriodType", [Data.SqlDbType]::NVarChar, 6))).Value = "cweek"
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ScopeVCD", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DateFrom", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DateTo", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@SID", [Data.SqlDbType]::NVarChar, 4000))).Value = [DBNull]::Value
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ShowVMsWithNoChanges", [Data.SqlDbType]::Int))).Value = 0
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@TimeZone", [Data.SqlDbType]::Int))).Value = 60

# Open SQL Connection and execute the command
Write-Host "Opening SQL Connection and fetching data..."
try {
    $SqlConnection.Open()
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) > $null
    $SqlConnection.Close()
    Write-Host "SQL Data fetched successfully."

    # Debugging: Inspect the dataset schema
    Write-Host "Inspecting dataset schema..."
    $DataSet.Tables[0].Columns | ForEach-Object { Write-Host $_.ColumnName }

    # Filter rows where avg_gbyte_value is not NULL, dt_start and dt_end are not empty or null, and vm_name is not empty
    $filteredRows = $DataSet.Tables[0] | Where-Object { 
        $_.avg_gbyte_value -ne $null -and $_.avg_gbyte_value -ne "" -and
        [double]::TryParse($_.avg_gbyte_value, [ref]0) -and
        $_.dt_start -ne $null -and $_.dt_start -ne "" -and 
        $_.dt_end -ne $null -and $_.dt_end -ne "" -and 
        $_.vm_name -ne $null -and $_.vm_name -ne ""
    }

    # Debugging: Print out the filtered rows with dt_start and dt_end values
    Write-Host "Filtered Rows:"
    foreach ($row in $filteredRows) {
        Write-Host "dt_start: $($row.dt_start), dt_end: $($row.dt_end), vm_name: $($row.vm_name), avg_gbyte_value: $($row.avg_gbyte_value)"
    }

    # Extract unique date ranges for the headers
    $dateRanges = $filteredRows | Select-Object -Property dt_start, dt_end -Unique

    # Filter out date ranges with empty or null dt_start or dt_end
    $dateRanges = $dateRanges | Where-Object {
        $_.dt_start -ne $null -and $_.dt_start -ne "" -and
        $_.dt_end -ne $null -and $_.dt_end -ne ""
    }

    # Debugging: Print the date ranges
    Write-Host "Date Ranges:"
    foreach ($dateRange in $dateRanges) {
        Write-Host "Start Date: $($dateRange.dt_start), End Date: $($dateRange.dt_end)"
    }

    # Prepare the HTML headers dynamically
    $htmlHeaders = @"
<html>
<head>
    <title>Backup VM Change Rate Estimation Report</title>
    <style>
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid black; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h1>Backup VM Change Rate Estimation Report</h1>
    <table>
        <thead>
            <tr>
                <th>VM Name</th>
"@

    foreach ($dateRange in $dateRanges) {
        $startDate = $dateRange.dt_start
        $endDate = $dateRange.dt_end
        $htmlHeaders += "<th>$startDate - $endDate</th>"
    }

    $htmlHeaders += "<th>Total Size (GB)</th></tr></thead><tbody>"

    # Group data by VM and prepare table rows
    $groupedData = $filteredRows | Group-Object -Property vm_name

    foreach ($group in $groupedData) {
        $vmName = $group.Name

        # Initialize an array to hold the data for each period
        $periodData = @{}
        foreach ($dateRange in $dateRanges) {
            $periodData["$($dateRange.dt_start) - $($dateRange.dt_end)"] = "0.00"
        }
        $total = 0

        foreach ($row in $group.Group) {
            $periodKey = "$($row.dt_start) - $($row.dt_end)"
            if ($row.avg_gbyte_value -ne $null -and $row.avg_gbyte_value -ne [DBNull]::Value) {
                $periodData[$periodKey] = "{0:N2}" -f [double]$row.avg_gbyte_value
                $total += [double]$row.avg_gbyte_value
            }
        }

        # Add a row for each VM
        $htmlHeaders += "<tr><td>$vmName</td>"
        foreach ($period in $periodData.Keys) {
            $htmlHeaders += "<td>$($periodData[$period])</td>"
        }
        $htmlHeaders += "<td>{0:N2} GB</td></tr>" -f $total
    }

    $htmlHeaders += @"
        </tbody>
    </table>
</body>
</html>
"@

    # Save the HTML content to the file
    $htmlFilePath = "$PSScriptRoot\BackupVMChangeRateEstimation.html"
    $htmlHeaders | Out-File -FilePath $htmlFilePath

    Write-Host "HTML report saved to $htmlFilePath"
} catch {
    Write-Host "Error fetching data: $_"
    exit
}

End result:


The code has the output in console as well, so you do not need to build a HTML if you do not want, and you can send it somewhere else like to another database, etc. It is PowerShell right, so you can manipulate the data and take it somewhere else.

Let us know if this works for you. Repeating myself, not supported.

[sandsturm]

Hi Jorge

Absolutely brilliant, this is exactly what I searched for!

Many thanks to you!
sandsturm

[jorgedlcruz]

We are always happy to read positive feedback. We are hoping to introduce this functionality in API in future releases.

Best regards