Get Report Server queries from QueryStore in SQL Server 2016 SP1

Question

I have a database that holds multiple seldom-used, redundant tables that I want to delete.

However, if my report server username accesses the table at all, I want to leave the table where it is until I can modify that report.

How can I tell if a particular username has read from a table, and is it possible to log all the details of their session and query out to a table, particularly the exact time of their queries?

I have Query Store active on the database and it's been turned on for a couple of weeks.

What I really want are the names of the reports being run that lead to these tables being accessed, so I can go point them at the newer tables and delete these old ones.

I figure I can get to that if I can get accurate timestamps for when a table was accessed by my report server login, I can then match that up with the time reports were run to let me know which reports access certain tables.

Or perhaps there's a way to actually join the SSRS tables to the querystore tables on session id, and I could just run a query looking for my report server username and particular tables?

Hope that makes sense?

Edit - Thanks to User Shekar Cola below I came to the following solution, which you can use to query your "ReportServer" DB and search the SQL of all reports on it:

  WITH XMLNAMESPACES(DEFAULT'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition')
SELECT c.ItemID as ReportID,
       c.Name as ReportName,
       c.path as ReportPath,
       CAST(CONVERT( xml, CONVERT(varbinary(MAX), Content)) AS varchar(MAX)) as ReportContent,
       CONVERT( xml, CONVERT(varbinary(MAX), Content) ).value('(/Report/DataSets/DataSet/Query/CommandText/text())[1]','varchar(1000)') as ReportQuery
  INTO #RSTemp
  FROM [dbo].[Catalog] c
 WHERE Content IS NOT NULL AND type =2
    GO

SELECT * 
  FROM #RSTemp
 WHERE ReportContent LIKE '%Any SQL command, table name or comment I want to search for!%'

Answer 1

perhaps there's a way to actually join the SSRS tables to the querystore tables on session id, and I could just run a query looking for my report server username and particular tables?

I don't think, there is possibility to have direct relationship as the session details in SSRS maintained by report services wherein the Query Store session details maintained by SQL Engine.

However, since you already have users report execution log within Report Server database select * from ExecutionLog2, with following query you can identity tables/views that are used for reports:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition')

select  c.ItemID as ReportID,
        c.Name as ReportName,
        c.path as ReportPath,
        --convert( xml, CONVERT(varbinary(max), Content) ) as ReportContent,
        convert( xml, CONVERT(varbinary(max), Content) ).value('(/Report/DataSets/DataSet/Query/CommandText/text())[1]','varchar(1000)') as ReportQuery
from [dbo].[Catalog] c
where Content is not null and type =2
go

Here is the definition of ExecutionLog2 (Builtin view of ReportServer database), you are free to add additional columns upon the requirement (save as different view do not replace ExecutionLog2 )

SELECT 
    c.ItemID as ReportID, ---- Additional Column added
    InstanceName, 
    COALESCE(C.Path, 'Unknown') AS ReportPath, 
    UserName,
    ExecutionId, 
    CASE(RequestType)
        WHEN 0 THEN 'Interactive'
        WHEN 1 THEN 'Subscription'
        ELSE 'Unknown'
        END AS RequestType, 
    -- SubscriptionId, 
    Format, 
    Parameters, 
    CASE(ReportAction)      
        WHEN 1 THEN 'Render'
        WHEN 2 THEN 'BookmarkNavigation'
        WHEN 3 THEN 'DocumentMapNavigation'
        WHEN 4 THEN 'DrillThrough'
        WHEN 5 THEN 'FindString'
        WHEN 6 THEN 'GetDocumentMap'
        WHEN 7 THEN 'Toggle'
        WHEN 8 THEN 'Sort'
        ELSE 'Unknown'
        END AS ReportAction,
    TimeStart, 
    TimeEnd, 
    TimeDataRetrieval, 
    TimeProcessing, 
    TimeRendering,
    CASE(Source)
        WHEN 1 THEN 'Live'
        WHEN 2 THEN 'Cache'
        WHEN 3 THEN 'Snapshot' 
        WHEN 4 THEN 'History'
        WHEN 5 THEN 'AdHoc'
        WHEN 6 THEN 'Session'
        WHEN 7 THEN 'Rdce'
        ELSE 'Unknown'
        END AS Source,
    Status,
    ByteCount,
    [RowCount],
    AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
GO