SSRS: Auditing Report Queries

Want to view a list of queries used in reports deployed on a Microsoft SQL Server Reporting Services (SSRS) server?

While SSRS’s web service exposes the functionality necessary to assemble this information, using that service requires programming/scripting skills or third-party software. Thankfully, these requirements can be bypassed by fetching this information directly from the report server database.

The below query returns query text and data source details for each local data set in each deployed report. The concepts covered can be extended to include shared data sets; however, implementing this is left as an exercise to the reader. (That’s the sophisticated way of saying “when I wrote this article, I only had access to SQL Express—which doesn’t support shared data sets—and so couldn’t test how to access their details.”)

Caveat: Microsoft doesn’t document or officially support querying the tables used below. Their schema and how data stored in them is interpreted can change between SSRS versions. If the query provided below doesn’t work on your version of SSRS, this could be why.

-- Provided "as is" with no warranties of any kind. User assumes all risks of use.
/* The XML XQuery statements used below ignore namespace so that information can be retrieved
   from multiple RDL versions. */
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
),
 
SharedDataSources AS (
    -- Details on uses of shared data sources.
    -- * Unused data sources are ignored.
    -- * ItemID identifies the catalog entry (e.g. report) using the shared data source. It is not
    --   the data source's ID!
    /* Table DataSource contains a row for each data source (embedded or shared) used in each report.
       Its column Name stores the data source name, as defined in the report. Shared data sources are
       defined (RDL XML) in the catalog. Inner joining between these two tables limits this CTE's
       resultset to details on shared data sources because embedded data sources don't have Link-ed
       rows in the catalog. */
    SELECT ds.ItemID,
        SharedDataSourceName = c.Name,
        LocalDataSourceName = ds.Name,
        DataProvider = ContentXML.value('(/*:DataSourceDefinition/*:Extension)[1]', 'NVARCHAR(260)'),
        ConnectionString = ContentXML.value('(/*:DataSourceDefinition/*:ConnectString)[1]', 'NVARCHAR(MAX)')
    -- Each DataSource row with a Link value represents a use of a shared data source.
    FROM DataSource ds
        -- Uses the Link value to look up the catalog entry defining the shared data source.
        JOIN CatalogWithXml c ON ds.Link = c.ItemID
),
 
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.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, sds.ConnectionString)
    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
        LEFT JOIN SharedDataSources sds ON r.ItemID = sds.ItemID AND r.LocalDataSourceName = sds.LocalDataSourceName
),
 
DataSets AS (
    -- Details on data sets used in reports.
    /* Outputs one row per data set used in each report. */
    SELECT ItemID,
        DataSetName = QueryXml.value('@Name', 'NVARCHAR(256)'),
        DataSourceName = QueryXml.value('(*:Query/*:DataSourceName)[1]', 'NVARCHAR(260)'),
        CommandType = QueryXml.value('(*:Query/*:CommandType)[1]', 'NVARCHAR(15)'),
        CommandText = QueryXml.value('(*:Query/*:CommandText)[1]', 'NVARCHAR(MAX)')
    FROM CatalogWithXml
        CROSS APPLY ContentXml.nodes('/*:Report/*:DataSets/*:DataSet') QueryData(QueryXml)
),
 
Data AS (
    -- Combines data set and data source details with additional information from Catalog.
    SELECT ds.ItemID,
        Name,
        Path,  
        LocalDataSourceName,
        SharedDataSource,
        SharedDataSourceName,
        DataProvider,
        ConnectionString,
		DataSetName,
        CommandType = ISNULL(CommandType, 'Text'), -- "Text" = default command type
        CommandText
    FROM DataSets ds
        JOIN AllDataSources src ON src.ItemID = ds.ItemID AND src.LocalDataSourceName = ds.DataSourceName
        JOIN Catalog c ON ds.ItemID = c.ItemID
)
 
SELECT *
FROM Data

16 thoughts on “SSRS: Auditing Report Queries

  1. Henro Veijer

    Ben, this code is wonderful, however, I do miss the shared datasources. Have you come any further on this or could you help me to add shared datasets to this? Friendly greetings, Henro

    Reply
  2. Wimpie Ratte

    Really useful! Thanks for this.
    Came across your post while searching for ways to locate reports that tries to connect with a specific login.
    This helps narrow it down by showing where they connect to and whether they have embedded connections or not :).

    Reply
  3. Wil

    First for others, when I used this I got an XML parsing error for unrecognized input signature. This is due to some of the images we have in our system. I just had to add a filter for type (type 3) in the XML XQuery statement.

    On another note, I have been walking down a similar path when I found this post. I noticed something a little odd. When i query the connection string of datasources in the catalog i get references to servers and catalogs when initially created. However the web gui, has a completely different connection string. I was attempting initiate a process to point reports from a DEV environment to a TEST environment. Has anyone had success doing this other than the gui?

    Reply
  4. JL Morrison

    This is great except its not working for me in SSRS2016. I guess the XML has different structure. Is there an updated version for 2016?

    Reply
  5. Takerme

    I have tried to run this on SQL2008 and get the following error.

    Msg 9420, Level 16, State 1, Line 4
    XML parsing: line 1, character 3, illegal xml character

    Does it not work on 08? What am I doing wrong?

    Reply
    1. Ben Gribaudo Post author

      This is strange. The error indicates that SQL Server wasn’t able to parse SSRS’s XML. I’d be curious whether the problem is systemic or just with a few rows in table Catalog.

      Could you try running something like the below, adjusting the WHERE clause on each run until you’ve isolated the rows having this problem (i.e. adjust the WHERE clause until all problematic rows are excluded and the query runs fine)?

      SELECT *,
         ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))
      FROM Catalog
      WHERE ItemID < = 1000 
      
      Reply
  6. Rudy Rodarte

    Thanks for publishing this. I think I found what is causing the issue. It seems the majority of records where the column MimeType is NOT null are causing the error below:

    Msg 9403, Level 16, State 1, Line 13  
    XML parsing: line 0, character 0, unrecognized input signature

    on this statement:
    ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))

    On our server all records with the following MimeTypes caused the error:
    image/png
    application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
    application/json; charset=utf-8

    Half of the records with the following MimeType failed:
    application/octet-stream

    All of the records with the following MimeType worked:
    application/xml; charset=utf-8

    Sadly, I don’t know the fix.

    Reply
    1. Wailen

      So I have added : WHERE Type <> 3
      In the first part of the query to filter the Type 3 (application/octet-stream and image/png) in the query and it works for me (SSRS2016). The first part of the query is now as follow:

      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
      	WHERE Type <> 3
      Reply
  7. 540YMX

    In case it helps anyone, I got this error:- XML parsing: line 5, character 18, A string literal was expected

    Which I was able to fix by adding a filter to ignore .odc datasets

         SELECT   *
                   ,        ContentXml = ( CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) )
                   FROM     Catalog
                   WHERE    name NOT LIKE '%.odc'
                            AND type IN ( 2, 5 )
    
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *