SSRS Report Users

Overview

SSRS Report Users answers who is using a report server, what it costs, and when.

The SSRS Report Users leaderboard, with the subscription share of each account
A filled tile and the words “service account” mark an account whose every run was a subscription. Those are schedules, not people.

Run count is the least interesting of the three things the execution log can say about an account, and it is the only one the previous version of this page showed. The other two change what you do:

  • Server time. An account running three reports that take four minutes each costs the server more than one running two hundred that take a second. Ranked by count, the second one is at the top and looks like the problem.
  • Interactive against subscription. A large share of every report server’s load belongs to a service account firing schedules. Presenting that account beside eight humans, with nothing to say it is not one, is how “our reporting is used by one person” gets said out loud in a meeting.

What this replaces

Five horizontal bars of run counts over seven days, above a four column grid whose only other facts were the count again and a raw datetime.

The query behind it used SELECT DISTINCT with COUNT(*) OVER (PARTITION BY UserName) to produce what a GROUP BY produces directly, computing a window function for every execution in the window and then discarding the duplicate rows.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Report Users.
  • Overview: click the Busiest accounts panel on the report server database’s overview page.

Requirements

  • A Reporting Services catalog database. The report is offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.ExecutionLog and dbo.Catalog.

The three views

Leaderboard

The SSRS report users leaderboard
One row per account, with each bar split into subscription runs and runs a person asked for.

One row per account, ranked by runs. The bar is split into the runs that were a subscription and the runs that were a person. The tile on the left carries the account’s initials; it is filled for an account whose every run was a subscription and outlined for everyone else, with the same distinction repeated in words underneath so the shape is never carrying it alone.

The dot beside the last seen time is green for an account that has been here inside the last hour.

When

Accounts down the side, hour of day across the top, opacity carrying how much of that account’s activity fell in that hour.

Each row is scaled against its own busiest hour rather than against the busiest cell on the chart. The question is when a given person works, and one subscription account firing four hundred reports at six in the morning would otherwise flatten every human row to nothing.

This is the view that tells you your overnight window is one schedule and your contention at nine o’clock is nine people opening the same dashboard.

Cost

The same rows ranked by server time instead of by run count. When the account at the top here is not the account at the top of the Leaderboard, the page says so as a finding.

The window

The toolbar carries 24 hours, 7 days and 30 days. This is the one control on the page that goes back to the server, because a different window is a different set of rows rather than a different view of the same ones. The choice is remembered between sessions.

Seven days is who uses the server. Twenty four hours is who used it this morning, which is what somebody investigating a slow morning actually wants.

Reading the grid

The SSRS report users grid
One row per account, with its run counts and what those runs cost.
Column What it holds
Account The account name from the execution log, with a service badge when every run was a subscription
Runs Executions in the window
Server time Total retrieval plus processing plus rendering, as a bar and a figure
Interactive against subscription The mix, always drawn full width because it is a proportion rather than a quantity
Reports touched Distinct catalog items, which separates someone living in one dashboard from someone exploring the catalog
Not succeeded Executions whose status was not rsSuccess
Last seen Relative time rather than a raw datetime

Findings

  • The busiest account, when it is a quarter or more of every run on the server.
  • Accounts that ran nothing but subscriptions, so they are schedules rather than people.
  • An account that costs the most server time despite not running the most reports.
  • Executions that did not succeed.

Actions

  • Click a row on the chart to select it in the grid, and the other way round.
  • Double click to open SSRS Reports Run.
  • Right click an account for the recent runs page, the speed page, or to copy the account name.
  • Right click the chart to copy it to the clipboard.

Where the data comes from

dbo.ExecutionLog3 when the report server has it, otherwise dbo.ExecutionLog, joined to dbo.Catalog and grouped by account. The hour breakdown is a second aggregate over the same window rather than a client side rollup, because thirty days on a busy server is a lot of rows to carry across the wire only to count them into twenty four buckets.

Frequently asked questions

Why is a service account at the top of my user list? Because it is running the subscriptions. The page marks it, and the bar shows the split, so it can be read past rather than mistaken for a person.

An account has a high run count but low server time. Is that a problem? Usually not. It is somebody refreshing a fast report, or a report being served from cache. Switch to the Cost view to rank by what the server actually spent.

Where does the account name come from? The execution log’s UserName, which is the account Reporting Services attributed the execution to. For a subscription that is the account the subscription runs as, not the person who created it.