Power BI Audit logs Extraction

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
                            
                            }
                    }

        }

Anonymous 

The link I provided has the PowerShell script you will need. It loops through as many days of activity and you want to get. The cmdlet / api can only return a single day at a time. If you want a simple script to get a single days worth of activity you can use the following. 

Connect-PowerBIServiceAccount

$logDate = '2022-07-05'
$startDate = $logDate + ‘T00:00:00.000’
$endDate = $logDate + 'T23:59:59.999’
$outFile = "D:\Power BI Event Log\PowerBIAuditLog" + $logDate + ".json"

Get-PowerBIActivityEvent -StartDateTime $startDate -EndDateTime $endDate | Out-File $outFile

Disconnect-PowerBIServiceAccount

This will prompt you for a login but you can replace with ps $credentials to avoid it. It saves the output to a json file. This user must be a Power BI Admin or you need to enable Admin API calls in the tenant settings for other users.

Hope this helps.