cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Satyasai
Frequent Visitor

Power BI Audit logs Extraction

Hi,

Our team will be extractting the audit logs from Power BI Admin Portal Manually.
Is there a way to automate, that extracts all the users who accessed the Reports on daily basis.
can someone suggest a better solution, in which Audit logs will be extracted automatically on a regular basis.

1 ACCEPTED SOLUTION

Hello @Satyasai


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

 

When 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

View solution in original post

11 REPLIES 11
pvuppala
Helper II
Helper II

I am thinking to include the the "Write back" code to sql within the same powershell script like this but I think I'm missing something with this syntax.  Not sure what the best way to handle all of the processing in PowerShell.

$activities | select @{Label='UpdtDt';Expression={$_.CreationTime.replace("T", " ").replace("Z", " ")}},@{Label='ShareDt';Expression={$_.CreationTime.replace("T", " ").replace("Z", " ")}},UserID,ItemName,CapacityName,WorkspaceName,Operation,SharingInformation |
Write-SqlTableData -ServerInstance "serverhostname\MS7SQL1D,49001" -DatabaseName "Audit_DB" -SchemaName "dbo" -TableName "PowerBI_Activity_Log_Master" 

 

Satyasai
Frequent Visitor

Hi @mariussve1 
Our requirement is to extract Auditlogs using PowerShell scripting, So the extarcted csv will be placed into a Network directory.
Can you please help on the shell scripting to extract Audit logs.

Hello @Satyasai


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

 

When 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

Dear @mariussve1 ,
Could you please let me know how to filter a particular Workspace using Power Shell script?

Regards,
Satya

Hi @mariussve1 

 

This is a very thorough response, kudos!

 

Quick question, how do you store securets to not use plain text inthe script? Is there a means of obtaining them from Azure KeyVault from PowerShell?

 

Also, do you currently push data to SQL through PowerShell or you save the files and ingest with SSIS or ADF?

Thanks!

 

 

Hi again @Satyasai ,

 

Yes, you can store secret in akv:

$secret = Get-AzKeyVaultSecret -VaultName "<your-unique-keyvault-name>" -Name "ExamplePassword" -AsPlainText

We are currently using adf, and adf has a component called copy data. This component can recive json formatted responses and store this as rows in azure sql database.

 

But you can of course use powershell script to save files, and the use adf or ssis or even power automate to load files into sql database table.

 

Br

Marius

@Satyasai 

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.

m-colbert
Resolver II
Resolver II

Hi @Satyasai 

 

This can be done several ways, but I found using PowerShell was the easiest method. Dropping the daily event log to a json file that can be read into a Power BI report, or push into SQL then into Power BI. 

 

Access the Power BI activity log - Power BI | Microsoft Docs

 

Hope this helps!

mariussve1
Responsive Resident
Responsive Resident

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

        }

 

@pvuppala 

I take the full json response and pass to a stored procedure as a varchar(max) and merge the data into tables in SQL. If you overlap times a bit it's ok as you have an id that is unique that you can skip. Using the JSON functios in SQL this is fairly easy to extract what you want.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors