Below script will give you the details of report execution in last 24 hours.
WHEN el.RequestType = 0 THEN 'Interactive'
WHEN el.RequestType = 1 THEN 'Subscription'
WHEN el.RequestType = 2 THEN 'Refresh Cache'END AS RequestType,
DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',Time (Sec)',
(el.TimeDataRetrieval/1000.00) AS 'Data Retrieval
(el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',
(el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',
WHEN el.Source=1 THEN 'LIVE'
WHEN el.Source=2 THEN 'Cache'
WHEN el.Source=3 THEN 'Snapshot'
WHEN el.Source=4 THEN 'History'
WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'
WHEN el.Source=6 THEN 'Session'
WHEN el.Source=7 THEN 'Report Definition Customization Extension(RDCE)'
END AS 'Source',
el.ByteCount/1024.00 AS 'Size(Kb)',
el.[RowCount] AS 'Number of Records'
FROM ExecutionLog EL INNER JOIN Catalog cON c.itemid=el.reportid
WHERE TimeStart>= DATEADD(hh,-24,GETDATE())ORDER BY TimeStart DESCLet us look into the output columns :
- Path : Report path in the the reports manager
- Name : Report Name
- Request Type :This tells you how the report got executed . Interactive is the manual execution of the report using the report manager or a custom UI. Report executed as part of scheduled subscription will have value subscription for this column.
- Format : The rendering format
- TimeStart : Report process start time
- TimeEnd : Report process end time . The difference between the TimeStart and TImeEnd gives you the total duration of the request.
- Data Retrieval time (Sec) : The number of seconds spent on data sources and data extenstions in main report and all of its subreports. If the multiple data source are accessed parallel, this will be be the duration of the longest data set duration.If the data sources are accessed sequentially, this will be the sum of all data set duration.
- Processing Time (Sec) : The number of seconds spent in the processing the request. This include report processing bootstrap time and processing time for grouping,sorting,filtering ,aggregation ,subreport processing etc.
- Rendering Time : The number of seconds spent on rendering extension.This includes time spent on rendering, pagination module, on demand expression evaluation etc.
- Source :Specifies the type of execution.
- Live : data set queries are executed to get the result
- Cache: Reports is generated based on the data in the cache. Data set queries are not excuted
- Session :Subsequent request with an existing session .For example, initial request to view the report and subsequent request is to export to pdf.
- RDCE: Indicates Report Definition Customization Extension, that can dynamically customize a report definition before it is passed to the processing engine during the report execution
- Status : Report execution status . rssuccess denote the success of report execution.
- Size(KB) : Number of Kb generated as output of this report execution request.
- Number of Records:Number of rows processed.
SELECT * FROM ConfigurationInfo WHERE Name = 'ExecutionLogDaysKept'
If you liked this post, do like my page on FaceBook