Usage data for SSRS reports to decide on reports to consider for re-engineering & retiring

You have a SSRS instance on either live or production and you want to find out which reports and most / least used.

Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can go only as far back as 60 days but this is quite useful in many cases and this can be extended.

To be able to access this information you would have to used System view named ExecutionLog traditionally and for backward compatibility. ExecutionLog2 for SQL Server 2008 and ExecutionLog3 for SQL server 2008 R2. Each subsequent newer version added more info and renamed and few columns.

For detailed documentation checkout here in MSDN. To be able to extend the 60 days default limit, fiddle with ExecutionLogDaysKept property (The number of days to keep report execution information in the execution log. Valid values for this property include 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log table.)

An example of TSQL to extend this limit is like this,

 

EXEC SetConfigurationInfo @Name=N'ExecutionLogDaysKept',@Value=N'90'
 

 

 

Below are some example queries to play around with.


SELECT
ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate
FROM ExecutionLog AS ex INNER JOIN
Catalog AS cat ON ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC

select * from ExecutionLog3
select * from Catalog

select cat.path + '/' + cat.name Report,   max(TimeStart) LastRunTime, count(reportid) NoOfExec
from ExecutionLog3 ex, Catalog cat
where ex. = cat.ItemID
group by cat.path , cat.name
order by NoOfExec desc
order by  max(TimeStart) desc

select name, CreationDate  from Catalog cat
where cat.itemid not in (select  reportid from ExecutionLog ex )
order by CreationDate desc

where ex.ReportID = cat.ItemID)

Advertisements
About

IT professional with wider interest in technology

Tagged with: , , ,
Posted in sql, SQL Server, ssrs, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: