Forum Discussion

justingraff88's avatar
2 years ago

Best way to run a script to pull Power BI activity

I have a powershell script to pull out Power BI activity logs and have it exporting a json into my onedrive. Now I am stuck at the best way to automate it so that I don't have to go in and hit the button to run it every day. Looking for something that will not require my device to be on. I'm guessing there is something in Power Automate but everything I've found seems to be several years old. I'm not a developer so the fact I've gotten this far is a testament to copilot.

 

Script:

 

Connect-PowerBIServiceAccount

 

#Input values before running the script:
$NbrDaysDaysToExtract = 30
$ExportFileLocation = 'C:\Users\Justin.Graff\OneDrive - Coretek\Reports\Power BI Logs'
$ExportFileName = 'PBIActivityEvents'
#--------------------------------------------

#Start with yesterday for counting back to ensure full day results are obtained:
[datetime]$DayUTC = (([datetime]::Today.ToUniversalTime()).Date).AddDays(-1)

#Suffix for file name so we know when it was written:
[string]$DateTimeFileWrittenUTCLabel = ([datetime]::Now.ToUniversalTime()).ToString("yyyyMMddHHmm")

#Loop through each of the days to be extracted (<Initilize> ; <Condition> ; <Repeat>)
For($LoopNbr=0 ; $LoopNbr -lt $NbrDaysDaysToExtract ; $LoopNbr++)
{
[datetime]$DateToExtractUTC=$DayUTC.AddDays(-$LoopNbr).ToString("yyyy-MM-dd")

[string]$DateToExtractLabel=$DateToExtractUTC.ToString("yyyy-MM-dd")

#Create full file name:
[string]$FullExportFileName = $ExportFileName + '-' + ($DateToExtractLabel -replace '-', '') + '-' + $DateTimeFileWrittenUTCLabel + '.json'

#Obtain activity events and store intermediary results:
[psobject]$Events=Get-PowerBIActivityEvent -StartDateTime ($DateToExtractLabel+'T00:00:00.000') -EndDateTime ($DateToExtractLabel+'T23:59:59.999')

#Write one file per day:
$Events | Out-File "$ExportFileLocation\$FullExportFileName"

Write-Verbose "File written: $FullExportFileName" -Verbose
}
Write-Verbose "Extract of Power BI activity events is complete." -Verbose

1 Reply

  • Power Automate can't handle that workload.  Stick with Powershell, use the task scheduler, and keep that machine on and logged in.