Housekeeping on an old SSRS, this particular server is 2008R2.
I have the query below which gets me the name, location, creation and modification overview for all the reports. I would also like to include the 'Connection String' at least, and some other details if possible from the data sources properties. But I can't find it in ReportServer. I have seen people use PowerShell to get it out of SSRS web service, I was hoping to find a T-SQL solution.
How can I display Data Source details with a SQL query?
--List all the reports on SSRS via "ReportServer" database USE [ReportServer] GO SELECT Name --, [ItemID] --Primary key , [Path] , [Description] --, [CreatedByID] --need link to get anything usable from here , Created.UserName as CreatedByUser , [CreationDate] --, [ModifiedByID] --need link to get anything usable from here , Modified.UserName as ModifiedByUser , [ModifiedDate] FROM [dbo].[Catalog] left join (select [UserID] , [UserName] from [dbo].[Users]) as Created on Catalog.CreatedByID = Created.UserID left join (select [UserID] , [UserName] from [dbo].[Users]) as Modified on Catalog.ModifiedByID = Modified.UserID WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html ORDER BY [Path], Name
Get SSRS Datasources from ReportServer
I would also like to include the 'Connection String' at least
How can I display Data Source details with a SQL query?
-- Transact-SQL script to get connection string of all SSRS Shared Datasources. /* Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards? With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources, to document the usage or to search for a specific server/database. Please remark: Querying the ReportServer database directly is not a supported way. Works with SSRS 2005 and higher version ReportServer databases. Requieres select rights on the "Catalog" table in ReportServer database. */ -- Connection strings of all SSRS Shared Datasources ;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause. (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource' ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd) ,SDS AS (SELECT SDS.name AS SharedDsName ,SDS.[Path] ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF FROM dbo.[Catalog] AS SDS WHERE SDS.Type = 5) -- 5 = Shared Datasource SELECT CON.[Path] ,CON.SharedDsName ,CON.ConnString FROM (SELECT SDS.[Path] ,SDS.SharedDsName ,DSN.value('ConnectString', 'varchar(150)') AS ConnString FROM SDS CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN) ) AS CON -- Optional filter: -- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%' ORDER BY CON.[Path] ,CON.SharedDsName;
Here's a query to add the data source name as well using your TSQL:
USE [ReportServer] GO SELECT CATALOG.NAME ,CATALOG.[Path] ,DataSource.NAME datasource ,CATALOG.[Description] ,Created.UserName AS CreatedByUser ,CATALOG.[CreationDate] ,Modified.UserName AS ModifiedByUser ,CATALOG.[ModifiedDate] FROM [dbo].[Catalog] LEFT JOIN ( SELECT [UserID] ,[UserName] FROM [dbo].[Users] ) AS Created ON CATALOG.CreatedByID = Created.UserID LEFT JOIN ( SELECT [UserID] ,[UserName] FROM [dbo].[Users] ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID WHERE CATALOG.[Type] = 2 ORDER BY [Path] ,NAME
When you upload data source and report definitions to the report server it does store their information in the dbo.Catalog table which you can see using Convert(Xml, Convert(Varbinary(Max), Content)) and as it's XML you can use XPath to pull out the fields you're interested in.
The problems really begin once you modify anything on the SSRS web GUI.
If you change the connection string within a shared data source, or modify a report's data source, then SSRS will leave the catalog entry as-is and instead add an entry to the dbo.DataSource table with an encrypted ConnectionString to hold the updated content.
While you can't decrypt it here you might be able to extract some of the connection strings and then flag outdated ones; but I haven't been able to find how it stores links between the old/new content.
This has also been been discussed before on Ask SSC where they reached basically the same conclusion https://ask.sqlservercentral.com/questions/20328/decrypting-datasources-in-ssrs.html
And there's a collection of partial scripts for calling the web API and WMI on SSC itself when they discussed the same http://www.sqlservercentral.com/Forums/Topic1312030-150-1.aspx
I'd encourage you to flip the problem and try to come up with a PowerShell solution because with the increasing complexity of SQL Server it's simply not feasible to try to do everything within T-SQL anymore.
Using the ReportingServicesTools module for Powershell to get data source details is probably the best (get-RsDatasource), as it's able to extract and decrypt the ConnectString for a datasource (without password or username info in it), and also give the username it might be using in a separate field. It does this through the SOAP provider.
A Datasource stored in the Catalog table (type=5) does also usually have its ConnectionString stored in the [Content] field, which you can then try and pull out with a XPath query. But on my server, I have a couple of these datasources that have their connection info somehow stored somewhere else, so the SQL-based query returns no connection string info for them. The ReportingServicesTools get-RsDatasource function will return the connection string for these, however.
I also have a bunch of 3rd-party system-supplied reports that have their own custom provider. These don't store a connection string in their RDL or in the ReportingServices..Catalog or ReportingServices..Datasource tables. get-RsDatasource returns bupkus for these reports' connections.
I imagine that if I was slightly more motivated, I'd add in some more stuff to my t-sql query to try and grok the connection info out of the RDL as well. But except for the app reports, our internally developed reports all use shared datasources, so my motivation for this is low.