Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ExecutionLogStorage table and Subscriptions table Expand / Collapse
Author
Message
Posted Friday, June 29, 2012 9:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 334, Visits: 1,054
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?
Post #1323410
Posted Monday, July 2, 2012 7:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, August 16, 2014 9:15 AM
Points: 2,818, Visits: 2,557
If you use data driven subscriptions, you can add a description to the subscription that will help you narrow it down.
Post #1323781
Posted Tuesday, July 3, 2012 9:48 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:57 PM
Points: 458, Visits: 857
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;

Post #1324762
Posted Wednesday, July 4, 2012 2:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:30 PM
Points: 334, Visits: 1,054
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.
Post #1325127
Posted Wednesday, July 4, 2012 6:06 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:57 PM
Points: 458, Visits: 857
Hi Marcia, if you do get a query together I'd love to see it and add it to the tool belt.

Good luck!
Post #1325142
Posted Thursday, September 27, 2012 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 6, Visits: 284
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.
Post #1365487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse