Hey there,
There are several queries on the web that will help you combine all that static analytics. Depending on the tool you use to report themout, you may end up creating a virtual dimensional model. I have twicked some from the web to do my bit of report tracking i hope it helps.
Subscription tracking query (what's missing can be collected and staged from the logfiles, something I have not worked on yet) :
select
convert(varchar(1000),case
when Cat.Type = 13 then 'Hyperlink to dashboard'
else 'Hyperlink to Paginated reports'
end) collate Latin1_General_CI_AS as RootPath ,
convert(varchar(1000),CAT.[Path]) collate Latin1_General_CI_AS as Path,
CAT.Name,
S.[SubscriptionID],
S.[Report_OID],
S.[DeliveryExtension],
S.[ExtensionSettings],
Owner.[UserName] as OwnedBy,
Modified.[UserName] as ModifiedBy,
S.[ModifiedDate],
S.[Description],
case when left(S.[LastStatus],9) = 'Completed' then 'Y' else 'N' end as IsRefreshSuccessful,
S.[LastStatus],
S.[EventType],
S.[Parameters],
S.[DataSettings],
A.[TotalNotifications],
A.[TotalSuccesses],
A.[TotalFailures],
S.[LastRunTime],
CAT.[Type]
from
ReportServer.dbo.[Subscriptions] S
inner join ReportServer.dbo.[Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
inner join ReportServer.dbo.[Users] Owner on S.OwnerID = Owner.UserID
inner join ReportServer.dbo.[Users] Modified on S.ModifiedByID = Modified.UserID
left outer join ReportServer.dbo.[SecData] SD on CAT.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
left outer join ReportServer.dbo.[ActiveSubscriptions] A with (NOLOCK)
on S.[SubscriptionID] = A.[SubscriptionID]
where
(S.[ReportZone] = 0)
and (S.[EventType] = 'RefreshCache'
OR S.[EventType] = 'DataModelRefresh' )
Report Stats:
with ExecutionLog3Marce
as
(
SELECT
c.itemid,
c.Name as ReportName,
InstanceName,
COALESCE(CASE(ReportAction)
WHEN 11 THEN AdditionalInfo.value('(AdditionalInfo/SourceReportUri)[1]', 'nvarchar(max)')
ELSE C.Path
END, 'Unknown') AS ItemPath,
convert(varchar(1000),case
when C.Type = 13 then 'Hyperlink to dashboard'
else 'Hyperlink to Paginated reports'
end) +
COALESCE(CASE(ReportAction)
WHEN 11 THEN AdditionalInfo.value('(AdditionalInfo/SourceReportUri)[1]', 'nvarchar(max)')
ELSE C.Path
END, 'Unknown')
as ReportWebPath,
UserName,
ExecutionId,
CASE(RequestType)
WHEN 0 THEN 'Interactive'
WHEN 1 THEN 'Subscription'
WHEN 2 THEN 'Refresh Cache'
ELSE 'Unknown'
END AS RequestType,
-- SubscriptionId,
Format,
Parameters,
CASE(ReportAction)
WHEN 1 THEN 'Render'
WHEN 2 THEN 'BookmarkNavigation'
WHEN 3 THEN 'DocumentMapNavigation'
WHEN 4 THEN 'DrillThrough'
WHEN 5 THEN 'FindString'
WHEN 6 THEN 'GetDocumentMap'
WHEN 7 THEN 'Toggle'
WHEN 8 THEN 'Sort'
WHEN 9 THEN 'Execute'
WHEN 10 THEN 'RenderEdit'
WHEN 11 THEN 'ExecuteDataShapeQuery'
WHEN 12 THEN 'RenderMobileReport'
WHEN 13 THEN 'ConceptualSchema'
WHEN 14 THEN 'QueryData'
WHEN 15 THEN 'ASModelStream'
WHEN 16 THEN 'RenderExcelWorkbook'
WHEN 17 THEN 'GetExcelWorkbookInfo'
WHEN 18 THEN 'SaveToCatalog'
WHEN 19 THEN 'DataRefresh'
ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
WHEN 1 THEN 'Live'
WHEN 2 THEN 'Cache'
WHEN 3 THEN 'Snapshot'
WHEN 4 THEN 'History'
WHEN 5 THEN 'AdHoc'
WHEN 6 THEN 'Session'
WHEN 7 THEN 'Rdce'
ELSE 'Unknown'
END AS Source,
Status,
ByteCount,
[RowCount]
--, AdditionalInfo
FROM ReportServer.dbo.ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN ReportServer.dbo.Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
)
select
LDAP.Name as EmployeeName,
EmployeeID,
convert(int,convert(varchar(10),TimeStart,112)) as ReportStartDateKey,
convert(int,convert(varchar(10),TimeEnd,112)) as ReportEndDateKey,
RepLog.*
from ExecutionLog3Marce RepLog
inner join SHLReporting.dbo.SHLLDAPUsers LDAP
on replace(Replog.UserName,'Your Windows Domain','') = sAMAccountName collate Latin1_General_100_CI_AS_KS_WS
and Username <> 'Admin account'
where
RepLog.ItemAction in ('ConceptualSchema', 'Render')
