Long term storage of Power BI activity logs and statistics using Powershell
This article is originally published at https://tomaztsql.wordpress.com
From previous blogpost(s):
Daily monitoring and using statistics and activity logs for your Power BI tenant is always a great way to track the activity of your end users. If you need statistics to check what and how your end users are using the Power BI reports or see detailed activities for datasets, mobile reports or exports, tracking this is almost a “must have”.
Power BI gives you out-of-the-box statistics and end users’ activities. But the retention period is 90 days.
The sample script combines all workplaces into a single SQL Server table and extracts activity logs per day. And you can keep your logs for as long as you need them.
Create a SQL Server table up front, where you will be storing the activity logs:
CREATE TABLE [dbo].[PowerBIActivityLog](
[Id] VARCHAR(50) NULL,
[CreationTime] DATETIME NULL,
[Workload] VARCHAR(50) NULL,
[UserID] VARCHAR(50) NULL,
[Activity] VARCHAR(50) NULL,
[ItemName] VARCHAR(255) NULL,
[WorkSpaceName] VARCHAR(255) NULL,
[DatasetName] VARCHAR(500) NULL,
[ReportName] VARCHAR(500) NULL,
[WorkspaceId] VARCHAR(50) NULL,
[ObjectId] VARCHAR(500) NULL,
[DatasetId] VARCHAR(50) NULL,
[ReportId] VARCHAR(50) NULL,
[ReportType] VARCHAR(50) NULL,
[DistributionMethod] VARCHAR(50) NULL,
[ConsumptionMethod] VARCHAR(50) NULL,
[Import_Timestamp] DATEtime not NULL default(getdate())
);
GO
Once you have the table created, make sure to have the the following modules installed:
# Prerequisits - install Powershell Power Bi modules
Install-Module -Name MicrosoftPowerBIMgmt.Profile -Force
Install-Module -Name SqlServer -Force
And then simply run the script. This PowerShell cmdlet Get-PowerBIActivityEvent will retrieve the audit activity events for a given day (yesterday from 00:00:00 to 23:59:59 (UTC)) and insert the results into SQLServer [dbo].[PowerBIActivityLog] table.
# 1. Login to app.power.bi
$user = "[email protected]"
$pass = "YourStrongP422w$rd"
$SecPasswd = ConvertTo-SecureString $pass -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($user,$SecPasswd)
Connect-PowerBIServiceAccount -Credential $myCred
# 2. Get Data from app.powerbi/azure for previous day
$Datum = Get-Date((get-date ).AddDays(-1)) -Format "yyyy-MM-dd"
$StartDate = $Datum + 'T00:00:00'
$EndDate = $Datum + 'T23:59:59'
$json = Get-PowerBIActivityEvent -StartDateTime $StartDate -EndDateTime $EndDate | ConvertFrom-Json
$activity = $json | Select Id, CreationTime,Workload, UserId, Activity, ItemName, WorkSpaceName, DatasetName, ReportName, WorkspaceId, ObjectId, DatasetId, ReportId, ReportType ,DistributionMethod, ConsumptionMethod
# 3. Insert into SQL Server Database
Write-SqlTableData -InputData $activity -ServerInstance "MySQLServer2022" -DatabaseName "MyDatabase" -SchemaName "dbo" -TableName "PowerBIActivityLog" -Force
Follow for more Powershell Scripts for Power BI on Github.
Happy scripting and stay healthy!
Thanks for visiting r-craft.org
This article is originally published at https://tomaztsql.wordpress.com
Please visit source website for post related comments.
