Reporting Services – Reviewing Report Run History

Background

An error that is recurring in our Reporting Services Installation is forcing me to take a look at Reporting Services tables.

 

Error

The error reads “Invalid or Expired Session: ex2vxzbn54n1ee55do4qptfe“.

Past

Already addressed once before…

SQL Server – Reporting Services – Error – “Invalid or Expired Session”

Link

 

Metadata

Let us see if we can correlate the Session ID mentioned in the error with anything else logged in our Reporting Services tables.

 

Metadata

Report – Execution Log History

Outline

Here are the columns that we will be returning…

Table Column Meaning
dbo.Catalog ItemID Report ID
dbo.Catalog Name Report Name
dbo.Catalog Path Report Path
dbo.ExecutionLogStorage TimeStart Time Report Requested
dbo.ExecutionLogStorage RequestType Request Type
dbo.ExecutionLogStorage Source Request Source (1=Live, 2=Cache, 3=Snapshot, 4=History, 5=Adhoc, 6=Session, 7=RDCE).
dbo.ExecutionLogStorage Parameters Parameters
dbo.ExecutionLogStorage RowCount Row Count
dbo.ExecutionLogStorage bytesInKB Report Output Size

 

Code



/*
	Report Server ExecutionLog and the ExecutionLog3 View
	https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view
	Source	Source of the report execution. Possible values:

	Source	:- Source of the report execution. 
	Possible values: (1=Live, 2=Cache, 3=Snapshot, 4=History, 5=Adhoc, 6=Session, 7=RDCE).

*/
; with cteReportExecutionSource
(
	  [id]
	, [literal]
)
as
(
	select 
		   1 as [id]
		, 'Live' as [literal]

	union all 

	select 
		   2 as [id]
		, 'Cache' as [literal]

	union all

	select 
		   3 as [id]
		, 'Snapshot' as [literal]

	union all

	select 
		   4 as [id]
		, 'History' as [literal]

	union all

	select 
		   5 as [id]
		, 'Ad Hoc' as [literal]

	union all

	select 
		   6 as [id]
		, 'Session (Another request within existing session)' as [literal]

	union all

	select 
		   7 as [id]
		, 'RDCE (i.e., Report Definition Customization Extension)' as [literal]

)
, cteRequestType
(
	  [id]
	, [literal]
)
as
(
	select 
		   0 as [id]
		, 'Interactive' as [literal]
	
	union all
		
	select 
		  1
		, 'Subscription'

	union all
		
	select 
		  2
		, 'Refresh Cache'

)

, cteReportFormat
(
	  [id]
	, [literal]
)
as
(
	select 
		   'RPL' as [id]
		, 'Native Format' as [literal]
	
	union all
		
	select 
		  'EXCELOPENXML'
		, 'Excel Open XML'

	union all
		
	select 
		  'CSV'
		, 'Comma-separated values'

	union all

	select 
		  'XML'
		, 'XML'
)

select top 1000

	  [itemID] = tblC.ItemID

	, [reportName] = tblC.[Name]

	, [reportPath] = tblC.[Path]


	, [requestType]
		 = isNull
			(
				  cteRT.literal
				, tblEL.[RequestType] 
			)	
			
			
	--, tblEL.[Source]
	
	, [reportExecutionSource]
		 = isNull
			(
				  cteRES.literal
				, tblEL.[Source] 
			)	


	, [reportFormat]
		= coalesce
			(
				  cteRF.[literal]
				, tblEL.[Format]
			)

	, [parameters]
		= tblEL.[Parameters]

	, [reportTimeStart] 
		= tblEL.TimeStart

	, [reportTimeEnd] 
		= tblEL.TimeEnd

	, [durationInSeconds]
		= datediff
		(
			  second
			, tblEL.TimeStart
			, tblEL.TimeEnd
		)

	, [numberofRows]
		= tblEL.[RowCount]

	, tblEL.[Status]

	, [byteInKB]
		= ( 
			  tblEL.ByteCount
			/ ( 1024  )
		  )
		  	
	, tblEL.ExecutionId

	--, tblEL.*

from  [dbo].[Catalog] tbLC

inner join [dbo].[ExecutionLogStorage] tblEL

	on tblC.[ItemID] = tblEL.ReportID

left outer join cteReportExecutionSource cteRES

	on tblEL.[Source] = cteRES.id

left outer join cteReportFormat cteRF

	on tblEL.[Format] = cteRF.id

left outer join cteRequestType cteRT
	on tblEL.[RequestType] = cteRT.id

order by 

	tblEL.TimeStart desc


Output

 

Explanation

  1. Columns
    • dbo.ExecutionLogStorage.ExecutionId
      • Matches the session Id

 

Session

Outline

Please feel free to check the [ReportServerTempdb].[dbo].[SessionData] table to see if there are current sessions, as well.

 

References

  1. Docs / SQL / SQL Server / Reporting Services / Report server
    • Report Server ExecutionLog and the ExecutionLog3 View
      Link
  2. Docs / SQL / SQL Server / Reporting Services / Extensions
    • Reporting Services Extensions
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s