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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1143 Visits: 1908
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4216 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
SSC Eights!
SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)

Group: General Forum Members
Points: 957 Visits: 1004
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1143 Visits: 1908
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
SSC Eights!
SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)

Group: General Forum Members
Points: 957 Visits: 1004
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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 441
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.
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