Tracking Usage in Reporting Services

  • sjs-36273

    SSCommitted

    Points: 1854

    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

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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]

  • Niels Naglé

    Ten Centuries

    Points: 1028

    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

  • dva2007

    SSCertifiable

    Points: 7703

    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,

    @userid,

    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

  • david_leibowitz

    Valued Member

    Points: 67

    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!

  • mw_sql_developer

    SSCoach

    Points: 19439

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply