November 14, 2018 at 4:24 pm
Hi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes sense
Kris
November 14, 2018 at 5:07 pm
Should be in the ExecutionLog
https://docs.microsoft.com/en-us/sql/reporting-services/report-server/reporting-services-log-files-and-sources?view=sql-server-2017
Here's a really helpful article I found...
https://www.sqlchick.com/entries/2011/2/6/querying-the-report-server-execution-log.html
Sue's right... I think your question is backwards... SSRS reports connect to databases and then run stored procedures inside them, not the other way around.
November 14, 2018 at 5:17 pm
Kris-155042 - Wednesday, November 14, 2018 4:24 PMHi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes sense
I must be missing something - databases don't connect to the report server but there are reports with data sources that connect to databases. Is that what you mean? The data sources for the reports?
Sue
November 14, 2018 at 5:50 pm
Sue_H - Wednesday, November 14, 2018 5:17 PMKris-155042 - Wednesday, November 14, 2018 4:24 PMHi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes senseI must be missing something - databases don't connect to the report server but there are reports with data sources that connect to databases. Is that what you mean? The data sources for the reports?
Sue
I want to know what databases reporting services are calling to run any given report. so if app1 is referenced by reporting service what is the database name for app1. Hope that makes better sense
Kris
November 14, 2018 at 8:36 pm
Kris-155042 - Wednesday, November 14, 2018 5:50 PMSue_H - Wednesday, November 14, 2018 5:17 PMKris-155042 - Wednesday, November 14, 2018 4:24 PMHi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes senseI must be missing something - databases don't connect to the report server but there are reports with data sources that connect to databases. Is that what you mean? The data sources for the reports?
Sue
I want to know what databases reporting services are calling to run any given report. so if app1 is referenced by reporting service what is the database name for app1. Hope that makes better sense
Yes...that makes sense. Not necessarily straight forward thing to do. And there would be some you could miss - such as deleted reports.
The Execution Log views are the only supported, documented tables for querying. But they will have the execution counts and date for the reports. In terms of the databases, those you would need to get from the data sources for the report. And you need to account for shared and embedded data sources. And those have the connection strings. The data sources are in the Catalog table and at times will be in the Data Sources table. The Catalog table has most things - you filter on the type for different objects. The Content xml column has different properties depending on the type. So...check this post that queries all of that, shreds the XML, etc:
SSRS: Auditing Report Queries
Sue
November 15, 2018 at 4:12 pm
Sue_H - Wednesday, November 14, 2018 8:36 PMKris-155042 - Wednesday, November 14, 2018 5:50 PMSue_H - Wednesday, November 14, 2018 5:17 PMKris-155042 - Wednesday, November 14, 2018 4:24 PMHi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes senseI must be missing something - databases don't connect to the report server but there are reports with data sources that connect to databases. Is that what you mean? The data sources for the reports?
Sue
I want to know what databases reporting services are calling to run any given report. so if app1 is referenced by reporting service what is the database name for app1. Hope that makes better sense
Yes...that makes sense. Not necessarily straight forward thing to do. And there would be some you could miss - such as deleted reports.
The Execution Log views are the only supported, documented tables for querying. But they will have the execution counts and date for the reports. In terms of the databases, those you would need to get from the data sources for the report. And you need to account for shared and embedded data sources. And those have the connection strings. The data sources are in the Catalog table and at times will be in the Data Sources table. The Catalog table has most things - you filter on the type for different objects. The Content xml column has different properties depending on the type. So...check this post that queries all of that, shreds the XML, etc:
SSRS: Auditing Report QueriesSue
I have run the script and get the following error
Msg 9420, Level 16, State 1, Line 4
XML parsing: line 1, character 3, illegal xml character
There is output one completed the error comes up.
Thanks
Kristen
Kris
November 27, 2018 at 3:12 pm
Kris-155042 - Thursday, November 15, 2018 4:12 PMSue_H - Wednesday, November 14, 2018 8:36 PMKris-155042 - Wednesday, November 14, 2018 5:50 PMSue_H - Wednesday, November 14, 2018 5:17 PMKris-155042 - Wednesday, November 14, 2018 4:24 PMHi, Is there a way to see what db's are connecting to report server over the last 30 days. Hope this makes senseI must be missing something - databases don't connect to the report server but there are reports with data sources that connect to databases. Is that what you mean? The data sources for the reports?
Sue
I want to know what databases reporting services are calling to run any given report. so if app1 is referenced by reporting service what is the database name for app1. Hope that makes better sense
Yes...that makes sense. Not necessarily straight forward thing to do. And there would be some you could miss - such as deleted reports.
The Execution Log views are the only supported, documented tables for querying. But they will have the execution counts and date for the reports. In terms of the databases, those you would need to get from the data sources for the report. And you need to account for shared and embedded data sources. And those have the connection strings. The data sources are in the Catalog table and at times will be in the Data Sources table. The Catalog table has most things - you filter on the type for different objects. The Content xml column has different properties depending on the type. So...check this post that queries all of that, shreds the XML, etc:
SSRS: Auditing Report QueriesSue
I have run the script and get the following error
Msg 9420, Level 16, State 1, Line 4
XML parsing: line 1, character 3, illegal xml character
There is output one completed the error comes up.Thanks
Kristen
I found how to get the custom data sources with the following query:
/*List connection strings of all SSRS Custom Datasources*/
WITH CatalogWithXml AS (
-- XMLifyies Catalog's Content column.
/* For report (Type = 2) and shared data source (Type = 5) objects, the image-typed column
Content stores the XML RDL defining the object. We convert this column to XML so that SQL's
XML type's functions can be used on it. */
SELECT *,
ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))
FROM Catalog
),
AllDataSources AS (
-- Details on both embedded & shared data sources *used* by reports.
/* Embedded data sources are defined in the hosting report's RDL. Shared data sources are
referenced (but not defined) in this RDL. We extract the relevant details and then join
to the SharedDataSources CTE to assemble a resultset with details on each data source
(embedded and shared) used by each report (identified by ItemID). */
SELECT r.ItemID,
r.name,
r.path,
r.LocalDataSourceName, -- embedded data source's name or local name given to shared data source
--sds.SharedDataSourceName,
--SharedDataSource = CAST ((CASE WHEN sds.SharedDataSourceName IS NOT NULL THEN 1 ELSE 0 END) AS BIT),
--DataProvider = ISNULL(r.DataProvider, sds.DataProvider),
ConnectionString = ISNULL(r.ConnectionString, 'N')
FROM (
SELECT c.*,
LocalDataSourceName = DataSourceXml.value('@Name', 'NVARCHAR(260)'),
DataProvider = DataSourceXml.value('(*:ConnectionProperties/*:DataProvider)[1]', 'NVARCHAR(260)'),
ConnectionString = DataSourceXml.value('(*:ConnectionProperties/*:ConnectString)[1]', 'NVARCHAR(MAX)')
FROM CatalogWithXml c
CROSS APPLY ContentXml.nodes('/*:Report/*:DataSources/*:DataSource') DataSource(DataSourceXml)
WHERE c.Type = 2 -- limit to reports only
) r
)
select * from AllDataSources
where not ConnectionString='N'
For Shared Data sources I used the below:
/*List 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[1]', '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;
Kris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply