June 29, 2012 at 9:19 pm
Is there a way to get only the ExecutionLogStorage rows that correspond to a particular subscription in the Subscriptions table?
I'd like to get some of the time columns from ExecutionLogStorage for a particular subscription so I can get some stats on what it takes to run a particular subscription.
I can join on Report_OID from Subscriptions and ReportID from ExecutionLogStorage and only get rows where RequestType = 1 (subscription). However, if I have a report that has multiple subscriptions, then I get rows from ExecutionLogStorage that are really for one of the other subscriptions for that report.
Basically, what I'm trying to do is to find out for a particular subscription how long it takes to run.
I'm not seeing anything else in those two tables that I can use to get a proper join. I'm not sure if I'm completely overlooking something or if this just isn't doable.
Or should I be taking a completely different approach to get this done?
July 2, 2012 at 7:17 am
If you use data driven subscriptions, you can add a description to the subscription that will help you narrow it down.
July 3, 2012 at 9:48 pm
Here's a query cobbled together from various sources that gives you a fair bit of info including report execution duration. If you've used a shared schedule then the schedule name is useful in combination with the report name. If you have one-off schedule settings then it will get the ugly GUID name.
select
U.UserName AS SubscriptionOwner,
cat.Path AS ReportPath,
S.EventType AS SubscriptionType,
S.DeliveryExtension AS DeliveryMethod,
Sc.Name AS ScheduleName,
Rs.ScheduleID
,cat.path
,cat.name
,CONVERT(nvarchar(10), min(ex.TimeStart), 103) as Earliest_run
,CONVERT(nvarchar(10), max(ex.TimeStart), 103) as Most_Recent_run
,sum(case when ex.Format = 'RPL' then 1 else 0 end) as OnScreen
,sum(case when ex.Format = 'EXCEL' then 1 else 0 end) as Excel
,sum(case when ex.Format = 'MHTML' then 1 else 0 end) as MHTML
,sum(case when ex.Format = 'PDF' then 1 else 0 end) as PDF
,sum(case when ex.Format = 'CSV' then 1 else 0 end) as CSV
,sum(case when ex.Format = 'HTML4.0' then 1 else 0 end) as HTML4
,sum(case when ex.Format = 'ATOM' then 1 else 0 end) as ATOM
,sum(case when ex.Format = 'IMAGE' then 1 else 0 end) as [IMAGE]
,sum(case when ex.Format = 'WORD' then 1 else 0 end) as WORD
,sum(case when ex.Format = 'XML' then 1 else 0 end) as [XML]
,sum(case when ex.Format NOT in
('EXCEL','MHTML','PDF','CSV','HTML4.0','RPL','ATOM','IMAGE','WORD','XML')
then 1 else 0 end) as Other
,AVG(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_average,
Max(Datediff(ms,ex.timestart,ex.timeend)/1000.0)
- Min(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_variance
,AVG(ByteCount) as Bytes
FROM ExecutionLog AS ex
INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID
INNER JOIN Subscriptions S ONS.Report_OID = cat.ItemID
INNER JOINUsers U ON S.OwnerID = U.UserID
INNER JOIN ReportSchedule RS ONS.SubscriptionID = RS.SubscriptionID
INNER JOIN Schedule Sc ON RS.ScheduleID = Sc.ScheduleID
group by cat.path, cat.name, U.UserName,
cat.Path,
S.EventType,
S.DeliveryExtension,
Sc.Name,
Rs.ScheduleID
Order by sum(case when ex.Format = 'RPL' then 1 else 0 end) desc;
July 4, 2012 at 2:25 pm
davoscollective,
Thanks very much. This gives me some excellent stats.
I'm still running into the problem that if I have the same report with multiple subscriptions (due to different parameters), I don't have a way of getting the stats narrowed down to just the runs for that subscription.
Since I wouldn't have the same report used with multiple subscriptions with the same parameter, I'm going to see if I can find a way of matching the parameters between the two tables. They are in completely different formats, so it's not just a matter of seeing if they are equal. I'll need to convert from one format to the other.
Unfortunately, due to another deadline, I'll probably have to drop this until next week. If I can figure out how to make the parameter formats match, I'll post the code.
July 4, 2012 at 6:06 pm
Hi Marcia, if you do get a query together I'd love to see it and add it to the tool belt.
Good luck!
September 27, 2012 at 1:51 pm
I'm trying to get the same info. How does the ExecutionLogStorage link with the Subscriptions table in SSRS? If anyone has any idea on this one you would save me a ton of time. Thanks.
November 15, 2017 at 1:50 pm
Marcia J - Wednesday, July 4, 2012 2:25 PMdavoscollective,Thanks very much. This gives me some excellent stats.I'm still running into the problem that if I have the same report with multiple subscriptions (due to different parameters), I don't have a way of getting the stats narrowed down to just the runs for that subscription.Since I wouldn't have the same report used with multiple subscriptions with the same parameter, I'm going to see if I can find a way of matching the parameters between the two tables. They are in completely different formats, so it's not just a matter of seeing if they are equal. I'll need to convert from one format to the other.Unfortunately, due to another deadline, I'll probably have to drop this until next week. If I can figure out how to make the parameter formats match, I'll post the code.
Hi Marcia,
I am running into same Issue. Please share your code if you found any solution to it.
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply