如何使用PowerShell管理SQL Server Reporting Services（SSRS）订阅

In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.

在“ SQL Server Reporting Services 2016中的报告订阅更改”一文中 ，我介绍了在SQL Server 2016版本中引入的对标准订阅和数据驱动订阅的更改。但是，所有这些更改都与使用GUI管理报告订阅有关（即报表管理器门户，SSRS配置管理器）。 SQL Server Core，Linux上SQL Server以及Windows PowerShell的增强功能的发布，加强了Microsoft日益增长的转变，要求Microsoft SQL Server专业人士将脚本作为执行日常任务的方法之一。 在本文中，我们将通过讨论一些可用于管理报告订阅的Windows PowerShell命令，继续探索SQL Server Reporting Services（SSRS）订阅功能。

基本的PowerShell命令 (Basic PowerShell Commands)

Although latest releases of SQL Server usually introduce new T-SQL functions, they still support basic built-in functions that can be used across all versions of SQL Server i.e. SUBSTRING, REPLACE. Not surprisingly, there are PowerShell commands that can be executed against most versions of Reporting Services. Examples of such basic reporting services functionalities that can be administered using PowerShell includes the following:

尽管最新版本SQL Server通常会引入新的T-SQL函数，但它们仍支持可在所有版本SQL Server中使用的基本内置函数，例如SUBSTRING，REPLACE。 毫不奇怪，可以对大多数版本的Reporting Services执行PowerShell命令。 可以使用PowerShell进行管理的此类基本报表服务功能示例包括：

• Providing a list of subscriptions
  提供订阅列表
• Change report subscription ownership
  更改报告订阅的所有权
• Delete a report subscription
  删除报告订阅

The ability to administer report subscriptions using PowerShell is made possible by the Report Server Web service. The Report Server Web service has several endpoints for administering a report server instance such as ReportService2005, ReportService2006 and ReportService2010. For the purposes of this discussion, we will focus on the ReportService2010. Figure 1 shows a preview of the XML file returned when you access the endpoint from a browser.

报表服务器Web服务使使用PowerShell来管理报表订阅的功能成为可能。 报表服务器Web服务具有多个用于管理报表服务器实例的终结点，例如ReportService2005 ， ReportService2006和ReportService2010 。 为了便于讨论，我们将重点介绍ReportService2010 。 图1显示了从浏览器访问端点时返回的XML文件的预览。

The ReportService2010 endpoint contains multiple members such as, properties, events and methods that can be queried using PowerShell. Script 1 shows how we can get a list of member details using the ReportService2010. The script is formatted and executed using Windows PowerShell Integrated Scripting Environment (ISE).

ReportService2010终结点包含可以使用PowerShell查询的多个成员，例如属性，事件和方法。 脚本1显示了如何使用ReportService2010获得成员详细信息列表。 脚本使用Windows PowerShell集成脚本环境（ISE）进行格式化和执行。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$webSRV | get-member;
 

1.在Windows PowerShell中列出我的订阅 (1. List My Subscriptions in Windows PowerShell)

One of the PowerShell commands that you can run against almost any version of SSRS is to list subscriptions that your account owns. This is similar to clicking My Subscriptions link from the Report Manager page. The PowerShell script used to return such a list is shown in Script 2 and its output (shown in Figure 2) is very similar to My Subscriptions list returned using the GUI – shown in Figure 3.

几乎可以对任何版本的SSRS都可以运行的PowerShell命令之一就是列出您的帐户拥有的订阅。 这类似于单击“报告管理器”页面中的“ 我的订阅”链接。 脚本2中显示了用于返回此类列表的PowerShell脚本，其输出（如图2所示）与使用GUI返回的“ 我的订阅”列表非常相似-如图3所示。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$mySubsList = $webSRV.ListMySubscriptions("/");  
$mySubsList | select report, description, owner, status, lastexecuted, subscriptionid | format-table -auto  
 

2.列出Windows PowerShell中的所有订阅 (2. List All Subscriptions in Windows PowerShell)

One of the security flows in reporting services is that subscriptions can continue to be owned by a domain account that has been disabled. Therefore as a Report Server administrator, you don’t want to be limited to just a list of subscriptions that your account own instead you may want to retrieve a subscription list for all accounts. Although, the GUI is unable to readily retrieve such a complete list, ReportService2010 endpoint has a method (ListSubscriptions) which can be used in PowerShell to retrieve all subscriptions as shown in Script 3.

报告服务中的安全流程之一是，订阅可以继续由已禁用的域帐户拥有。 因此，作为Report Server管理员，您不希望只限于帐户拥有的订阅列表，而是可以检索所有帐户的订阅列表。 尽管GUI无法轻松检索到这样的完整列表，但ReportService2010端点具有方法（ ListSubscriptions ），可在PowerShell中使用该方法来检索所有订阅，如脚本3所示。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$mySubsList = $webSRV.ListSubscriptions("/");  
$mySubsList | select report, description, owner, status, lastexecuted, subscriptionid | format-table -auto  
 

Whilst Figure 2 showed an output of subscriptions owned by the Administrator account, the output of Script 3 (shown in Figure 4), further retrieved subscriptions owned by account name WIN-9P7O5S87UBG\Sifiso.

尽管图2显示了Administrator帐户所拥有的订阅的输出，而脚本3的输出（如图4所示）显示了帐户名WIN-9P7O5S87UBG \ Sifiso所拥有的订阅的进一步检索。

Depending on the number of subscriptions within a given report server instance, the output of Script 3 can potentially be a long list. You can reduce the returned output by retrieving subscriptions of a given path. Up to this point, the script examples demonstrated have been looking at the entire root folder (denoted as “/”), Script 4 shows the changes that you have to make in order to retrieve subscriptions for reports located in Folder1.

根据给定报表服务器实例内的预订数量， 脚本3的输出可能很长。 您可以通过检索给定路径的订阅来减少返回的输出。 到目前为止，演示的脚本示例一直在查看整个根文件夹（表示为“ /”）， 脚本4显示了为检索Folder1中的报表的订阅而必须进行的更改。

 
  $mySubsList = $webSRV.ListSubscriptions("/Folder1");  
 

Figure 5 shows a reduced list of subscriptions belonging to reports stored on within Folder1.

图5显示了属于Folder1内存储的报告的订阅的简化列表。

3.在Windows PowerShell中更改订阅所有者 (3. Change Subscription Owner in Windows PowerShell)

One of the things we may want to do as a result of going through an output of an All Subscriptions list would be to change ownership for subscriptions that belong to disabled domain accounts. This can be done using the method ChangeSubscriptionOwner which accepts two parameters, namely, the report subscription ID as well as the name of the account the subscription ownership is being changed to.

经过“ 所有订阅”列表输出后，我们可能要做的一件事就是更改属于禁用域帐户的订阅的所有权。 这可以使用ChangeSubscriptionOwner方法来完成，该方法接受两个参数，即报表订阅ID以及将订阅所有权更改为的帐户名称。

Figure 6 identifies a report subscription whose ownership we would like to change from WIN-9P7O5S87UBG\Administrator to WIN-9P7O5S87UBG\Sifiso

图6标识了一个报告订阅，我们希望将其所有权从WIN-9P7O5S87UBG \ Administrator更改为WIN-9P7O5S87UBG \ Sifiso

The PowerShell script for changing report subscription ownership is shown in Script 5 and its output (shown in Figure 7) indicates that the subscription ownership was successfully changed.

脚本5中显示了用于更改报告订阅所有权的PowerShell脚本，其输出（如图7所示）表明订阅所有权已成功更改。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$webSRV.ChangeSubscriptionOwner("46db12cb-bbe1-45cc-8d5f-30fbdc306dbO","WIN-9P7O5S87UBG\Sifiso");  
 

4.在Windows PowerShell中删除订阅 (4. Delete Subscriptions in Windows PowerShell)

In addition to switching account ownership, you also have an option to delete a report subscription without having to launch and navigate through Report Manager links. This can be done in PowerShell using the DeleteSubscription method. The method simply requires a report subscription ID as shown in Script 6.

除了切换帐户所有权外，您还可以选择删除报表订阅，而不必启动和浏览报表管理器链接。 这可以在PowerShell中使用DeleteSubscription方法完成。 该方法仅需要报告订阅ID，如脚本6所示。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$webSRV.DeleteSubscription("46db12cb-bbe1-45cc-8d5f-30fbdc306dbO");  
 

SSRS 2016中的新PowerShell命令 (New PowerShell Commands in SSRS 2016)

The release of SSRS 2016 introduced new methods into the ReportService2010 endpoint. One of the checks you may want to conduct as a report server administrator could be to use PowerShell to identify report subscriptions that are using the new File Share Account feature in your environment. Another feature introduced in SSRS 2016 – disabling and enabling of report subscriptions – could also be administered in PowerShell.

SSRS 2016的发行版在ReportService2010端点中引入了新方法。 您可能希望以报表服务器管理员身份进行的检查之一是使用PowerShell来识别在您的环境中使用新文件共享帐户功能的报表订阅。 SSRS 2016中引入的另一个功能（禁用和启用报表订阅）也可以在PowerShell中进行管理。

1.文件共享帐户 (1. File Share Account)

Unlike previous sections, the ReportService2010 endpoint doesn’t contain a dedicated method for identifying subscriptions configured to use a File Share Account instead we make use of the ListSubscriptions method. File Share Account can only be configured against Windows File Share delivery method, thus, the first part of writing a PowerShell script that will list subscriptions using File Share Account is to firstly identify those subscriptions that are using Windows File Share delivery method. Script 7 identifies such subscriptions by filtering DeveliverySettings.Extension on “Report Server FileShare”.

与前面的部分不同， ReportService2010终结点不包含用于标识配置为使用文件共享帐户的订阅的专用方法，而是使用ListSubscriptions方法。 只能使用Windows File Share交付方法配置File Share帐户，因此，编写将使用File Share Account列出订阅的PowerShell脚本的第一部分是首先识别使用Windows File Share交付方法的那些订阅。 脚本7通过过滤“ Report Server FileShare”上的DeveliverySettings.Extension来识别此类订阅。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$mySubsList = $webSRV.ListSubscriptions("/") | where {$_.DeliverySettings.Extension -eq "Report Server 
FileShare"} ;  
$mySubsList | select report, description, status, lastexecuted, subscriptionid | format-table –auto
 

For the purposes of this discussion, I setup sample subscriptions with keyword FS to indicate that they are using Windows File Share delivery method. Subsequently, the output of Script 7 (shown in Figure 8) shows such list of report subscriptions.

为了便于讨论，我使用关键字FS设置了示例订阅，以指示它们正在使用Windows File Share传递方法。 随后， 脚本7的输出（如图8所示）显示了此类报告订阅列表。

Now that we have identified all subscriptions configured to use Windows File Share delivery method, we can apply the second part of the script which uses the filter method to iterate through subscriptions properties to identify those subscriptions configured to use File Share Account. The complete script is shown in Script 8.

现在，我们已经确定了所有配置为使用Windows File Share传递方法的订阅，我们可以应用脚本的第二部分，该脚本的第二部分使用filter方法来遍历订阅属性，以标识那些配置为使用文件共享帐户的订阅。 完整的脚本显示在脚本8中 。

 
filter FileShareAcountFilter {
    ForEach($Param in $_.DeliverySettings.ParameterValues)  
    {  
        if($Param.Name -eq "DEFAULTCREDENTIALS" -and $Param.Value -eq "True")  
        {  
            $_  
            break  
        }  
    } 
}
 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$mySubsList = $webSRV.ListSubscriptions("/") | where {$_.DeliverySettings.Extension -eq "Report Server 
FileShare"} ;  
$mySubsList | select report, description, status, lastexecuted, subscriptionid | format-table –auto
 

The output of Script 8 is shown in Figure 9 which contains a further reduced list from the one displayed in Figure 8.

脚本8的输出在图9中显示，其中包含从图8中显示的列表中进一步缩小的列表。

2.使用Windows PowerShell启用和禁用订阅 (2. Enable and disable subscriptions using Windows PowerShell)

The enabling and disabling of subscriptions in Windows PowerShell is similar to doing a delete in that a single report subscription ID parameter is passed into a method. The scripts for enabling and disabling subscriptions is shown in Script 9 and 10, respectively.

在Windows PowerShell中启用和禁用订阅类似于执行删除操作，因为将单个报表订阅ID参数传递到方法中。 脚本9和10分别显示了用于启用和禁用订阅的脚本 。

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$webSRV.EnableSubscription("46db12cb-bbe1-45cc-8d5f-30fbdc306dbO");  
 

 
$webSRV = New-WebServiceProxy -Uri "http://win-9p7o5s87ubg/ReportServer/ReportService2010.asmx" -Namespace 
SSRS.ReportingService2010 -UseDefaultCredential;  
$webSRV.DisableSubscription("46db12cb-bbe1-45cc-8d5f-30fbdc306dbO");  
 

参考资料 (References)

• Windows PowerShell Integrated Scripting Environment (ISE)
  Windows PowerShell集成脚本环境（ISE）
• Get-Member (Windows PowerShell)
  获取成员（Windows PowerShell）
• Report Server Web Service Endpoints
  报表服务器Web服务端点

翻译自: https://www.sqlshack.com/how-to-administer-sql-server-reporting-services-ssrs-subscriptions-using-powershell/