SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ExecutionLogStorage table and Subscriptions table


ExecutionLogStorage table and Subscriptions table

Author
Message
Marcia J
Marcia J
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2759 Visits: 2062
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?
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17382 Visits: 2629
If you use data driven subscriptions, you can add a description to the subscription that will help you narrow it down.
davoscollective
davoscollective
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 1008
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 ON S.Report_OID = cat.ItemID
INNER JOIN Users U ON S.OwnerID = U.UserID
INNER JOIN ReportSchedule RS ON S.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;


Marcia J
Marcia J
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2759 Visits: 2062
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.
davoscollective
davoscollective
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 1008
Hi Marcia, if you do get a query together I'd love to see it and add it to the tool belt.

Good luck!
matt.austin
matt.austin
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 442
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.
shwetha2me
shwetha2me
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
Marcia J - Wednesday, July 4, 2012 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.

Hi Marcia,
I am running into same Issue. Please share your code if you found any solution to it.
Thank you

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search