Full Comprehensive PowerBI Service Audit

[icassiem]

Good day,

I have the task of doing a Full Comprehensive PowerBI service Audit, required:

1. The Number and name of reports

2. The Number of workspaces, the name and who are the custodians/members

3. The Number of inactive and active workspaces, the names

4. The number of users

5. The number of active users, name (who)

6. The number of inactive users, name (who)

7. What and the number active reports

8. what and number the failed reports

9. reports usage even if its forwarded by link

10. the reports refresh rate

11. the reports last used

12. the reports last refreshed 

13. The volume/data size of the workspace or entire power bi services

14. number of active personal workspace + report count

15. anything i am missing etc

I have powerbi admin access, and i cant get azure access from the IT but i can request the reports if needed, or do i require more access?

Please help

Regards

[v-venuppu · Accepted solution]

Hi @icassiem ,

For more details, you can refer the below Microsoft Documentations:
1.Power BI PowerShell Overview & Installation: Power BI Cmdlets reference | Microsoft Learn
2.Connecting to the Power BI Service:Connect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Learn

Here are some mapping to your requirements:
1.Report names & count:Get-PowerBIReport (MicrosoftPowerBIMgmt.Reports) | Microsoft Learn

2.Workspace names, count, members:Get-PowerBIWorkspace (MicrosoftPowerBIMgmt.Workspaces) | Microsoft Learn

3.Active/inactive workspaces:Get-PowerBIActivityEvent (MicrosoftPowerBIMgmt.Admin) | Microsoft Learn

4.Active users (names) > [Get-PowerBIActivityEvent]

5.Inactive users > Compare all users (Get-PowerBIWorkspaceUser) with active list from Get-PowerBIActivityEvent

6.Report usage (including link views) > [Get-PowerBIActivityEvent]

7.Get-PowerBIDataset (PowerShell):Get-PowerBIDataset (MicrosoftPowerBIMgmt.Data) | Microsoft Learn

8.Get Refresh History (Power BI REST API):Datasets - Get Refresh History - REST API (Power BI Power BI REST APIs) | Microsoft Learn
9.Failed refreshes > [Get-PowerBIRefreshHistory]

10.Data size per workspace > Admin API in Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn or Premium Capacity Metrics App

11.Personal workspaces > Get-PowerBIWorkspace -Scope Individual

Thank you.

[icassiem]

@v-venuppu  Thank You, i will give this a go

[v-venuppu]

Hi @icassiem ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[icassiem]

@v-venuppu , @rohit1991 , @MFelix 
Hi,

Regarding the powershell api calls for the below, is there more detail into how/the api's etc?

"

What you can get:

1. Report names & count >> Get-PowerBIReport -Scope Organization
2. Workspace names, count, members >> Get-PowerBIWorkspace -All + Get-PowerBIWorkspaceUser
3. Active/inactive workspaces >> Use last activity or refresh date
4. Active users (names) >> From Get-PowerBIActivityEvent
5. Inactive users >> Subtract from full user list (request from IT)
6. Report usage (including link views) >> Tracked in activity logs
7. Report refresh rate & last refreshed >> Get-PowerBIDataset + Get-PowerBIRefreshHistory
8. Failed refreshes >> Check refresh status
9. Data size per workspace >> Admin API or Premium Metrics App
10. Personal workspaces >> Get-PowerBIWorkspace -Scope Individual

1. Data size per workspace or tenant:
   You can get dataset sizes using Get-PowerBIDataset and some metadata columns (especially if you’re on Premium).
   Or just ask IT to help with the Premium Capacity Metrics App if you have Premium.
2. Personal workspaces with reports:
   Use Get-PowerBIWorkspace -Scope Individual and check which ones contain reports."

[v-venuppu]

Hi @icassiem ,

For a one-time or periodic Power BI Service audit covering:

 1.Refresh Failures Summary
Use the Power BI Activity Log via the Admin Portal or Microsoft 365 Compliance Center. Filter for RefreshDataset events to identify failed refreshes across all workspaces.
 2.Gateway Analysis
Go to the Manage Gateways section in Power BI Service. You will find:

Gateway status,Connected data sources,Admins and users.This helps you assess gateway health and usage.

 3.License Usage
Use the Microsoft 365 Admin Center to view:

Assigned Power BI licenses (Pro, PPU, Premium),Active vs inactive users,Last activity date (to identify unused licenses)

 For a Full Audit you can also:

Use the Power BI Admin Portal to export workspace and user data.Access the Power BI Activity Log for detailed user actions.Consider the Power BI Capacity Metrics app if you're using Premium.

Thank you.

[v-venuppu]

Hi @icassiem ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991 @MFelix for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[MFelix]

Hi @icassiem ,

You should take a look into admin monitoring workspace.

https://learn.microsoft.com/en-us/fabric/admin/monitoring-workspace

There is also a external tool Measure Killer that allows you to scan your tenant and check that information it's a paid tool but it can really help if you have a lot of workspaces and reports to monitor.

https://www.brunner.bi/measurekiller

(I'm not related with this tool in any manner and do not received any payment to refer this tool)

[rohit1991]

Hi @icassiem 

You can cover most of the audit with Power BI Admin access using PowerShell and Admin APIs.

What you can get:

1. Report names & count >> Get-PowerBIReport -Scope Organization
2. Workspace names, count, members >> Get-PowerBIWorkspace -All + Get-PowerBIWorkspaceUser
3. Active/inactive workspaces >> Use last activity or refresh date
4. Active users (names) >> From Get-PowerBIActivityEvent
5. Inactive users >> Subtract from full user list (request from IT)
6. Report usage (including link views) >> Tracked in activity logs
7. Report refresh rate & last refreshed >> Get-PowerBIDataset + Get-PowerBIRefreshHistory
8. Failed refreshes >> Check refresh status
9. Data size per workspace >> Admin API or Premium Metrics App
10. Personal workspaces >> Get-PowerBIWorkspace -Scope Individual

1. Data size per workspace or tenant:
   You can get dataset sizes using Get-PowerBIDataset and some metadata columns (especially if you’re on Premium).
   Or just ask IT to help with the Premium Capacity Metrics App if you have Premium.
2. Personal workspaces with reports:
   Use Get-PowerBIWorkspace -Scope Individual and check which ones contain reports.

What You Can't Fully Do Without Azure:

• Get a clean list of all users in the tenant
• Tie usage back to things like job roles or departments
• Trace external sharing (guests might be tricky depending on how IT set up access)

[icassiem]

@MFelix , @rohit1991 @v-venuppu  Thank You for helping me

@MFelix my org does not have fabric and i have no funds for this exercise, so i wont be able to purchase for this once off task

@rohit1991 can you please provide the "How" in terms of the actual code examples on calling each API or links for each of the 14?
I dont have Premium for Premium Metrics App and i think i can skip this What You Can't Fully Do Without Azure

The list was defined by me so that could provide pivot tables view for my senior and stakeholders, hence anything important i am missing "Point 15"

[rohit1991]

Hi @icassiem 

Additional Items You Might Be Missing :

1. Additional Items You Might Be Missing :

   1. Refresh Failures Summary
   • List of all datasets with refresh failures (last 30/60/90 days)
   • Helps spot reliability issues early
   2. Power BI Apps:
   • Number of published apps, usage stats, and access mapping
   3. Paginated Reports (RDL):
   • Count, owners, access mapping, and refresh logs (if using Paginated Reports)
   4. Embedded Reports or Service Principal Use:
   • Check for reports used in embedding scenarios (e.g., external portals)
   • List Service Principals and what access they have
   5. Gateway Analysis:
   • On-prem gateways, associated datasources, and admins
   6. Tenant Settings Snapshot:
   • Export critical Power BI tenant settings (e.g., Export control, share externally, Publish to web etc.)
   • Useful for auditing governance posture
   7. License Usage:
   • List of Pro users not publishing or viewing reports (to optimize license cost)
   8. Sensitivity Labels & Data Classification:
   • Which reports/datasets are labeled? Any missing?
   9. Audit Log Retention:
   • Ensure important logs (activity, sharing, access) are being archived if M365 log retention is limited
   10. Datasets with Large Size or Long Refresh Duration:
   • High-resource datasets can impact capacity , identify them early

[MFelix]

Hi @icassiem ,

If you don't have a fabric capacity then you can check the Monitor Report Usage metrics
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-usage-metrics
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-modern-usage-metrics

[icassiem]

@rohit1991 , @MFelix  Thank You
@rohit1991 1. Refresh Failures Summary, 5. Gateway Analysis: and 7. License Usage:
Can you please provide the powershell code + api examples to all these i require, and how

@MFelix its more a once-off or every 2 years exercise, so i dont really want to be developing a report. Also this works for a specific dataset/report only, it does not give me the big picture of things i am not aware of, like personal workspaces, the users uasge etc