Report Server Monitoring

4Eric — Wed, 19 Dec 2018 18:56:02 GMT

I am working on a report to monitor certain things on the Power BI report Server. I was wondering what items others may be monitoring on reports. I am willing to share what I have done so far.

Listed below are the items I am currently monitoring:

A. Whether the scheduled data refreshes failed or succeeded.

a. Would love to be able to get the failure message.

B. What is the average response time of a query.

a. Is there a way to determine when the report is first opened. I would like to calculate initial load time.

C. What was the longest response time of a query per day.

D. How many times a query took longer than 5 seconds.

Re: Report Server Monitoring

Anonymous — Wed, 26 Dec 2018 09:50:18 GMT

@4Eric,

You can also monitor the following items.

Who is viewing reports?
Which reports are the most popular?
When are reports being executed?
What is performance like (any slow reports?)?
What % of the workload is Power BI reports versus other report types?

Reference:
https://insightsquest.com/2018/01/29/power-bi-report-server-monitoring/

Regards,
Lydia

Re: Report Server Monitoring

lafondm — Fri, 06 Dec 2019 16:17:56 GMT

This is a great solution!

Re: Report Server Monitoring

mpsrshl — Sun, 08 Dec 2019 21:56:14 GMT

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')

topic Report Server Monitoring in Report Server

Report Server Monitoring

Re: Report Server Monitoring

Re: Report Server Monitoring

Re: Report Server Monitoring