Forum Discussion
Power BI Audit logs Extraction
Hello Anonymous,
To fully automate this you should create an app user (Service principal). Here is a guide on how to do it:
https://www.sqlbi.com/articles/creating-a-service-principal-account-for-power-bi-apiWhen you have created a service principal you should have tenantid, appid and secret.
Then you can use this script to take out yesterdays log:
# Power BI Activity Events
# Parameters
$TenantId = "put yout tenantid here"
$AppId = "put yout appid here" # Service PRincipal ID
$Secret = "put yout secret here" # Secret from Service Principal# Connect the Service Principal
$password = ConvertTo-SecureString $Secret -AsPlainText -Force
$Creds = New-Object PSCredential $AppId, $password
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Creds -Tenant $TenantId$headers = Get-PowerBIAccessToken
1..1 |
foreach {
$Date = (((Get-Date).Date).AddDays(-$_))
$StartDate = (Get-Date -Date ($Date) -Format yyyy-MM-ddTHH:mm:ss)
$EndDate = (Get-Date -Date ((($Date).AddDays(1)).AddMilliseconds(-1)) -Format yyyy-MM-ddTHH:mm:ss)Get-PowerBIActivityEvent -StartDateTime $StartDate -EndDateTime $EndDate -ResultType JsonString |
Out-File -FilePath "C:\Marius\PowerBI_AudititLog_$(Get-Date -Date $Date -Format yyyyMMdd).json"
}If you want to take out the last 7 days, you can only change the script to:
# Power BI Activity Events
# Parameters
$TenantId = "put yout tenantid here"
$AppId = "put yout appid here" # Service PRincipal ID
$Secret = "put yout secret here" # Secret from Service Principal# Connect the Service Principal
$password = ConvertTo-SecureString $Secret -AsPlainText -Force
$Creds = New-Object PSCredential $AppId, $password
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Creds -Tenant $TenantId$headers = Get-PowerBIAccessToken
1..7 |
foreach {
$Date = (((Get-Date).Date).AddDays(-$_))
$StartDate = (Get-Date -Date ($Date) -Format yyyy-MM-ddTHH:mm:ss)
$EndDate = (Get-Date -Date ((($Date).AddDays(1)).AddMilliseconds(-1)) -Format yyyy-MM-ddTHH:mm:ss)Get-PowerBIActivityEvent -StartDateTime $StartDate -EndDateTime $EndDate -ResultType JsonString |
Out-File -FilePath "C:\Marius\PowerBI_AudititLog_$(Get-Date -Date $Date -Format yyyyMMdd).json"
}I think the limit is 30 days.
If you dont want to use service principal, you need to login every time you get the files. Then you have to use Connect-PowerBIServiceAccount like m-colbert mention in the earlier posts.
Here you can read how to convert json to csv if this is important for you:
https://www.techcartnow.com/powershell-script-to-convert-complex-nested-json-to-csv-file-format
BR
Marius
We are using this rest api:
https://docs.microsoft.com/en-us/rest/api/power-bi/admin/get-activity-events
You can make a flow in Power Automate or Azure Data Factory and extract data on daily basis.
We push the data into our sql database and have a Power BI report Build on this table.
Works really nice
Br
Marius
Hi Marius,
I'm curious how are you updating your SQL table with this API resultset. We've been appending to a csv file but now we're thinking to switch to a SQL Server table. I have all the script to get activities based on when we updated it last but I need to update SQL Table somehow instead of appending CSV. Please let me know.
for ($a=0; $a -lt $days_since; $a++) {
$dt = "{0:yyyy-MM-dd}" -f (Get-Date).AddDays(-$days_since + $a)
#$activities = Get-PowerBIActivityEvent -StartDateTime '2021-12-13T00:00:00' -EndDateTime '2021-12-13T23:59:59' -ActivityType 'ShareReport' | ConvertFrom-Json
$st = $dt + "T00:00:00"
$et = $dt + "T23:59:59"
$activities = Get-PowerBIActivityEvent -StartDateTime $st -EndDateTime $et -ActivityType 'ShareReport' | ConvertFrom-Json
$activities | Select-Object UserId,ItemName,CapacityName,WorkspaceName,SharingAction,SharingInformation | Where-Object {($_.WorkspaceName -like 'PersonalWorkspace*') -or ($_.WorkspaceName -like '*[Production]') }
#$activities
$rowMax = $activities.Count
write-host "$($dt) has $($rowMax) rows" -ForegroundColor Yellow
for ($i=0; $i -lt $rowMax; $i++)
{
if ($rowMax -gt 0)
{
write-host "$($activities[$i].UserId) shared : $($activities[$i].ItemName) via $($activities[$i].SharingAction) from $($activities[$i].WorkspaceName) workspace on $($activities[$i].CreationTime)"
#$activities
#"$($activities[$i].CreationTime.Replace('T',' ').Replace('Z','')),$($activities[$i].UserId),$($activities[$i].ItemName),$($activities[$i].CapacityName),$($activities[$i].WorkSpaceName),$($activities[$i].SharingAction),$(($activities[$i].SharingInformation) | % {$_.RecipientEmail + $_.ObjectId +'[' + $_.ResharePermission +']'})" | Out-File -FilePath $output_file -Append -Encoding ASCII
"$($today),$($activities[$i].CreationTime),$($activities[$i].UserId),$($activities[$i].ItemName),$($activities[$i].CapacityName),$($activities[$i].WorkSpaceName),$($activities[$i].SharingAction),$(($activities[$i].SharingInformation) | % {$_.RecipientEmail + $_.ObjectId +'[' + $_.ResharePermission +']'})" | Out-File -FilePath $output_file -Append -Encoding ASCII
}
}
}
