December 14, 2007 at 12:58 pm
I am looking for a way to track usage on my Reporting Services environment. I know that there are log files located in \\servername\c$\WINDOWS\system32\LogFiles\W3SVC1 that contain activity information.
I am looking to report overall activity, users, and heavily used reports.
Is there a way to have the log file get inserted directly into a SQL database? If not, I can possibly ETL the files into a table but there is a file for every day which would be difficult to load. Is there a way to have Reporting Services create less log files?
Are there any tools out there that can automatically report the information that I am looking for?
Thanks,
Steve
December 26, 2007 at 9:45 am
You can query theReportServer database for this information as follows:
SELECT
ex.UserName, ex.Format, ex.TimeStart, cat.Name,
ex.Parameters,
CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate
FROM ExecutionLog AS ex, Catalog AS cat
where ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 27, 2007 at 2:58 am
Besides that you can query the database, there are also prebuild reports available from microsoft for specificially the reports you described.
The link with more information about these reports is pasted below.
http://technet.microsoft.com/en-us/library/aa964131.aspx
Have fun with it
January 10, 2008 at 9:17 am
hi,
When you write your stored procedure, at the beginning you can write some code which will create entry in one table. You can create one table like SQL_Usage (Report ID, USer ID, Username, datetime, parameter).
You will have table in ur database which is having user name detail so u can get all information accordingly. You can combine all parameter like @parameter = @parameter1 + @parameter2 and then insert in a table.
Finally write insert statement something liek below
insert into dbo.RS_Usage
(
ReportID, UserID, Username, [Datetime],parameters
)
select
1,
@useridno,
getdate(),
@parameters
Each time you run the stored procedure and RS_Usage table will be updated with all the information and you can track the data.
Hope this helps,
Vijay
November 27, 2009 at 5:53 pm
To scrub your SSRS logs, check out SQLScrubs. Datawarehouse, SSIS and sample Reporting Services usage reports
http://www.summitcloud.com/solutions/scrubs
The Community Edition is free on CodePlex!
December 20, 2011 at 8:45 am
I noticed that the table ExcutionLog only holds info for the last 3 months.
I may be wrong here. But let me know your thoughts on this
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply