Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Usage Tracking Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 7:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, Visits: 180
Hello -

I have found several articles online regarding usage statistics for existing reports/users but I am trying to figure out a way to log when a new report has been added to the Report Server. I don't fully understand the ExecutionLog and Catalog tables... maybe the information is being logged but I'm not sure how to query the data.

It would be very helpful to know what report has been added; when the report was added; and who added the report.

Thanks in advance!

Mike
Post #1431541
Posted Friday, March 15, 2013 9:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 2,014, Visits: 3,452
Hi Mike,
something like this?

use ReportServer
go

SELECT u.UserName, c.CreationDate, c.Name, c.[Path]
FROM dbo.[Catalog] c
left join dbo.Users u on u.UserID = c.CreatedByID

Cheers
Post #1431584
Posted Friday, March 15, 2013 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, Visits: 180
I can definitely use something like this! Thank you!

Do you know if it's possible to be notified when a new report has been pusblished? Maybe I can go that direction instead of seeing the entire catalog by user.

Thanks again!
Post #1431642
Posted Friday, March 15, 2013 11:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 2,014, Visits: 3,452
No problem!
Do you need instant notification?
You could achieve this with a trigger on the table calling database mail. I'd be wary of making changes directly to 'system' tables like this though.

Another way would be to create a job that returns the results for the last day/hour - whatever frequency works best.
This would show any items created the previous day: (note this includes folders, datasources etc, not just reports)

use ReportServer
go

SELECT u.UserName, c.CreationDate, c.Name, c.[Path]
FROM dbo.[Catalog] c
left join dbo.Users u on u.UserID = c.CreatedByID
where c.CreationDate >= CAST(CAST(DATEADD(DD, -1, GETDATE()) AS DATE) AS DATETIME)

Post #1431647
Posted Friday, March 15, 2013 11:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, Visits: 180
Very helpful! Thank you again!
Post #1431648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse