View: ExecutionLog
| InstanceName | nvarchar(38) | NOT NULL | Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER |
| ReportID | uniqueidentifier | NULL | The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times). |
| UserName | nvarchar(260) | NULL | Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername) |
| RequestType | bit | NOT NULL | User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user. |
| Format | nvarchar(26) | NULL | This is the rendering format. Mostly RPL if viewed in MS Internet Explorer. |
| Parameters | ntext | NULL | Parameters and the values they were submitted with. |
| TimeStart | datetime | NOT NULL | Time report started to run. |
| TimeEnd | datetime | NOT NULL | Time report finished running? Need to check what finished? |
| TimeDataRetrieval | int | NOT NULL | Milliseconds spent retrieving the data. |
| TimeProcessing | int | NOT NULL | Milliseconds spent processing the report. |
| TimeRendering | int | NOT NULL | Milliseconds spent rendering the report. |
| Source | int | NOT NULL | Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History) |
| Status | nvarchar(32) | NOT NULL | either rsSuccess or an error code; if multiple errors occur, only the first error is recorded |
| ByteCount | bigint | NOT NULL | Size of rendered reports in bytes. |
| RowCount | bigint | NOT NULL | Number of rows returned from queries. |
Conversions Needed:
- I want to convert a start and end date to a single time value (hours minutes seconds mseconds)
- I want to total 3 of the columns (which measure in milliseconds) and convert to a time value with milliseconds showing.
- Now why don't these match?
- Using in an aggregate, how do I get the milliseconds between start and end times instead of summing the remaining columns?
- Correction, sum will only give me the total time but what if a report has been run twice more often then another, then is it slower? Not necessarily.
- Like all measures in bytes, I would like a thousand separator, and lose the decimal ".00".
- I need the size of the report but the value has to be returned as part of the aggregate query.
- I want the SQL to return just numbers and let the MDX query add the thousand separator.
My answer for everything:
- CAST((TimeEnd - TimeStart) AS TIME)
- CAST(CONVERT(CHAR, DATEADD(millisecond, [TimeDataRetrieval]+[TimeProcessing]+[TimeRendering], '00:00:00'), 121) AS TIME)
- Unaccounted time.
- SUM(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenSum]
- AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenAvg]
- LEFT(CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountSum]
- LEFT(CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountAvg]
- Use the data format: "$#,##0;($#,##0)"
A quick query to show the execution log with at least the report name for us human users:
copyraw
SELECT a.[InstanceName]
,b.[Name]
,a.[UserName]
,a.[RequestType]
,a.[Format]
,a.[Parameters]
,a.[TimeStart]
,a.[TimeEnd]
,a.[TimeDataRetrieval]
,a.[TimeProcessing]
,a.[TimeRendering]
,a.[Source]
,a.[Status]
,a.[ByteCount]
,a.[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog] a
INNER JOIN [ReportServer].[dbo].[Catalog] b
ON a.ReportID = b.ItemID
- SELECT a.[InstanceName]
- ,b.[Name]
- ,a.[UserName]
- ,a.[RequestType]
- ,a.[Format]
- ,a.[Parameters]
- ,a.[TimeStart]
- ,a.[TimeEnd]
- ,a.[TimeDataRetrieval]
- ,a.[TimeProcessing]
- ,a.[TimeRendering]
- ,a.[Source]
- ,a.[Status]
- ,a.[ByteCount]
- ,a.[RowCount]
- FROM [ReportServer].[dbo].[ExecutionLog] a
- INNER JOIN [ReportServer].[dbo].[Catalog] b
- ON a.ReportID = b.ItemID
The Top 5 Most Frequent
copyraw
-- Top 5 Most Frequent: SELECT TOP 5 COUNT(Name) AS ExecutionCount , Name , CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg] , AVG(ByteCount) AS [ByteCountAvg] , AVG([RowCount]) AS [RowCountAvg] , SUM(ByteCount) AS [ByteCountSum] , SUM([RowCount]) AS [RowCountSum] , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum , SUM(TimeProcessing) AS TimeProcessingSum , SUM(TimeRendering) AS TimeRenderingSum FROM ( SELECT TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount] FROM Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2 ) AS RE GROUP BY Name ORDER BY COUNT(Name) DESC , Name
- -- Top 5 Most Frequent:
- SELECT TOP 5
- COUNT(Name) AS ExecutionCount
- , Name
- , CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg]
- , AVG(ByteCount) AS [ByteCountAvg]
- , AVG([RowCount]) AS [RowCountAvg]
- , SUM(ByteCount) AS [ByteCountSum]
- , SUM([RowCount]) AS [RowCountSum]
- , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
- , SUM(TimeProcessing) AS TimeProcessingSum
- , SUM(TimeRendering) AS TimeRenderingSum
- FROM
- (
- SELECT
- TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
- FROM
- Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
- WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2
- ) AS RE
- GROUP BY
- Name
- ORDER BY
- COUNT(Name) DESC
- , Name
Category: Databases :: Article: 375
