Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Anonymous
Not applicable

PowerBi Audit Log

 Hi, May I know if anyone can advise is there a way to run PowerBi analytics with the audit log?

Currently I need to export the CSV file manually and import into PowerBi to run analytics pattern.

 

Would appreciate if anyone could do it.

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi there

This is what I currently use and it works really well.

http://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-soluti...




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

15 REPLIES 15
caseycook
Helper I
Helper I

I have a SQL job that runs this PowerShell daily.  I'm no PowerShell expert, I basically put together some stuff from Adam Saxton (https://guyinacube.com/about/) and MS.  I'm sure it could be cleaned up.  I then bring the files into Power BI...

 

 

#Set-ExecutionPolicy Unrestricted -Scope CurrentUser -Force

# Option 1 - This can be used to be prompted for credentials
#$UserCredential = Get-Credential
#Connect-MsolService -credential $UserCredential

# Option 2 - If you really want to automate the script, you will
# want to hard code the credentials to log into Azure AD.
$User = "email@domain.com"
$PWord = ConvertTo-SecureString -String "SuperSecretPassword" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
Connect-MsolService -credential $UserCredential

# Create the session to Exchange Online
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

# Import the Exchange Online commands
Import-PSSession $Session
 
# Controls number of days to retrieve from audit log; customize as required
$Number_Days = 1
 
# Date control variables
$End_Date = (Get-Date).ToString("yyyy-MM-dd")
$Start_Date = (Get-Date).AddDays(-$Number_Days).ToString("yyyy-MM-dd")
$File_Date = (Get-Date).ToString('yyyy-MM-dd_hh_mm_ss')
 
# Output folder and file; customize as required
$Output_Folder = "d:\DataImports\PowerBIAuditLogs"
$Csv_File = "$Output_Folder\Audit_Log-$File_Date.csv"
 
# Check for output folder; create if required
#IF (!(Test-Path $Output_Folder -PathType Container)) {
#New-Item -ItemType Directory -Force -Path $Output_Folder
#}
 
# Get Audit Log data for PowerBI activities
$Result = 
Search-UnifiedAuditLog -StartDate $Start_Date -EndDate $End_Date -RecordType PowerBI -ResultSize 5000 | Export-Csv $Csv_File
 
# Site and document library name; customize as required; no sub-folders
#$SPO_Site = "Your SharePoint Online site URL here"
#$SPO_Folder = "Your Document Library name here"
 
# Connect to SharePoint / OneDrive for Business
#Connect-PNPOnline -url $SPO_Site -credential $User_Credential
 
# Upload csv file
#Add-PnPFile -Path $Csv_File -Folder $SPO_Folder

Exit
Anonymous
Not applicable

Hi Caseycook

The Script u shared is logging in using Personal Account,

Is there anyway we can connect using Azure Principal service account??

Anonymous
Not applicable

Hi, Thanks for your detailed explanation.
Do you know if there is information about Favorites reports/dashboards in this auditlog?
I would like to know which are the favorite reports/dashboards marked by the users

Hi @Anonymous as far as I am aware this is not currently possible.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I would like to access the Audit Log details related to Power BI services such as dataset details, count of datasets, number of users, active user details etc, using Power Shell scripts. While i explored i found the possible way to achieve this functionality is through Exchange Online. But i dont have privileges to execute Exchange Online cmdlets. Can you please help me, is there any alternate way to achieve this one.

By default, Audit Logs require Tenant Admin privileges.

But there is a work around we use.

In the Exchange Admin Center, you can create an admin role called "View-Only Audit Logs" or something similar.

For the Roles, assign the "View-Only" Audit Logs role.

Then add the role to the user who should get access. By doing this, a non-Tenant Admin can access the Audit Logs.

Of course there are various security concerns to keep in mind, but it is a techinical solution to get around the limitation of needing Tenant Admin permissions to see the Audit Logs.

The data is in the dataset. You just need to add in some additional logic




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thank you for your quick reply. Would you possibly be able to point me to a reference document that lets me know what is available inside the dataset? Or if you know, is there some powershell command I can use to iterate through all of it so that I can then go back and select the items I am interested in?

GilbertQ
Super User
Super User

Hi there

This is what I currently use and it works really well.

http://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-soluti...




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I too use this solution, but I have come across a few bits of information it appears to be lacking.

 

Specifically, I am trying to access some of the items available in the "Report Usage Metrics" feature such as "Distribution Method", "Platforms", and "Report page". Has anyone found a way to access this information programatically?

Anonymous
Not applicable

@jbnawrocki did you ever get an answer on this? It seems it's still not possible to extract Report page information from the audit logs. And with the Usage Metrics currently unreliable I'm looking for alternatives. Thanks.

Anonymous
Not applicable

Hi @GilbertQ

 

Thank you for your reply and solution.

At the moment is that only the one solution ?

Any other simpler or much convenient way than running power Shell and dumping files into share folder ?

 

Regards

BDA2

Hi there,

You could use this, but there is an associated cost

https://powerbi.microsoft.com/en-us/blog/announcing-the-power-bi-usage-metrics-solution-template/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Works like a charm! Anyone knows how to store the output of the script on Azure Storage account? Would be cool to schedule the powershell with an Azure Runbook and store the files on a storage account.

 

regards,

Stefan

Anonymous
Not applicable

@Stefkus Did you ever figure out how to do this? I have managed to get the powershell script working with task scheduler but I think using Azure runbook and Automate would be much nicer solution

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors