The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
Is there a way to extract user activities more than 1 day by using Powershell.
Thanks in advance
Solved! Go to Solution.
#2. Authenticate to Power BI
$PBIAdminUPN="userName"
$PBIAdminPW="Password"
$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)
Connect-PowerBIServiceAccount -Credential $myCred
$AuditType = 'PowerBIAudit'
$RetrieveDate = Get-Date
$RetrieveDate = $RetrieveDate.AddDays(-2) #replace -2 with days you want to extract
for($i = 0; $i -lt 1; $i++){ #repalce 1 with days you want
$RetrieveDate = $RetrieveDate.AddDays(+1)
$RetrieveYearStr = $RetrieveDate.ToString('yyyy')
$RetrieveMonthStr = $RetrieveDate.ToString('MM')
$RetrieveDayStr = $RetrieveDate.ToString('dd')
$StartDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T00:00:00.000'
$EndDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T23:59:59.999'
$datestring = $RetrieveDate.ToString('yyyyMMdd')
$ActivityLogsPath = ('D:\Audit\PowerBIAudit_' + $datestring + '.csv')
#4. Export out current date activity log events to CSV file
$ActivityLogs = Get-PowerBIActivityEvent -StartDateTime $StartDT -EndDateTime $EndDT | ConvertFrom-Json
$ActivityLogSchema = $ActivityLogs | `
Select-Object `
Id,CreationTime,CreationTimeUTC,RecordType,Operation,OrganizationId,UserType,UserKey,Workload,UserId,ClientIP,UserAgent,Activity,ItemName,WorkSpaceName,DashboardName,DatasetName,ReportName,WorkspaceId,ObjectId,DashboardId,DatasetId,ReportId,OrgAppPermission,CapacityId,CapacityName,AppName,IsSuccess,ReportType,RequestId,ActivityId,AppReportId,DistributionMethod,ConsumptionMethod, `
@{Name="RetrieveDate";Expression={$RetrieveDate}}
$ActivityLogSchema | Export-Csv $ActivityLogsPath
Write-Host ('Created file {0}' -f $ActivityLogsPath)
}
use this code, replace username and password with yours and days with your requirement
Thanks to @GilbertQ for initial code.
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!
No. But of course you can write your own looping code in Powershell
Track user activities in Power BI - Power BI | Microsoft Docs
Thanks for the response, if you have loping code please send me otherwise is there any website I will find the code.
#2. Authenticate to Power BI
$PBIAdminUPN="userName"
$PBIAdminPW="Password"
$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)
Connect-PowerBIServiceAccount -Credential $myCred
$AuditType = 'PowerBIAudit'
$RetrieveDate = Get-Date
$RetrieveDate = $RetrieveDate.AddDays(-2) #replace -2 with days you want to extract
for($i = 0; $i -lt 1; $i++){ #repalce 1 with days you want
$RetrieveDate = $RetrieveDate.AddDays(+1)
$RetrieveYearStr = $RetrieveDate.ToString('yyyy')
$RetrieveMonthStr = $RetrieveDate.ToString('MM')
$RetrieveDayStr = $RetrieveDate.ToString('dd')
$StartDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T00:00:00.000'
$EndDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T23:59:59.999'
$datestring = $RetrieveDate.ToString('yyyyMMdd')
$ActivityLogsPath = ('D:\Audit\PowerBIAudit_' + $datestring + '.csv')
#4. Export out current date activity log events to CSV file
$ActivityLogs = Get-PowerBIActivityEvent -StartDateTime $StartDT -EndDateTime $EndDT | ConvertFrom-Json
$ActivityLogSchema = $ActivityLogs | `
Select-Object `
Id,CreationTime,CreationTimeUTC,RecordType,Operation,OrganizationId,UserType,UserKey,Workload,UserId,ClientIP,UserAgent,Activity,ItemName,WorkSpaceName,DashboardName,DatasetName,ReportName,WorkspaceId,ObjectId,DashboardId,DatasetId,ReportId,OrgAppPermission,CapacityId,CapacityName,AppName,IsSuccess,ReportType,RequestId,ActivityId,AppReportId,DistributionMethod,ConsumptionMethod, `
@{Name="RetrieveDate";Expression={$RetrieveDate}}
$ActivityLogSchema | Export-Csv $ActivityLogsPath
Write-Host ('Created file {0}' -f $ActivityLogsPath)
}
use this code, replace username and password with yours and days with your requirement
Thanks to @GilbertQ for initial code.
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!
Is there a way to call all objects instead of having to select specific ones?
no, but again you can loop through your list of objects, same as you loop through your list of days.
Hi Arvindsingh,
If i run the above powershell script i am getting individual csv files with PowerBIAudit_respective dates, but my requirement is instead of creating individual csv files, i want single csv file with combined csv files with name as PBI_Audit_Logs.
Thanks in Advance
You can use Powershell to combine CSV files
Remove-Item $Target
Get-Content "$Path\$file1" | Add-Content $Target
Get-Content "$Path\$file2" | Select -Skip 1 | Add-Content $Target
Get-Content "$Path\$file3" | Select -Skip 1 | Add-Content $Target
etc.
If you dont mine can u send me the combined powershell script to extract audit logs for more than 24 hours. Thanks in advance.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
36 | |
31 | |
23 | |
14 |