Who is viewing reports in SQL Server 2012 Reporting Services

In previous versions, SQL Server 2012 Reporting Services provides us with a number of tables and views to let us see who has accessed what report and when they accessed the report.
The T-SQL query below selects from the ExecutionLog2 view in the ReportServer database.

USE ReportServer;
GO
SELECT el2.username,el2.InstanceName, 
el2.ReportPath,el2.TimeStart, 
el2.TimeEnd,el2.[Status],
isnull(el2.Parameters, 'N/A') as Parameters 
FROM ExecutionLog2 el2
GO

The first 13 rows output from the T-SQL code are shown below. As you can see in this image, we can see who viewed which report, when they viewed the report, and which parameters if any were passed to the report. This query itself could be used within an SSRS report and then published to the report server.

Using a query similar to the one shown below, we can look at the TimeEnd column to determine when the reports are being viewed. This will help us gain insight into when our processes that are generating the data behind the reports should complete. We can also look at the results of this query to determine behavioral patterns of the report users.

USE ReportServer;
GO
SELECT username, convert(varchar(25),TimeEnd, 120) as AccessTime
FROM ExecutionLog2
WHERE status='rsSuccess'
AND username='STAFF\user01' 
AND ReportPath='/Sales/YTDSalesByProductCategory'
ORDER BY AccessTime desc
GO

The query shown below will return the report access counts per user and report for the current month.

USE ReportServer;
GO
SELECT username, ReportPath, count(*) as ViewCount
FROM ExecutionLog2
WHERE status='rsSuccess'
AND TimeEnd>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
GROUP BY username, ReportPath
ORDER BY username, ViewCount desc
GO

Determine the Space Used by Each Table in a SQL Server Database

SET NOCOUNT ON

DECLARE @table_name VARCHAR(500)

DECLARE @SpaceUsed TABLE (    
    tablename sysname,
    row_count INT,
    reserved VARCHAR(50),
    data VARCHAR(50),
    index_size VARCHAR(50),
    unused VARCHAR(50)
)

DECLARE curTables CURSOR FOR 
SELECT s.name + ‘.’ + t.name  
FROM sys.tables t  INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

OPEN curTables
FETCH NEXT FROM curTables INTO @table_name
WHILE @@FETCH_STATUS = 0 
BEGIN  
    SET @table_name = REPLACE(REPLACE(@table_name, ‘[',''), ']‘, ”)

    — make sure the object exists before calling sp_spacedused
    IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
    BEGIN
        INSERT INTO @SpaceUsed EXEC sp_spaceused @table_name, false
    END
    FETCH NEXT FROM curTables INTO @table_name
END
CLOSE curTables
DEALLOCATE curTables

– Compute the total size of each table and add the schemaname to the result set
SELECT    s.name as schemaname,
        su.tablename,
        su.row_count,
        su.data,
        su.index_size,
        su.unused,
        CONVERT(decimal, CONVERT(bigint, REPLACE(su.data, ‘ KB’, ”)) +
            CONVERT(bigint, REPLACE(su.index_size, ‘ KB’, ”)) +
            CONVERT(bigint, REPLACE(su.unused, ‘ KB’, ”))) / 1000 AS [total (MB)]
FROM    @SpaceUsed su INNER JOIN sys.tables t
            ON t.name = su.tablename
        INNER JOIN sys.schemas s
            ON t.schema_id = s.schema_id
ORDER BY 
        [total (MB)] DESC,
        schemaname,
        tablename