Need to fetch sites reports that uses SSRS reports using PowerShell in SharePoint 2013

Question

Hello Team,

I need to fetch all sites in web application that uses SSRS report using PowerShell. These data I needed for content migration in SharePoint 2019.

Please response asap

Answer 1

Hi @sushma Kumari ,

According to your description, this problem has more to do with SQL. I added the relevant tag, hoping that relevant engineers can see it and give a more professional solution to this issue.

Based on my research, I found a script for your reference:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$webapp = Get-SPWebApplication "http://yourwebapplicationurl"
foreach ($site in $webapp.Sites)
{
    foreach ($web in $site.AllWebs)
    {
        $reportServerUrl = $web.Site.WebApplication.GetResponseUri([Microsoft.SharePoint.Administration.SPUrlZone]::Default).AbsoluteUri + "/_vti_bin/ReportServer/ReportService2010.asmx"
        $rs = New-WebServiceProxy -Uri $reportServerUrl -UseDefaultCredential
        $reports = $rs.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "Report" }
        if ($reports.Count -gt 0)
        {
            Write-Host "Site URL: $($web.Url)"
            Write-Host "Reports:"
            foreach ($report in $reports)
            {
                Write-Host " - $($report.Path)"
            }
        }
        $web.Dispose()
    }
    $site.Dispose()
}

This script will iterate through all sites and webs in the web application, and for each web, it will retrieve the Report Server URL and use it to get a list of all reports. If there are any reports, it will output the site URL and a list of the reports.

Note that this script assumes that the SSRS reports are deployed to the default Report Server URL (/_vti_bin/ReportServer/ReportService2010.asmx). If your SSRS reports are deployed to a different URL, you will need to modify the script accordingly.

And there is a article, hope it can give you some inspiration:

https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/manage-subscription-owners-and-run-subscription-powershell?view=sql-server-ver16

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @sushma Kumari ,

Sorry to hear that, please try this:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$webApp = Get-SPWebApplication "http://sp19:90"
foreach ($site in $webApp.Sites)
{
    foreach ($web in $site.AllWebs)
    {
        $ssrsReports = $web.Lists | Where-Object {$_.BaseTemplate -eq "ReportLibrary" -and $_.RootFolder.Name -eq "Forms"} | Select-Object -ExpandProperty ParentWebUrl
        Write-Host "Site: $($web.Url) - SSRS Reports: $($ssrsReports)"
        $web.Dispose()
    }
    $site.Dispose()
}

The result:

Please make sure that the account has permission to the site.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.