SSRS Subscription Administration – Part 1

This is part one of a two part series of SQL Server Reporting Services Subscription Administration. In part one, we’ll review some “care and feeding” basics and review a handy query that allows you to get a better view of how your SSRS subscriptions are behaving. In part two, we’ll look at a report that takes the subscription results query and puts it in a format that allows you to quickly review status, recipients, and parameters.

Part of my new job at Trek involves SSRS administration. I really enjoyed managing Reporting Services instances at my previous job, so it felt good to have another set of instances to get to know. Trek’s BI team is SQL 2012 all the way, but I can reassure other SSRS admins out there that haven’t gotten to play with the newest toys: if you can manage a 2008 or even 2005 instance, you’ll quickly adapt to 2012.

Administrators of any version of Reporting Services will – at some point – have to create, update, and manage subscriptions. While subscriptions are a great way to deliver reports on a schedule or based on changes in the report’s data, managing them can still feel primitive.

Subscription Gotcha’s

• By default, a new email subscription’s “To” address will pick up the user account of whoever is creating it. End users can easily get tricked into thinking “Oh good, it knows who I am and will translate that into my email address!” Nope. That subscription can’t send to jsmith123 any more than Gmail can.
• Email address entry doesn’t integrate with any outside address book. Beware the copy/paste fail or improper separator between addresses. Check your work and remember to use a semi-colon to separate them. Remember: one bad email address in that subscription will fail the whole subscription.
• Whenever possible, use distribution lists rather than individual user email addresses in subscriptions. Email groups are usually integrated with Active Directory, so when users come and go, they’ll get added or deleted outside your purview and SSRS subscriptions won’t choke on trying to send to a dead email address.
• The owner of the subscription (and the SQL agent job that runs it) is the person who creates it. This means when SSRS admin Jane leaves the company and her Active Directory account gets deleted – surprise! – all those subscriptions break. The only way to change the owner of that subscription is via manual update query.

Updating an SSRS  Subscription’s Owner to a Valid User Account
Note: you may choose to put this together however you like. For example, some may want to use variables for your old and new userID uniqueidentifiers or even create a stored procedure if you frequently need to make these updates. I am using nested queries here to show how the relationships connect, and assuming that you’ll only be updating a small number of records (less than 100) at a time

Step 1: Get the User ID uniqueidentifier for the inactive user.
Only 1 result should be returned!
SSRS keeps its own table of users and groups associated with its security, so even  if the user account has been deleted from your Active Directory system, it will be available for you to reference inside the ReportServer database
Select UserID, UserName
from ReportServer.dbo.Users
where UserName = 'Domain\DeletedUserID'

Step 2: Get the User ID uniqueidentifier for a service account used with your SSRS instance
Only 1 result should be returned!
This may the account that is used to run the SSRS service on your application server. A service account isn’t linked to a human being, and therefore won’t be deleted from Active Directory. Alternately, you can use the User ID key for a group used to grant permissions in your SSRS instance since groups and users are treated the same for this purpose
Select UserID, UserName
from ReportServer.dbo.Users
where UserName = 'Domain\SA-SsrsInstanceAct'

Step 3: Get the count of subscriptions owned by the inactive user ID.
It’s good to know ahead of time how many records you’ll be updating so you’ll know whether “30 row(s) affected” is a valid response or a reason to call your DBA for a database restore
Select Count(*)
from ReportServer.dbo.Subscriptions
where OwnerID = (Select UserID from Reportserver.dbo.Users where UserName = 'Domain\DeletedUserID')

Step 4: Update the subscription owner for all affected subscriptions
UPDATE ReportServer.dbo.Subscriptions
SET OwnerID = (Select UserID from Reportserver.dbo.Users where UserName = 'Domain\SA-SsrsInstanceAct')
WHERE OwnerID = (Select UserID from Reportserver.dbo.Users where UserName = 'Domain\DeletedUserID')

Subscriptions & Data Sources

I did discover one nice feature of Report Manager recently that is either new in 2012 or just never jumped out at me before. If you look at the properties of a data source in Report Manager, one of the available sections is “Subscriptions”. Why is this useful? Let’s say a whole database used by oodles of reports is going to be down for a multi-hour software upgrade. If you look at this data source’s Subscriptions section ahead of time, you’ll know what subscriptions are potentially going to fail. You could choose to reschedule them around the outage, or just remember to add “Reports automatically emailed to you” to the list of “Stuff That You’ll Miss During the Big Software Upgrade” sent to that application’s users.

Querying the Instance Database for Subscription Details

Savvy SSRS admins can overcome the rather primitive OOTB management of subscriptions using the power of SSRS queries and reports run against the ReportServer database. Let’s take a look at a query that can give better visibility into what’s going on with our subscriptions.

As many of you no doubt know, an even passing familiarity with the ReportServer database (often called the Catalog database) for your SSRS instance will yield serious rewards. You will get great visibility into what’s really going on, often in a format that allows easier comparison than the limited info available in Report Manager. Let’s take a look at a query that allows you to get a good chunk of metadata about all the subscriptions for reports in a given folder. You can use this query on its own or use it as the dataset for the report we’ll discuss in part two of this series.

SELECT c.path as ReportPath
--Need to convert Subscription ID to a varchar to allow it to be used in our Part 2 report
,CONVERT(varchar(36),s.subscriptionID) as SubscriptionID
--Differentiate between email subscriptions and subscriptions that drop reports out onto a file share
,case s.DeliveryExtension
when 'Report Server Email' then 'Email'
when 'Report Server FileShare' then 'File Drop'
end as SubscriptionType
--Keep track of which subscriptions are using shared schedules and which are using their own custom schedule
,case sch.Type
when 0 then sch.Name
else '(Custom)' end as SubscriptionScheduleName
--IsDataDriven is another part of the querystring for the Report Manager URL.
--If the subscription has any data settings, it's a data-driven subscription
,case
when DATALENGTH(s.DataSettings) IS NULL then 'False'
else 'True' End As IsDataDriven
--While these fields are NTEXT instead of XML, they contain XML and can be queried via the .value function
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="TO"]/Value)[1]','VARCHAR(1024)') AS [SendTo]
, CAST(ExtensionSettings AS XML).value('(//ParameterValue[Name="CC"]/Value)[1]','VARCHAR(1024)') AS [SendCC]
--Depending on the number of parameters for the report, you may wish to add or remove these
--If some reports have no parameters, columns will be
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[1]','varchar(max)') as Parameter1
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[2]','varchar(max)') as Parameter2
, CAST(s.Parameters AS XML).value('(//ParameterValue/Value)[3]','varchar(max)') as Parameter3
, s.laststatus as SubscriptionStatus
, s.LastRunTime
--Determine what subscriptions haven't been run in the last month. Perhaps they can be deleted?
, case DateDiff(month,GetDate(), s.LastRunTime)
when 0 then 'Y'
when -1 then 'Y'
else 'N' end as RunInLast30Days
, s.ModifiedDate
FROM ReportServer.dbo.Subscriptions s
INNER JOIN ReportServer.dbo.Catalog c
ON s.report_oid = c.itemid
INNER JOIN ReportServer.dbo.ReportSchedule rsc
on rsc.subscriptionID = s.SubscriptionID
and rsc.ReportID = c.itemID
INNER JOIN ReportServer.dbo.Schedule sch
on sch.scheduleID = rsc.scheduleID
WHERE LOWER(c.path) like '/Manufacturing%'

I hope these tips have been useful to you and make your job administering your SSRS subscriptions a little easier! Thanks for reading!