PBIRS Audit Dashboard

A self-hosted usage & refresh dashboard for Power BI Report Server (PBIRS). Point it at your ReportServer catalog database and get instant visibility into report adoption, per-user activity, and dataset refresh health — no changes to your report server required.

Built with React 18 + Vite + TypeScript on the front end and ASP.NET Core 8 on the back end.

Screenshots

Insights	Report Adoption

Features

• Insights — at-a-glance overview: report opens, reports viewed, refresh counts and failures, top reports by opens and by refresh volume, daily activity charts (bar/line), refresh outcome donut.
• Report Adoption — sortable/searchable table of report opens with unique-user counts; double-click any report for a per-user breakdown (who opened it, how often, first/last view).
• Refresh History — per-report refresh summary with success/failure counts, average/min/max durations, and click-to-filter stat cards.
• Settings — enter your report server connection (SQL or Windows auth) right in the UI, with a one-click connection test. Nothing sensitive lives in the client.
• Built-in help & guided tour — a spotlight walkthrough auto-runs on first launch and can be relaunched anytime from the header's ? button, which also opens a quick-reference help panel.
• Light/dark + 9 accent themes, responsive layout, collapsible sidebar.
• Custom logo — upload your company logo and set its display size right from the Settings page (live preview; stored server-side in gitignored App_Data/). The bundled client/src/assets/logo.svg remains the default until you do.

Tech stack

Layer	Technology
Front end	React 18, TypeScript 5, Vite 6
UI	Bootstrap 5 + react-bootstrap, Bootstrap Icons, hand-rolled SVG charts (no chart library)
Tables	TanStack Table v8 (sorting, pagination)
Routing / HTTP	React Router 6, Axios
Theming	CSS custom properties driven by a small React context (9 themes incl. dark, persisted to localStorage)
Back end	ASP.NET Core 8 (minimal hosting, MVC controllers)
Data access	Raw ADO.NET via Microsoft.Data.SqlClient — the only NuGet dependency
Database	Your existing PBIRS ReportServer catalog database (read-only)
Testing	xUnit (server), Vitest (client), GitHub Actions CI on every push/PR

Architecture

flowchart LR
    B[Browser\nReact SPA] -->|"/api/* (JSON)"| S[ASP.NET Core 8\nPbirsAudit.Server]
    S -->|read-only SQL| DB[(ReportServer\ncatalog DB)]
    S -.->|serves built SPA\nfrom wwwroot| B
    S <-->|save / load| F[App_Data/connection.json\ngitignored]

Loading

• The browser never talks to SQL Server — the .NET backend is the only thing holding the connection string, and the client only ever receives masked settings (hasPassword: true, never the password itself).
• vite build outputs straight into server/wwwroot, so the published server is a single self-contained site: static SPA + /api on one port.
• In development the two run separately (Vite on :5173 proxying /api → Kestrel on :5210).

Request flow, end to end: a page like Report Adoption calls /api/pbiserver/adoption?days=90 → PbiServerController clamps the day window and delegates to PbiAuditService → the service builds the connection string from the saved settings (or env var) and runs a parameterized query against ExecutionLog3/Catalog → a small reflection-based mapper (DbHelper) turns rows into DTOs → JSON back to the React table. If no connection is configured yet, the API returns 503 with a hint and the UI shows a "Not configured" badge that links to Settings.

Project structure

├── client/                      # React SPA (Vite)
│   └── src/
│       ├── Components/
│       │   ├── Layout/          # Header (logo top-right, theme picker), Sidebar
│       │   ├── PbiServer/       # Insights, ReportAdoption, RefreshHistory pages
│       │   ├── Settings/        # Connection settings page (test + save)
│       │   ├── Shared/          # DataTable, Pagination, LoadingSpinner, ServerBadge
│       │   ├── ThemeContext/    # 9-theme runtime theming
│       │   └── Toast/           # Toast notifications
│       ├── services/            # Axios API wrappers (pbiServer, settings, app)
│       └── utils/formatters.ts  # Date / AD-username / catalog-path helpers
└── server/                      # ASP.NET Core 8 API + static host
    ├── Controllers/             # PbiServer (data), Settings (config), App (info)
    ├── Services/                # PbiAuditService (SQL), ConnectionSettingsService (persistence)
    ├── Models/                  # DTOs
    └── Helpers/DbHelper.cs      # Minimal ADO.NET row→POCO mapper

Data source

The backend runs read-only SQL against standard PBIRS catalog objects — no custom views, no CLR, nothing to install on your report server:

Data	Source
Report opens (Power BI reports)	dbo.ExecutionLog3 where RequestType='Interactive' and ItemAction='ConceptualSchema'
Dataset refreshes	dbo.ExecutionLog3 where ItemAction='DataRefresh'
Report names	dbo.Catalog (joined by item path)

Retention note: ExecutionLog3 only holds as much history as your server's ExecutionLogDaysKept setting (default 60 days). Increase it in SSMS (right-click the server in Report Server mode → Properties → Logging) if you want the 90/180/365-day windows to be fully populated.

Prerequisites

• .NET 8 SDK
• Node.js 18+
• Network access to the SQL Server hosting your PBIRS ReportServer database
• A SQL login (or Windows account) with db_datareader on that database (or SELECT on dbo.ExecutionLog3 and dbo.Catalog)

Getting started

Visual Studio (fastest): open PbirsAuditDashboard.slnx and press F5. On the first run the build detects that wwwroot is empty, installs npm packages, and builds the React client into it (this one-time step can take a minute — watch the Output window); then your browser opens at http://localhost:5210 automatically. Go to Settings, enter your server details, hit Test Connection, then Save.

After editing client code, refresh the served copy with cd client && npm run build (or delete server/wwwroot and press F5 again) — or use the live-reload workflow below.

Command line / live client development:

# 1. API (http://localhost:5210)
cd server
dotnet run

# 2. Client (http://localhost:5173, proxies /api to the server, hot-reloads on save)
cd client
npm install
npm run dev

Open http://localhost:5173, go to Settings, enter your server details, hit Test Connection, then Save.

Connecting your report server

Everything is configured from the Settings page inside the app — no config files required. One thing up front: the app connects to the SQL Server catalog database behind your report server (usually named ReportServer), not to the PBIRS web portal URL.

1. Find the catalog database. On the machine running Power BI Report Server, open Report Server Configuration Manager → Database and note the SQL Server name and database name shown there (default: ReportServer).

2. Create a read-only login (recommended). The dashboard only ever SELECTs from two objects, so give it exactly that and nothing more:

CREATE LOGIN pbirs_audit_reader WITH PASSWORD = '<strong password>';
USE ReportServer;
CREATE USER pbirs_audit_reader FOR LOGIN pbirs_audit_reader;
GRANT SELECT ON dbo.ExecutionLog3 TO pbirs_audit_reader;
GRANT SELECT ON dbo.Catalog TO pbirs_audit_reader;

Prefer Windows authentication instead? Grant those same rights to the account the dashboard's server process runs as — that is the account Windows auth connects with.

3. Enter the connection in the app. Open Settings and fill in the form:

Field	What to enter
SQL Server	Host from step 1 — MYPBISERVER, a named instance MYPBISERVER\SQLEXPRESS, or host,port MYPBISERVER,1433
Database	ReportServer (or whatever step 1 showed)
Authentication	SQL Server for the login from step 2, or Windows to use the app process's account
Trust server certificate	Leave checked unless your SQL Server has a CA-issued TLS certificate (internal servers typically use self-signed)
Username / Password	The SQL login from step 2 (SQL auth only)

4. Test Connection, then Save. The test runs a real probe — it connects and verifies it can read ExecutionLog3 — so a green result means the dashboard will work. After saving, the header badge shows the connected host and every page loads live data.

If something's off:

• A "Not configured" badge in the header means nothing has been saved yet — the API returns 503 until a connection exists.
• Test fails with a certificate/TLS error → make sure Trust server certificate is checked.
• Test succeeds but charts look sparse → ExecutionLog3 only retains ExecutionLogDaysKept days of history (60 by default — see the retention note above), and "report opens" counts interactive Power BI (.pbix) report views specifically.

Testing

Both sides ship with unit tests, and CI (GitHub Actions) runs the full build plus both suites on every push and pull request:

# Server — xUnit: connection-string building/escaping, settings & branding
# persistence, API validation, and the password-never-echoed guarantee
dotnet test

# Client — Vitest: the display/formatting helpers (dates, AD names, catalog paths)
cd client
npm test

The SQL layer itself (PbiAuditService/DbHelper) needs a live ReportServer database and is deliberately outside the unit suite — the Settings page's Test Connection covers that path against your real server.

Production build

cd client
npm install
npm run build        # outputs into server/wwwroot

cd ../server
dotnet publish -c Release -o ./publish

The published server serves both the API and the built client — run PbirsAudit.Server and browse to its URL. Host it under IIS, a systemd service, or a container — walkthroughs for the two most common options follow.

Installing under IIS (Windows)

The typical home for this app in a PBIRS shop — often right on an existing utility/web server.

1. Install the .NET 8 Hosting Bundle on the IIS server (search "ASP.NET Core 8 Hosting Bundle" on Microsoft's download site — it includes the ASP.NET Core Module that lets IIS host the app). Restart IIS afterwards so the module loads:

net stop was /y
net start w3svc

2. Publish and copy the app:

# on your build machine, from the repo root (also builds the React client)
cd server
dotnet publish -c Release -o C:\inetpub\pbirs-audit

(Or publish locally and copy the output folder to the server.)

3. Create the site in IIS Manager:

• Application Pools → Add Application Pool — name it PbirsAudit, set .NET CLR version = No Managed Code (ASP.NET Core runs out-of-process; IIS just proxies).
• Sites → Add Website (or add an Application under an existing site) — physical path C:\inetpub\pbirs-audit, pick your port/host name, select the PbirsAudit pool.

4. Let the app save its settings — the Settings page (connection + logo) writes to App_Data, so the app pool identity needs write access there:

mkdir C:\inetpub\pbirs-audit\App_Data
icacls C:\inetpub\pbirs-audit\App_Data /grant "IIS AppPool\PbirsAudit:(OI)(CI)M"

5. (Windows auth to SQL only) If you plan to use Windows (integrated) authentication on the Settings page, the account that connects is the app pool identity — change it from ApplicationPoolIdentity to a domain account with read rights on the ReportServer database (Application Pools → Advanced Settings → Identity). SQL logins need no such change.

6. Protect the site. The dashboard ships with no login of its own, so use IIS to gate it: enable Windows Authentication and disable Anonymous Authentication on the site (IIS Manager → Authentication) to restrict it to domain users, or scope access with firewall rules / a reverse proxy.

Browse to the site, open Settings, and connect (see Connecting your report server).

Running in Docker (optional)

The repo ships a multi-stage Dockerfile (Node builds the client, the .NET SDK publishes the server, and only the slim ASP.NET runtime ships) plus a docker-compose.yml:

# either
docker compose up -d
# or manually
docker build -t pbirs-audit-dashboard .
docker run -d -p 8080:8080 -v pbirs-audit-data:/app/App_Data pbirs-audit-dashboard

Then browse to http://localhost:8080. Two container-specific notes:

• Persist /app/App_Data (the volume above) — that's where the saved connection and any uploaded logo live. Or skip the Settings page entirely and pass the connection via the ReportServerDatabase__Connection environment variable (see the commented example in docker-compose.yml).
• Use a SQL login. Linux containers can't do Windows integrated authentication against SQL Server — the read-only pbirs_audit_reader login from the setup guide is exactly right.

Configuration

Two ways to configure the report server connection (the first wins):

1. Settings page — saved server-side to server/App_Data/connection.json (gitignored).
2. Environment/config — set ReportServerDatabase__Connection (env var) or ReportServerDatabase:Connection in appsettings.json to a full connection string. Useful for containers.

Security notes

• This app ships with no authentication — anyone who can reach it can view usage data and change the connection settings. Put it behind host-level protection (IIS Windows auth, a reverse proxy with SSO, VPN/network rules) before exposing it beyond your own machine.
• Saved credentials are stored in plaintext JSON in App_Data/. Lock down file permissions on the host, and prefer the low-privilege, read-only SQL login from Connecting your report server over anything with broader rights.

License

MIT