Ability to Schedule Download of Readiness Reports to Local Folder

[WestieDave]

Our security policies mandate that we will not enable the SMTP server on VRO to email reports. Instead we wish to output the reports to a local folder on the VRO server and this will then be collected internally to a central repository via a secure file transfer method.

Given these reports are not available Veeam One currently is it possible to have VRO save these reports to a local folder via an option in the VRO console or is this functionality coming in a future release of Veeam One or VRO?

Alternatively would be it possible to have this functionality triggered from a script on the VRO server that will run from Scheduled Task?

[Alec King]

Hello Dave,

Regarding SMTP server, we will be updating the email integration in VRO to use modern authentication instead of SMTP; the work is planned but not yet scheduled for a specific release.

Meantime I've asked our senior developer to work on a script that can extract the daily readiness checks from the database and dump to a local folder.
The script should be run daily and will extract all readiness check reports for that day and save them in your chosen folder on the VRO server.

Just to confirm, do you want to extract Summary reports, Full reports, or both?
Also, do you use PDF or DOCX as the report format?

Thanks!

[WestieDave]

Hi Alec,

Thanks for the reply on this.

Ideally we'd like the option to extract both report options in PDF format so that they can be stored in an accessible area to show our DR readiness.

[Alec King]

Hi Dave,
Here is a PowerShell script that should work for you. Hopefully all the required parameters are clear, if you need any modifications let us know!

Code: Select all

# Connection parameters
$serverName = "your_server_name"  # SQL server name
$databaseName = "your_database_name"  # Database name

# Folder for saving files
$outputFolder = "D:\NewFolder"

# Ensure the output folder exists
If (!(Test-Path -Path $outputFolder)) {
    New-Item -ItemType Directory -Path $outputFolder
}

# Connection string
$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"

# SQL query. DetailLevel (1 - summary, 2 - full)
$query = "select Name, ReportDocumentFormat, ReportData
from VeeamAA.Reports r
join VeeamAA.ReportsData rd on r.Uid = rd.ReportsUid
where TemplateType = 3 and (DetailLevel = 2 or DetailLevel = 1)"

# Connect to the database
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Execute the SQL query
$command = $connection.CreateCommand()
$command.CommandText = $query

# Read data
$reader = $command.ExecuteReader()

# Process data
while ($reader.Read()) {
    $name = $reader["Name"]
    $reportData = $reader["ReportData"]
    $documentFormat = $reader["ReportDocumentFormat"]
    
    # Replace colon with underscore in the name
    $safeName = $name -replace ":", "_"

# Determine the file extension
    $fileExtension = if ($documentFormat -eq 1) { ".pdf" } else { ".docx" }
    $filePath = Join-Path -Path $outputFolder -ChildPath ("$safeName$fileExtension")
    
    # Save the file
    [System.IO.File]::WriteAllBytes("$filePath", $reportData)
}

# Close the connection
$reader.Close()
$connection.Close()

[WestieDave]

Awesome!

Thank you Alec for the swift response in providing this. I have just 1 minor question. Is it possible to add to the query string so that only the most recent reports are extracted?

[Alec King]

Sure Dave, here's the updated script. It will save only the most recent reports when you run it. Let us know how it goes!

Code: Select all

# Connection parameters
$serverName = "your_server_name"  # SQL server name
$databaseName = "your_database_name"  # Database name

# Folder for saving files
$outputFolder = "D:\NewFolder"

# Ensure the output folder exists
If (!(Test-Path -Path $outputFolder)) {
    New-Item -ItemType Directory -Path $outputFolder
}

# Connection string
$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"

# SQL query. DetailLevel (1 - summary, 2 - full)
$query = "select Name, ReportDocumentFormat, ReportData
from (
select Uid, ReportDocumentFormat,
row_number() over(partition BY PlanId, TemplateType ORDER BY CreateDate desc) num
from VeeamAA.Reports r
where TemplateType not in(-1, 2)
) rep
join VeeamAA.ReportsData rd on rep.Uid = rd.ReportsUid
where (DetailLevel = 2 or DetailLevel = 1) and num = 1"

# Connect to the database
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Execute the SQL query
$command = $connection.CreateCommand()
$command.CommandText = $query

# Read data
$reader = $command.ExecuteReader()

# Process data
while ($reader.Read()) {
    $name = $reader["Name"]
    $reportData = $reader["ReportData"]
    $documentFormat = $reader["ReportDocumentFormat"]
    
    # Replace colon with underscore in the name
    $safeName = $name -replace ":", "_"

# Determine the file extension
    $fileExtension = if ($documentFormat -eq 1) { ".pdf" } else { ".docx" }
    $filePath = Join-Path -Path $outputFolder -ChildPath ("$safeName$fileExtension")
    
    # Save the file
    [System.IO.File]::WriteAllBytes("$filePath", $reportData)
}

# Close the connection
$reader.Close()
$connection.Close()

[WestieDave]

Hi Alec,

Once again thanks for your swift response and the work of your senior developer to produce this.

This is precisely what we're looking for to have these reports exported internally to meet our security and DR policies.