85 lines
2.7 KiB
PowerShell
85 lines
2.7 KiB
PowerShell
function Get-SSRSReportHashes {
|
|
<#
|
|
.SYNOPSIS
|
|
Retrieves a hashtable containing the Name of a report and a hashed value genreated from the Report's content.
|
|
|
|
.DESCRIPTION
|
|
Use this command to generate a hashtable of report hashes for use in determining if a report has changed.
|
|
|
|
.PARAMETER SqlReportServer
|
|
[string] The machine name of the Reports SQL Server. Required.
|
|
|
|
.PARAMETER ReportPath
|
|
[string] The case-sensitive ReportPath used in SQL Server (i.e. /AWS Staging QAstg). Required.
|
|
|
|
.EXAMPLE
|
|
Get-SSRSReportHashes "sc00rs01.fh.local" "/AWS Staging QAstg"
|
|
|
|
.EXAMPLE
|
|
Get-SSRSReportHashes -SqlReportServer "sc00rs01.fh.local" -ReportPath "/AWS Staging QAstg"
|
|
#>
|
|
[CmdletBinding()]
|
|
[OutputType([System.Collections.Hashtable])]
|
|
param (
|
|
[Parameter(Mandatory = $true)]
|
|
[Alias("SqlReportServer")]
|
|
[string]$reportServer,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[Alias("ReportPath")]
|
|
[string]$path
|
|
)
|
|
|
|
Write-Verbose "$logLead : Filtering data using report path $path"
|
|
$connectionString = "data source=$($reportServer);Integrated Security=SSPI;Database=ReportServer;MultiSubnetFailover=true"
|
|
Write-Host "$logLead : Connecting to ReportServer Database with connection string: $connectionString"
|
|
|
|
$query = @"
|
|
SELECT Name, CONVERT(varchar(max), CONVERT(varbinary(max), Content)) as TextContent
|
|
FROM dbo.Catalog c
|
|
WHERE Content IS NOT NULL
|
|
AND PATH like '%$path/%'
|
|
ORDER BY Name
|
|
"@
|
|
|
|
try {
|
|
$conStrBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($connectionString)
|
|
} catch {
|
|
Write-Error "$logLead : Exception occurred building the ConnectionString: $($_.Exception.Message.ToString())"
|
|
throw $_
|
|
}
|
|
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection $conStrBuilder.ToString()
|
|
$command = New-Object System.Data.SqlClient.SqlCommand($query, $conn)
|
|
$dbReportHashes = @{ }
|
|
|
|
try {
|
|
if ($conn.State -ne [System.Data.ConnectionState]::Open) {
|
|
$conn.Open()
|
|
}
|
|
$results = $command.ExecuteReader()
|
|
|
|
while ($results.Read()) {
|
|
$name = $results.Item(0)
|
|
$content = $results.Item(1)
|
|
|
|
$hashedContent = Get-UTF8ContentHash $content
|
|
Write-Verbose "$logLead : Name: $($name) Hash: $($hashedContent)"
|
|
|
|
$dbReportHashes.Add($name, $hashedContent)
|
|
}
|
|
} catch {
|
|
Write-Error "$logLead : Exception occurred reading database results: $($_.Exception.Message.ToString())"
|
|
throw $_
|
|
} finally {
|
|
if ($null -ne $results) {
|
|
$results.Dispose()
|
|
}
|
|
|
|
if ($null -ne $results) {
|
|
$conn.Dispose()
|
|
}
|
|
}
|
|
|
|
return $dbReportHashes
|
|
} |