April 9, 2008 at 8:30 pm
I feel like there has to be something stupid that I am missing. I went to review reporting services execution logs today, and was a bit upset to find that the only data in the ExecutionLog table is old, nothing in there for months. First thing I did was go to report manager, click "site settings" and verified that execution logging was turned on (it was already set to on). I restarted the reporting service, then ran some reports, and rechecked the ExecutionLog table - still nothing being logged.
I know it was working at some point, because like I said, there is older data in the table. I have no idea if maybe some service pack screwed something up, or what happened to disable logging? I also have no idea what I need to do to re-enable logging.
Any ideas?
April 10, 2008 at 10:49 am
OK, I just solved my issue. Like I said, I thought it had to be something stupid and it was. I forgot how the reporting executionlog table was populated because I set it up so long ago. Details here:
http://technet.microsoft.com/en-us/library/aa964131.aspx
Basically a DTS package has to run to populate that table. I had also set up the job to run the package nightly, but this machine is ONLY used for reporting services and that job is the only job. The agent service was never set to "automatic" and therefore at some point it was turned off (possibly after a server reboot) so the execution logging job had not run in a long time. I just changed the service to automatic (always on) and manually ran the job to populate the execution logging database and everything looks great now, I have all the data that I thought was missing.
Here's a nice little query you can use to summarize results (report hits by month):
declare @TimeStart datetime, @TimeEnd datetime
set @TimeStart='1/1/2008'
set @TimeEnd='12/31/2008'
SELECT ReportPath, Report, UserName, Count(*) Hits, Month, MonthNum FROM (
SELECT reports.path ReportPath, reports.[Name] Report, u.UserName, ExecutionLogs.TimeStart [Date], convert(varchar(30), datename(month, ExecutionLogs.TimeStart) ) Month,datepart(month, ExecutionLogs.TimeStart) MonthNum
FROM ExecutionLogs
join reports ON reports.ReportKey = ExecutionLogs.ReportKey
join users u on ExecutionLogs.userkey = u.userkey
WHERE ExecutionLogs.RequestType = 0 AND
TimeStart Between @TimeStart AND @TimeEND
--and reports.path like '/cms drug%'
)x
Group BY reportpath,Report, UserName, Month, MonthNum
order by MonthNum ASC,report
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply