Office 365 PowerBI activity report

My colleagues asked me to get an Office 365 PowerBI user activity report. Not just a one time, but to get it regularly for a month. The purpose of that report is to identify who use PowerBI and know not, so they can safely revoke a PowerBI Pro license. I started thinking how to get that kind of information… I was not sure if I could get it from PowerBI portal and there was no PowerShell module for PowerBI so far.

There is one easy and obvious way found on Azure Portal. Unfortunately, it is not very useful in my case.

Luckily, there is a good article from Resa Rad how to make it happen.

There are some limitations, based on a way how Microsoft stores its information. The data is available for last 90 days only and you can query 5000 events per day. That’s not an issue – you can make a cycle there or run a script daily.

Define initial parameters

To begin with, let’s define variables which will be used later more than once. If you want to query for more than 1 day back or store the exported data in another folder – you are welcome to change them.

## List of variables ##
    [int]$DaysBack = 90
    [string]$OutputFolder = "C:\temp"
    [string]$LogFile = "C:\temp\PowerBIAuditLog.log"
    $ClearFiles = $true

Connect to Exchange Online by PowerShell

Next is the script preparation part when you need to connect to Exchange Online in this case. These days it is strongly recommended to use MFA for all admin accounts. You may need to install additional PowerShell modules to enable that option.

Write-Host "Collect logs for PowerBI reports access Script Start"
Connect-ExoPSSession

Create cycle for the number of days to go back to collect required PowerBI usage report data

for ($i = - $daysback; $i -le -1; $i++) {
    $startdate = ([datetime]::Today).AddDays($i)
    $enddate = $startdate.adddays(1)
    Write-host "Quering data from $startdate to $enddate"
    $Outfile = "$OutputFolder\PowerBIAuditLog_$($startdate.ToString('yyyyMMdd')).csv"
    Search-UnifiedAuditLog -StartDate $startdate -EndDate $enddate -RecordType PowerBI -ResultSize 5000 | ConvertTo-Csv | Out-File $Outfile
    if ( (get-Item $Outfile).length -eq 0) {
        Write-host "Search returned wrong result. Resulting file size is null. Trying to query again" > $LogFile
        Search-UnifiedAuditLog -StartDate $startdate -EndDate $enddate -RecordType PowerBI -ResultSize 5000 | ConvertTo-Csv | Out-File $Outfile
        if ( (get-Item $Outfile).length -eq 0) {
            Write-host "Search returned wrong result for the 2nd attempt. Resulting file size is null." > $LogFile
        }
    }
    $fileName = Get-ChildItem -Path $Outfile 
    Write-host "The report size file is $($filename.length)"  > $LogFile
}

Upload resulting PowerBI usage report file to SharePoint Online

After I managed to obtain all the required data and store it as a CSV file, I had to make an upload to the Teams (SharePoint Online) site for further processing. That was a part of the request and I found this part needed to be automated as well.

I spent some time troubleshooting this part, because there are quite a few versions of PowerShell modules for SharePoint. Found that SharePointPNPOnline fits here well. There is an option to connect without MFA, but I choose the more secure option.

The $ClearFile variable used in the last part of this code section gives you an option to delete file from a server after successful upload.

 $makeUrl = "https://[yourtenantname].sharepoint.com/sites/[sitename]" 
 $topSPOFolder = "Shared%20Documents%2FData%2FPower%20BI%20Audit%20Log%20Extracts"
   
 Connect-PnPOnline -Url https://[yourtenantname.sharepoint.com -UseWebLogin
 $filepath = [System.IO.Path]::GetDirectoryName($FileName.FullName) + "\" 
 $Urlpath = ($filepath.Replace($OutputFolder, '')); 
 $fn = $topSPOFolder + "\"; 
 $Result = Add-PnPFile -Path $fileName -Folder $fn; 
 $fn = $null 
 If ($ClearFiles -and $Result) {
     Remove-Item  $fileName
 }

Next steps and conclusion

I think there could be a next step in this process that would do an analysis automatically and remove a license. It makes sense, if there is no use for a defined period. Unfortunately, it was not a part of the request at this time, so I decided not to spend too much time on building that feature. Later on, there was another request to get pretty much the same data for other services. It was easy to do just updating couple of lines of code in this script.

Hope that would be helpful for you to get some data for your reports from Microsoft 365 and optimize license use for your tenant. If you want to know more about services users consume, you can try to query that information replacing “PowerBI” with another service name. All the options for audited activities documented here.

More Office 365 related articles