pbiTools.psm1
|
# Power BI Tools and Library # (v) Bob, Prodata Sept 2019 # History # 24/09/2019 First Version # # Install-Module MicrosoftPowerBIMgmt function New-pbiReport { param( [Parameter(Mandatory=$True)]$Path, #File OR Folder. Can be Array [Parameter(Mandatory=$True)]$Workspace, #Single Workspace [Parameter(Mandatory=$False)]$Name, #Name of DataSet/Report. If Blank will take from File [Parameter(Mandatory=$False)]$SqlServer, #Name of SQL Servr to Map Model to (If a Model ) [Parameter(Mandatory=$False)]$UserName #Name of Current User. If specified will take over dataset if needed ) ) $ErrorActionPreference = "Stop" $WorkspaceID = Get-PowerBIWorkspace -Name $Workspace $BaseName =$Name foreach ($p in $Path) { Get-ChildItem -Path $p| Where {$_.extension -like ".pbix"}| foreach { Write-Verbose -Message $_ if (!($BaseName)) {$Name=$_.BaseName } else {$Name =$BaseName} $Id= New-PowerBIReport -Path $_.FullName -Name $Name -Workspace $WorkspaceID -ConflictAction:"CreateOrOverwrite" | Select -ExpandProperty "Id" $count =@(Get-PowerBIReport -Workspace $WorkspaceID -Name $_.BaseName ).Count # if ($count -gt 1) { # Invoke-pbiReportBindFix -ReportId $Id # } if ($SqlServer) { Set-pbiSqlServer -Workspace $Workspace -Name $Name -SqlServer $SqlServer -UserName $UserName } else { Set-pbiDatasetGateway -Workspace $Workspace -Dataset $Name } } } } #Check if Linked Report has Duplicate and fix by # # function Invoke-pbiReportBindFix { } function Check-pbiModules { Load-Module "SqlServer" Load-Module "MicrosoftPowerBIMgmt" } function Remove-pbiReport { param( [Parameter(Mandatory=$True)]$Workspace #Single Workspace ) $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIReport -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "groups/$WorkspaceId/reports/$Id" Invoke-PowerBIRestMethod -Url $Url -Method Delete } Get-PowerBIDataSet -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "groups/$WorkspaceId/datasets/$Id" Invoke-PowerBIRestMethod -Url $Url -Method Delete } } function Set-pbiRebindReport { param( [Parameter(Mandatory=$True)][object]$Workspace , #Single Workspace [Parameter(Mandatory=$False)][String]$Report="*", #Name of Report. Can be * [Parameter(Mandatory=$False)][String]$ForceDatasetId #DataSetId to force a Bind To ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIReport -WorkspaceId $WorkspaceId | Where {$_.Name -like $Report} | foreach { $DatasetId=$_.DatasetId $reportId=$_.Id $Url ="groups/$WorkspaceId/datasets/$DatasetId" $Id=Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $DatasetId | Select -ExpandProperty "Id" if (!$Id) { Write-Verbose -Message ("Rebind Report: " + $_.Name) #Get Name of Dataset in Remote Workspace $Url ="groups/$WorkspaceId/datasets/$DatasetId" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $DatasetName =$data.name if ($ForceDatasetId) { $newDatasetId=$ForceDatasetId } else { #Get Dataset ID for Local Workspace Dataset $newDatasetId=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -eq $DatasetName } | Select -ExpandProperty "Id" } #Rebind Report to Dataset specified $url="groups/$WorkspaceId/reports/$reportId/Rebind" $Body=@" {datasetId: "$newDatasetId"} "@ Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } } } function Set-pbiDatasetTakeOver { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Dataset="*", #Name of Dataset. Can by Array [Parameter(Mandatory=$False)]$UserName="" #Username of USer to Change Ownership to ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id =$_.Id $Url ="groups/$WorkspaceId/datasets/$Id/Default.TakeOver" Invoke-PowerBIRestMethod -Url $Url -Method Post -Body "" } } function Set-pbiDatasetGateway { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Dataset="*" #Name of Dataset. Can by Array ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset} |foreach { $Id =$_.Id $DataSet = $_.Name $IsOnPremGatewayRequired =$_.IsOnPremGatewayRequired if ($IsOnPremGatewayRequired) { $Url ="groups/$WorkspaceId/datasets/$Id/DiscoverGateways" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $GatewayId=$data.value.Id $Body=@" {"gatewayObjectId": "$GatewayId"} "@ $url="groups/$WorkspaceId/datasets/$Id/BindToGateway" Write-Verbose -Message $url try { Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } catch [System.Net.Http.HttpRequestException],[AggregateException] { write-Error ("Could Not Bind Gateway on DataSet $DataSet . Check SqlServer Name and Gateway Configuration.") write-Error (Resolve-PowerBIError -Last | Out-String) } catch { #write-Error ( $_.Exception) write-Error (Resolve-PowerBIError -Last | Out-String) } } } } function Get-pbiStatus { param( [Parameter(Mandatory=$True)] [object] $Workspace , #WorkSpaces. Can be Array [Parameter(Mandatory=$False)][string] $Dataset="*",#Name of Dataset. Can by WildCard [Parameter(Mandatory=$False)][hashtable] $Errors =@{}, #Hash Table of Errors During Processing to tag them on Status returned [Parameter(Mandatory=$False)][hashtable] $StartTimes =@{} #Hash Table of Start Times if Processing Failures not logged ) # $ErrorActionPreference = "Stop" $DataSetList=@() ForEach ($w in $Workspace ) { $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True} | Where-Object {$_.Name -NotIn "Usage Metrics Report"}|foreach { $Id=$_.Id $obj=$null $obj = New-Object System.Object $obj | Add-Member -type NoteProperty -Name WorkSpace -Value $w $obj | Add-Member -type NoteProperty -Name DataSetId -Value $_.id $obj | Add-Member -type NoteProperty -Name DataSet -Value $_.Name $obj | Add-Member -type NoteProperty -Name ConfiguredBy $_.ConfiguredBy $obj | Add-Member -type NoteProperty -Name IsOnPremGatewayRequired $_.IsOnPremGatewayRequired #Get last Refresh History $url="groups/$WorkspaceId/datasets/$Id/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $status =(&{if ($data.value.status -eq "Unknown") {"InProgress"} else {$data.value.status}}) if ($Errors.item($Id).length -gt 0) {$status = "Failed" } if ($Errors.item($Id).length -gt 0) { $startTime = $StartTimes.item($Id) $endTime =$startTime } else { if ($data.value.startTime) {$startTime = [datetime]$data.value.startTime} if ($data.value.EndTime){$endTime =[datetime]$data.value.EndTime} } $obj | Add-Member -type NoteProperty -Name refreshStatus $status $obj | Add-Member -type NoteProperty -Name refreshType $data.value.refreshType $obj | Add-Member -type NoteProperty -Name startTime $startTime $obj | Add-Member -type NoteProperty -Name EndTime $endTime $obj | Add-Member -type NoteProperty -Name Error ($Errors.item($Id) + $data.value.serviceExceptionJson ) $DataSetList+=$obj } } $DataSetList } function Invoke-pbiRefresh { param( [Parameter(Mandatory=$True)] [object]$Workspace , #Array of Workspaces [Parameter(Mandatory=$False)] [String]$Dataset="*", #Name of Dataset. Can by Array [parameter(Mandatory=$false)] [switch]$MailOnFailure=$False, #Email on Failure [parameter(Mandatory=$false)] [switch]$Wait=$True, #Wait for Failure (Synchronous) [parameter(Mandatory=$false)] [int]$WaitTime=10, #Time to wait for checking Processing Progress [parameter(Mandatory=$false)] [String]$SqlServer ,#Sql Server to Log Results. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlDatabaseName , #Sql Server to Log Results to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlSchemaName ="dbo" , #Sql Server to Log Schema to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlTableName ="ProcessView", #Sql Table to Log Results to [parameter(Mandatory=$false)] [switch]$IgnoreProcessError, #Raise Error if any Process Errors. Default is to Fail on any Error [parameter(Mandatory=$false)] [switch]$NoOutput , #Output Status When Finished [parameter(Mandatory=$false)] [int]$DOP =4, #Degree of Parrallelism [parameter(Mandatory=$false)] [bool]$RetryFailed = 0, #Retry failed only dataset flag [parameter(Mandatory=$false)] [int]$RetryAttempts = 1, #No of retry attempts [parameter(Mandatory=$false)] [int]$RetryWaitTime = 20 #Time to wait in Seconds ) $ErrorActionPreference = "Stop" $errormessage="" Check-pbiModules $Datasets =@() $RefreshList =@() $index =0 $i=0 $Errors =@{} $StartTimes =@{} [string]$status="" ForEach ($w in $Workspace) { $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" $sDatasets=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True } | Where-Object {$_.Name -NotIn "Usage Metrics Report"} forEach ($d in $sDatasets) { $AddDataset = $True If ($RetryFailed) { $status = (Get-pbiStatus -Workspace $w -Dataset $d.name) | Select -ExpandProperty "refreshStatus" If ($status -eq "Completed") {$AddDataset=$False} } if($AddDataset) { $record= New-Object System.Object $record | Add-Member -MemberType NoteProperty -Name "Index" -Value $i $record | Add-Member -MemberType NoteProperty -Name "DataSet" -Value $d.Name $record | Add-Member -MemberType NoteProperty -Name "DataSetId" -Value $d.Id $record | Add-Member -MemberType NoteProperty -Name "WorkSpace" -Value $w $record | Add-Member -MemberType NoteProperty -Name "WorkSpaceid" -Value $WorkspaceId $record | Add-Member -MemberType NoteProperty -Name "status" -Value "Running" $record | Add-Member -MemberType NoteProperty -Name "ProcessKey" -Value 0 $record | Add-Member -MemberType NoteProperty -Name "Error" -Value "" $record | Add-Member -MemberType NoteProperty -Name "startTime" -value $null $i+=1 $Datasets+=$record } } } $DatasetCount =$i $index=0 $Run=0 while ($index -lt $DataSetCount) { $Run+=1 $RefreshList =@() for ($i=1; $i -le $DOP; $i++) { if ($index -lt $DataSetCount ) { $w = $Datasets[$Index].WorkSpace $d = $Datasets[$Index].DataSet $status="" $ProcessKey= Invoke-pbiRefreshDataSet -Workspace $w -Dataset $d -SqlServer $SqlServer -SqlDatabaseName $SqlDatabaseName -SqlSchemaName $SqlSchemaName -NoWait $Datasets[$Index].ProcessKey=$ProcessKey if ($SqlServer ) { $Query ="select refreshStatus FROM {0}.{1}.{2} WHERE ProcessKey = {3}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $ProcessKey Write-Verbose $Query $status= (Invoke-sqlcmd -ServerInstance $SqlServer -Query $Query).refreshStatus $Datasets[$Index].status = $status $Datasets[$Index].startTime =Get-Date if ($status -eq "Failed" ) { $Query ="select Error FROM {0}.{1}.{2} WHERE ProcessKey = {3}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $ProcessKey $Datasets[$Index].Error = (Invoke-sqlcmd -ServerInstance $SqlServer -Query $Query).Error } } if ($status -ne "Failed") {$RefreshList+=$Datasets[$Index]} $index +=1 } } if (($Wait -eq$True) -and ($RefreshList.length -gt 0)) { Start-Sleep -Seconds 1 $Complete=$False While (!($Complete)) { ForEach ($r in $RefreshList | Where-Object {$_.status -ne "Completed" -and $_.status -ne "Failed" } ) { $WorkspaceId =$r.WorkSpaceId $Id=$r.DataSetId $DataSetIndex=$r.index $d=$Datasets[$DataSetIndex] if (!($Errors -contains $Id)) { $url="groups/$WorkspaceId/datasets/$Id/refreshes?" + '$top=1' try { $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json if ($data.value.EndTime){$EndTime = Get-Date -Date $data.value.EndTime -f "dd/MM/yyyy HH:mm:ss.fff"} [string]$status=$data.value.status if ($d.status -ne $status -and $status -ne "Failed") { $Query="UPDATE [{0}].[{1}].[{2}] SET refreshStatus='{4}' WHERE ProcessKey={3}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $r.ProcessKey, $status invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } if ($status -eq "Failed" -and $d.status -ne "Failed" ) { $Error =$data.value.serviceExceptionJson -replace "'","''" $Datasets[$DataSetIndex].Error=$Error $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime='{6}', refreshStatus='{3}' , Error='{4}' WHERE ProcessKey={5}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $status, $Error , $r.ProcessKey, $EndTime invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query write-Warning ("`r`nError in $($r.WorkSpace)\$($r.DataSet): $Error" ) } if ($status -eq "Completed" ) { $Error =$data.value.serviceExceptionJson -replace "'","''" $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime='{5}', refreshStatus='{3}' , Error=null WHERE ProcessKey={4}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $status, $r.ProcessKey, $EndTime invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } $Datasets[$DataSetIndex].status = $status } catch [System.AggregateException] { $Error =(Resolve-PowerBIError -Last ).Message $status ="Failed" $Datasets[$DataSetIndex].status=$status $Datasets[$DataSetIndex].Error += $Error if ($Errors.ContainsKey( $id)) {$Errors[$Id] =$Errors[$Id] + '.' + $Erro} else {$Errors.Add($Id,$Error) } $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='{3}' , Error='{4}' WHERE ProcessKey={5}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $status, $Error , $r.ProcessKey invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query write-Warning ("`r`nError in $($r.WorkSpace)\$($r.DataSet): $Error" ) } catch { $status ="Failed" $Datasets[$DataSetIndex].status=$status $Datasets[$DataSetIndex].Error += $Error if ($Errors.ContainsKey( $id)) {$Errors[$Id] =$Errors[$Id] + '.' + $Error} else {$Errors.Add($Id,$Error) } $Error =$Errors[$Id] $Error = $Error -replace "'","''" $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='{3}' , Error='{4}' WHERE ProcessKey={5}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $status, $Error , $r.ProcessKey invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query write-Warning ("`r`nError in $($r.WorkSpace)\$($r.DataSet): $Error" ) } } } #Check if all failed or Completed, otherwise Wait $Complete=$True $RefreshList | Where {$_.status -ne "Completed" -and $_.status -ne "Failed"} | ForEach-Object { $Complete=$False } if (!($Complete )) {Start-Sleep -Seconds $WaitTime} } } } #Retry to refresh Failed datasets $FailedDatasetsList = $Datasets | Where {$_.status -eq "Failed"} If ($RetryAttempts -gt 0 -and $FailedDatasetsList.Count -gt 0) { Write-Host ("Retrying Failed Datasets", $FailedDatasetsList.DataSet) Start-Sleep -Seconds $RetryWaitTime $RetryAttempts= $RetryAttempts-1 Invoke-pbiRefresh -Workspace $WorkSpace -Wait -SqlServer $SqlServer -SqlDatabaseName $SqlDatabaseName -SqlSchemaName "lineage" -SqlTableName "ProcessLog" -RetryAttempts $RetryAttempts -RetryFailed 1 } $ErrorActionPreference = "continue" ForEach ($d in $Datasets) { $checkStatus = (Get-pbiStatus -Workspace $d.Workspace -Dataset $d.DataSet) | Select -ExpandProperty "refreshStatus" If ($checkStatus -eq "Failed"){ if (!$Errors.ContainsKey( $d.DataSetId) ) {$Errors.Add($d.DataSetId, $d.Error)} $StartTimes.Add( $d.DataSetId, $d.startTime) if ($d.Error) {$failed=$True ; $LastError= $d.Error} } } if (!($NoOutput.IsPresent)) { Get-pbiStatus -Workspace $WorkSpace -Dataset $DataSet -Errors $Errors -StartTimes $StartTimes | ft } if ($failed) { EXIT 1 } } function Invoke-pbiRefreshDataSet { param( [Parameter(Mandatory=$True)] [object]$Workspace , #Workspace Name (Required) [Parameter(Mandatory=$False)] [String]$Dataset, #Name of Dataset. [Parameter(Mandatory=$False)] [String]$DatasetId, #Id of DataSet [parameter(Mandatory=$false)] [switch]$MailOnFailure=$False, #Email on Failure [parameter(Mandatory=$false)] [switch]$NoWait, #Wait for Failure (Synchronous) [parameter(Mandatory=$false)] [Int16]$WaitTime=5, #Secs to Wait before status Refresh [parameter(Mandatory=$false)] [String]$SqlServer , #Sql Server to Log Results. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlDatabaseName , #Sql Server to Log Results to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlSchemaName ="lineage" , #Sql Server to Log Schema to. Will Log if this is present [parameter(Mandatory=$false)] [String]$SqlTableName ="ProcessLog" #Sql Table to Log Results to ) $ErrorActionPreference = "Stop" $failed=$False $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" if (-Not ($DataSetId)) { $d=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True } $DataSetId =$d.Id } else { $d=Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $DataSetId } $url="groups/$WorkspaceId/datasets/$DataSetId/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $Id =$d.Id $DatasetName=$d.Name $IsOnPremGatewayRequired =$d.IsOnPremGatewayRequired if ($data.Value.Status -ne "Unknown") { if ($MailOnFailure.IsPresent) {$notifyOption="MailOnFailure"} else { $notifyOption="NoNotification" } $Body='{"notifyOption": "' + $notifyOption+ '"}' $url="groups/$WorkspaceId/datasets/$Id/refreshes" Write-Host ("Refreshing {0}/{1}" -f $Workspace , $DatasetName) try { if ($SqlServer) { $Query="INSERT INTO [{0}].[{1}].[{2}] (Workspace,DataSetId,DataSet, IsOnPremGatewayRequired, refreshStatus, refreshType, startTime) VALUES ('{3}','{4}','{5}','{6}','{7}','{8}', getdate()); SELECT SCOPE_IDENTITY()" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $Workspace, $id,$DatasetName, $IsOnPremGatewayRequired, "Started","ViaApi" Write-Verbose $Query $Key= (invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query)[0] } $res=Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body if ($SqlServer) { $Query="UPDATE [{0}].[{1}].[{2}] SET refreshStatus='Started' WHERE ProcessKey={3}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $key invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } } catch [AggregateException] { $failed=$True $error=$_.Exception.innerexception.message if ($error.Contains("400 (Bad Request)")) {$error ="Refresh Limit Reached, or API Failure. (Bad Request)" } write-Warning ("`r`nRefresh $w\$DatasetName. $error " ) if ($SqlServer) { $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='Failed', Error = '{3}' WHERE ProcessKey={4}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $error, $key Write-Verbose $Query invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } } catch { $failed=$True write-Warning ("`r`nRefresh $w\$DatasetName. $_.Exception.message" ) Write-Verbose -Message $_.Exception $Error = $_.Exception -replace "'","''" if ($SqlServer) { $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='Failed', Error = '{3}' WHERE ProcessKey={4}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $Error, $key invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } } } elseif($data.Value.Status -eq "Unknown"){ if ($SqlServer) { $Query="INSERT INTO [{0}].[{1}].[{2}] (Workspace,DataSetId,DataSet, IsOnPremGatewayRequired, refreshStatus, refreshType, startTime,endTime) VALUES ('{3}','{4}','{5}','{6}','{7}','{8}', getdate(),getdate()); SELECT SCOPE_IDENTITY()" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $Workspace, $id,$DatasetName, $IsOnPremGatewayRequired, "Unknown","ViaApi" Write-Verbose $Query $Key= (invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query)[0] } } if (!($NoWait.IsPresent )) { $startTime=get-date $url="groups/$WorkspaceId/datasets/$Id/refreshes?" + '$top=1' While (!($Complete)) { $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $status=$data.value.status if ($status -eq "Failed" -or $status -eq "Completed") {$Complete =$True} else {Start-Sleep -Seconds $WaitTime} } if ($status -ne "Completed") { Write-Warning $data.value.serviceExceptionJson $Error =$data.value.serviceExceptionJson -replace "'","''" } $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='{3}' , Error='{4}' WHERE ProcessKey={5}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $status, $Error , $key invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query if ($status -ne "Completed") { write-Warning ("`r`nRefresh $w\$DatasetName. $Error" ) return $Key } } else { return $Key } } function Set-pbiSqlServer { param( [Parameter(Mandatory=$True)]$Workspace , #Single Workspace [Parameter(Mandatory=$False)]$Name="*", #Name of Dataset. Can by Array [Parameter(Mandatory=$True)]$SqlServer="", #New SqlServer Name [Parameter(Mandatory=$False)]$UserName="" #Username of validated user. If this is set then updaet will TakeOver Dataset if required ) $ErrorActionPreference = "Stop" $WorkspaceId=(Get-PowerBIWorkspace -Name $Workspace) | Select -ExpandProperty "Id" Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Name} |foreach { $ConfiguredBy=$_.ConfiguredBy $Id =$_.Id $Dataset = $_.Name $Url = "groups/$WorkspaceId/datasets/$Id/datasources" $ds=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $ds $datasourceType=$data.value.datasourceType $oldServer =$data.value.connectionDetails.server $Database =$data.value.connectionDetails.database if ($Username) { if ($ConfiguredBy -ne $UserName) { Set-pbiDatasetTakeOver -Workspace $Workspace -Dataset $Dataset $ConfiguredBy=(Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $Id) | Select -ExpandProperty "ConfiguredBy" if ($ConfiguredBy -ne $UserName) { Write-Error "Cannot Updatedatasource. Owner=$ConfiguredBy,Current UserName=$UserName" } } } if ($datasourceType -eq "sql") { $Url = "groups/$WorkspaceId/datasets/$Id/updatedatasources" $Body=@" {"updateDetails":[ {"connectionDetails": { "server": "$SqlServer", "database": "$Database" }, "datasourceSelector": { "datasourceType": "Sql", "connectionDetails": { "server": "$OldServer", "database": "$Database" } } } ] } "@ Write-Verbose -Message $Url Write-Verbose -Message $Body Write-Host "Updating Datasource for $Workspace\$Dataset. Server $oldServer=>$SqlServer..." try { Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body } catch [System.Net.Http.HttpRequestException],[AggregateException] { write-Error ("Update for $Workspace\$Dataset.PBIX Failed. Check PowerQuery has no MERGE operations. See https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources") write-Error (Resolve-PowerBIError -Last | Out-String) } catch { write-Error ( $_.Exception) } } else { Write-Host "datasourceType=$datasourceType. Not Updated as not Sql" } #Set PBI Gateway (not Required as it now Auto Sets) Set-pbiDatasetGateway -Workspace $Workspace -Dataset $Dataset } } function Load-Module ($m) { # If module is imported say that and do nothing if (Get-Module | Where-Object {$_.Name -eq $m}) { return } else { # If module is not imported, but available on disk then import if (Get-Module -ListAvailable | Where-Object {$_.Name -eq $m}) { Import-Module $m } else { # If module is not imported, not available on disk, but is in online gallery then install and import if (Find-Module -Name $m | Where-Object {$_.Name -eq $m}) { Install-Module -Name $m -Force -Verbose -Scope CurrentUser -AllowClobber Import-Module $m } else { # If module is not imported, not available and not in online gallery then abort write-host "Module $m not imported, not available and not in online gallery, exiting." Exit 1 } } } } function Import-pbiAuditLogs { <# .SYNOPSIS Import PowerBI Audit Logs from Office 365 .DESCRIPTION Import PowerBI Audit Logs from Office 365. Used in conjunction with PBI Usage Metrics.PBIX Requires Global Admin Account and MSOnline POSH Module. This data is used to determine who has a valid PowerBI License so we can reporton % usage .PARAMETER Credential Powershell Credential. Must be Office 365 Global Admin. .PARAMETER SqlInstance SQL Server instance to Import to (default localhost) .PARAMETER Database SQL Database for Import .PARAMETER Schema SQL Schema. Default to dbo .PARAMETER Table SQL Table. Default to AuditLogs .PARAMETER SqlUsername SQL Username. If this is null will use windows auth for SQL .PARAMETER SqlPassword SQL Password. can be blank if using windows auth for SQL .EXAMPLE $username = "globaladmin@domain.ie" $password = "Plain Text Password" Import-pbiMsolUser -Username $username -password (ConvertTo-SecureString $password -AsPlainText -Force) -SqlInstance "AGDEV" -schema "dbo" -Database "TempDB" #> param( [parameter(Mandatory=$true)] [string]$Username, [parameter(Mandatory=$true)] [SecureString]$Password, [parameter(Mandatory=$false)] [string]$SqlInstance='localhost', [parameter(Mandatory=$false)] [string]$Database='TempDB', [parameter(Mandatory=$false)] [String]$Schema='dbo', [parameter(Mandatory=$false)] [String]$Table='AuditLog', [parameter(Mandatory=$false)] [String]$SqlUsername, [parameter(Mandatory=$false)] [String]$SqlPassword, [parameter(Mandatory=$false)] [Int]$ResultSize =5000 ) $ErrorActionPreference = "Stop" $Session= "" $Credential =New-Object System.Management.Automation.PSCredential ($Username, $Password) #Check if Module Loaded if (!(Get-Module -ListAvailable -Name "MSOnline")) { Write-Error "MSOnline Module Required. Run Install-Module MSOnline (as Administrator)" Exit 1 } if (!(Get-Module -ListAvailable -Name "SqlServer")) { Write-Error "MSOnline Module Required. Run Install-Module SqlServer (as Administrator)" Exit 1 } try { Write-Host ( "{0} Start Import-pbiAuditLogs" -f (get-date) ) #Get Watermark Value $SqlCmd="IF OBJECT_ID('[{0}].[{1}]') is not null SELECT MAX(CreationTime) as CreationTime FROM [{0}].[{1}]" -f $Schema, $Table Write-Verbose -Message $SqlCmd if ($SqlUsername) { $startDate=(Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd -Username $sqlUsername -Password $SqlPassword) } else { $startDate=(Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd) } if ($startDate) {[DateTime]$startDate=$startDate[0]; $startDate =$startDate.AddSeconds(1) } else { [DateTime]$startDate =(get-date).AddDays(-90) } $endDate=(get-date) Write-Host ( "{0} Start Import-pbiAuditLogs date range {1} to {2} " -f (get-date), $startDate, $endDate) Write-Verbose "Connecting to office365.com for AuditLogs. Please wait..." $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri 'https://outlook.office365.com/powershell-liveid/' -Credential $Credential -Authentication Basic -AllowRedirection Import-PSSession $Session -DisableNameChecking -AllowClobber > $null #Get-PSSession | Remove-PSSession $scriptStart=(get-date) $sessionName = (get-date -Format 'u')+'pbiAuditLog' $i = 0 $currentResults = @() $rc Do { $currentResults=Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize $ResultSize -RecordType PowerBI | ` select-object -ExpandProperty AuditData | ForEach-Object {ConvertFrom-Json $_} | Select-Object -Property Id, RecordType, CreationTime, Operation, OrganizationId, UserType, UserId, userAgent, Activity, ConsumptionMethod, ItemName, WorkSpaceName, DashboardName, DatasetName, ReportName, WorkspaceId , ObjectId, DashboardId, DatasetId, ReportId if ($currentResults) { $aggregateResults += $currentResults if ($currentResults -is [array]) {$rc=$currentResults.Count } else {$rc=1} Write-Host ("Search-UnifiedAuditLog: Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $rc, $user.UserPrincipalName ) if ($currentResults.Count -lt $ResultSize) {$currentResults = @()} else {$i++} } else {$currentResults = @() } } Until ($currentResults.Count -eq 0) #Sort by CreationTime (for re-entrant behaviour) and then Insert to SQL if (!($aggregateResults)) { Write-Host ("{0} No new AuditLog Data" -f (Get-Date)) }else { Write-Host ("{0} Sorting and Write-SqlTableData to {1}" -f (Get-Date), $SqlInstance ) if ($SqlUsername) { $Credential =New-Object System.Management.Automation.PSCredential ($SqlUsername, (ConvertTo-SecureString $SqlPassword -AsPlainText -Force)) $aggregateResults | Sort-Object -Property CreationTime , Id | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $Database -TableName $Table -SchemaName $Schema -Force -Credential $Credential } else { Write-SqlTableData -InputData $aggregateResults -ServerInstance $SqlInstance -DatabaseName $Database -TableName $Table -SchemaName $Schema -Force } } } catch { Write-Host $_.Exception.Message -ForegroundColor Red Write-Host $_.Exception.ErrorRecord.ScriptStackTrace -ForegroundColor Red Exit 1 } finally { #Cleanup. max Three Sessions allowed as per https://4sysops.com/forums/topic/office-365-you-have-exceeded-the-maximum-number-of-connections-allowed-3/ if ($Session) { Remove-PSSession -Id $Session.Id } Write-Host ( "{0} End Import-pbiAuditLogs" -f (get-date) ) } } function Import-pbiMsolUser { <# .SYNOPSIS Import User and License Data from O365. .DESCRIPTION Import User and License Data from O365.. Requires Global Admin Account and MSOnline POSH Module. This data is used to determine who has a valid PowerBI License so we can reporton % usage .PARAMETER Credential Powershell Credential. Must be Office 365 Global Admin. .PARAMETER SqlInstance SQL Server instance to Import to (default localhost) .PARAMETER Database SQL Database for Import .PARAMETER Schema SQL Schema. Default to dbo .PARAMETER Table SQL Table. Default to MsolUser .PARAMETER SqlUsername SQL Username. If this is null will use windows auth for SQL .PARAMETER SqlPassword SQL Password. can be blank if using windows auth for SQL .EXAMPLE $username = "globaladmin@domain.ie" $password = "Password" $secpasswd = ConvertTo-SecureString $password -AsPlainText -Force $Credential = New-Object System.Management.Automation.PSCredential ($username, $secpasswd) $Credential #> param( [parameter(Mandatory=$true)] [string]$Username, [parameter(Mandatory=$true)] [SecureString]$Password, [parameter(Mandatory=$false)] [string]$SqlInstance='localhost', [parameter(Mandatory=$false)] [string]$Database='TempDB', [parameter(Mandatory=$false)] [String]$Schema='dbo', [parameter(Mandatory=$false)] [String]$Table='MsolUser', [parameter(Mandatory=$false)] [String]$SqlUsername, [parameter(Mandatory=$false)] [String]$SqlPassword ) $Credential =New-Object System.Management.Automation.PSCredential ($Username, $Password) #Check if Module Loaded if (!(Get-Module -ListAvailable -Name "MSOnline")) { Write-Error "MSOnline Module Required. Run Install-Module MSOnline (as Administrator)" Exit 1 } if (!(Get-Module -ListAvailable -Name "SqlServer")) { Write-Error "MSOnline Module Required. Run Install-Module SqlServer (as Administrator)" Exit 1 } Connect-MsolService -Credential $Credential $SqlCmd="IF OBJECT_ID('[{0}].[{1}]') is not null DROP TABLE [{0}].[{1}]" -f $Schema, $Table Write-Verbose -Message $SqlCmd if ($SqlUsername) { Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd -Username $sqlusername -Password $SqlPassword } else { Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd } Get-MsolUser | Where-Object {($_.isLicensed -eq $true ) } | Select-Object -Property DisplayName, City, Country, Department, @{ name = "licenses"; expression = {ConvertTo-Json $_.licenses.accountskuid}}, ObjectId, State, UsageLocation, UserPrincipalName, @{ name="UserType"; expression = { [string]$_.UserType}}, @{ name="ValidationStatus"; expression= {[string]$_.ValidationStatus }}, WhenCreated ` | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $Database -TableName $Table -SchemaName $Schema -Force } function Import-pbiAuditLogsRestApi { <# .SYNOPSIS Import PowerBI Audit Logs using Rest APIs .DESCRIPTION Import PowerBI Audit Logs using PBI rest apis. Used in conjunction with PBI Usage Metrics.PBIX Requires Global Admin Account and MSOnline POSH Module. This data is used to determine who has a valid PowerBI License so we can reporton % usage .PARAMETER Credential Powershell Credential. Must be Office 365 Global Admin. .PARAMETER SqlInstance SQL Server instance to Import to (default localhost) .PARAMETER Database SQL Database for Import .PARAMETER Schema SQL Schema. Default to dbo .PARAMETER Table SQL Table. Default to AuditLogs .PARAMETER SqlUsername SQL Username. If this is null will use windows auth for SQL .PARAMETER SqlPassword SQL Password. can be blank if using windows auth for SQL .EXAMPLE $username = "globaladmin@domain.ie" $password = "Plain Text Password" Import-pbiAuditLogsRestApi -Username $username -password (ConvertTo-SecureString $password -AsPlainText -Force) -SqlInstance "AGDEV" -schema "dbo" -Database "TempDB" #> param( [parameter(Mandatory=$true)] [string]$Username, [parameter(Mandatory=$true)] [SecureString]$Password, [parameter(Mandatory=$false)] [string]$SqlInstance='localhost', [parameter(Mandatory=$false)] [string]$Database='TempDB', [parameter(Mandatory=$false)] [String]$Schema='dbo', [parameter(Mandatory=$false)] [String]$Table='AuditLog', [parameter(Mandatory=$false)] [String]$SqlUsername, [parameter(Mandatory=$false)] [String]$SqlPassword ) $ErrorActionPreference = "Stop" $Session= "" $aggregateResults = @() $Credential =New-Object System.Management.Automation.PSCredential ($Username, $Password) Login-PowerBI -Credential $Credential #Check if Module Loaded if (!(Get-Module -ListAvailable -Name "MSOnline")) { Write-Error "MSOnline Module Required. Run Install-Module MSOnline (as Administrator)" Exit 1 } if (!(Get-Module -ListAvailable -Name "SqlServer")) { Write-Error "MSOnline Module Required. Run Install-Module SqlServer (as Administrator)" Exit 1 } try { Write-Host ( "{0} Start Import-pbiAuditLogs" -f (get-date) ) #$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri 'https://outlook.office365.com/powershell-liveid/' -Credential $Credential -Authentication Basic -AllowRedirection #Import-PSSession $Session -DisableNameChecking -AllowClobber > $null #Get-PSSession | Remove-PSSession #Get Watermark Value $SqlCmd="IF OBJECT_ID('[{0}].[{1}]') is not null SELECT MAX(CreationTime) as CreationTime FROM [{0}].[{1}]" -f $Schema, $Table Write-Verbose -Message $SqlCmd if ($SqlUsername) { $startDate=(Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd -Username $sqlUsername -Password $SqlPassword) } else { $startDate=(Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query $SqlCmd) } if ($startDate) {[DateTime]$startDate=$startDate[0]; $startDate =$startDate.AddSeconds(1) } else { [DateTime]$startDate =(get-date).AddDays(-30) } $endDate=(get-date) Write-Host ( "{0} Start Import-pbiAuditLogs date range {1} to {2} " -f (get-date), $startDate, $endDate) $sessionName = (get-date -Format 'u')+'pbiAuditLog' #Download Data while ($startDate -le $endDate) { $StartDateTime = (Get-Date -Date ($startDate) -Format yyyy-MM-ddTHH:mm:ss) $EndDateTime = (Get-Date -Date ((($startDate.Date).AddDays(1)).AddSeconds(-1)) -Format yyyy-MM-ddTHH:mm:ss) Write-Host ( "{0} Downloading data from {1} to {2} " -f (get-date), $StartDateTime, $EndDateTIme) $aggregateResults += Get-PowerBIActivityEvent -StartDateTime $StartDateTime -EndDateTime $EndDateTime -ResultType JsonString | ForEach-Object {ConvertFrom-Json $_} | Select-Object -ExpandProperty Syncroot | Select-Object -Property Id, RecordType, CreationTime, Operation, OrganizationId, UserType, UserId, userAgent, Activity, ConsumptionMethod, ItemName, WorkSpaceName, DashboardName, DatasetName, ReportName, WorkspaceId , ObjectId, DashboardId, DatasetId, ReportId $startDate = $startDate.Date.AddDays(1) } #Sort by CreationTime (for re-entrant behaviour) and then Insert to SQL if (!($aggregateResults)) { Write-Host ("{0} No new AuditLog Data" -f (Get-Date)) }else { Write-Host ("{0} Sorting and Write-SqlTableData to {1}" -f (Get-Date), $SqlInstance ) if ($SqlUsername) { $Credential =New-Object System.Management.Automation.PSCredential ($SqlUsername, (ConvertTo-SecureString $SqlPassword -AsPlainText -Force)) $aggregateResults | Sort-Object -Property CreationTime , Id | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $Database -TableName $Table -SchemaName $Schema -Force -Credential $Credential } else { Write-SqlTableData -InputData $aggregateResults -ServerInstance $SqlInstance -DatabaseName $Database -TableName $Table -SchemaName $Schema -Force } } } catch { Write-Host $_.Exception.Message -ForegroundColor Red Write-Host $_.Exception.ErrorRecord.ScriptStackTrace -ForegroundColor Red Exit 1 } finally { #Cleanup. max Three Sessions allowed as per https://4sysops.com/forums/topic/office-365-you-have-exceeded-the-maximum-number-of-connections-allowed-3/ #if ($Session) { Remove-PSSession -Id $Session.Id } Write-Host ( "{0} End Import-pbiAuditLogs" -f (get-date) ) } } function Remove-pbiBackupBlob { Param( [parameter(Mandatory=$true)] [string]$StorageAccountName, [parameter(Mandatory=$true)] [string]$SAS, [parameter(Mandatory=$false)] [object]$Containers, [parameter(Mandatory=$true)] [int]$Cleanup =7 ) $ctx=New-AzStorageContext -StorageAccountName $StorageAccountName -SasToken $SAS -ErrorAction Stop Write-Host $("Connecting to {0}" -f $StorageAccountName) foreach ($container in $Containers) { $blobs =Get-AzStorageBlob -Context $ctx -Container $Containers | Where-Object {$_.Name -like "*.pbix"} if ($blobs ){ foreach ($blob in $blobs) { $LastModified=$blob.LastModified $DaysOld = ([DateTime]::Now.Date - [DateTime]$LastModified.Date) if ($DaysOld.Days -gt $Cleanup){ Write-Host $("Deleting the blob file {0}" -f $blob.Name) Remove-AzStorageBlob -Context $ctx -Container $container -Blob $blob.Name } } } } } function Download-pbiReport { param( [Parameter(Mandatory=$False)] [object]$Workspace ="" , #Array of Workspaces [Parameter(Mandatory=$False)] [String]$Report="*", #Name of Dataset. Can by Array [Parameter(Mandatory=$False)] [String]$Path="C:\temp\PowerBI", #Path to Export to. Can be a Temp Path if [parameter(Mandatory=$false)] [string]$StorageAccountName="", #Azure StorageAccountName for Upload [parameter(Mandatory=$false)] [string]$Container="powerbi", #Azure $Container eg "PowerBI" [parameter(Mandatory=$false)] [string]$SAS="" #SAS Token for upload ) $ErrorActionPreference = "Stop" if (-Not $workspace) { [System.Collections.ArrayList]$workspace=Get-PowerBIWorkspace | select-Object Name } if ($SAS) { if ((Get-Command "AzCopy.exe" -ErrorAction SilentlyContinue) -eq $null) { Write-Error "Unable to find AzCopy in your PATH. Please download to C:\Windows or in Path" } } New-Item -ItemType Directory -Force -Path $Path | Out-Null Remove-Item -Path $Path -Force -Recurse -Filter "*.pbix" ForEach ($w in $Workspace) { $WorkspaceId=Get-PowerBIWorkspace -Name $w | Select -ExpandProperty "Id" if (-Not $WorkspaceId) { write-Error "Workspace '$w' Not Found. Check Workspaces and Permissions"} Get-PowerBIReport -WorkspaceId $WorkspaceId | Where {$_.Name -like $Report} | ForEach-Object { $ds=Get-PowerBIDataset -Id $_.DataSetId -WorkspaceId $WorkspaceId if ($ds.IsEffectiveIdentityRequired -eq $True) { $pbix=$_.Name + "_"+ (Get-Date -Format "yyyyMMdd") + ".pbix" $file=Join-Path -Path $Path -ChildPath $w New-Item -ItemType Directory -Force -Path $file | Out-Null $file=Join-Path -Path $file -ChildPath ${pbix} if (Test-Path $file) { Remove-Item $file } "Downloading {0}" -f $file $Id =$_.Id $Url = "groups/$WorkspaceId/reports/$Id/Export" Invoke-PowerBIRestMethod -Url $Url -Method get -OutFile $file #Export-PowerBIReport -Id $_.Id -OutFile $file } } } if ($SAS) { $uri = "https://${StorageAccountName}.blob.core.windows.net/${Container}?$SAS" AzCopy cp $Path $uri --recursive=true } } export-modulemember -function New-pbiReport export-modulemember -function Remove-pbiReport export-modulemember -function Set-pbiRebindReport export-modulemember -function Set-pbiDatasetTakeOver export-modulemember -function Set-pbiDatasetGateway export-modulemember -function Get-pbiStatus export-modulemember -function Invoke-pbiRefresh export-modulemember -function Invoke-pbiRefreshDataSet export-modulemember -function Set-pbiSqlServer export-modulemember -function Import-pbiAuditLogs export-modulemember -function Import-pbiMsolUser export-modulemember -function Import-pbiAuditLogsRestApi export-modulemember -function Download-pbiReport export-modulemember -function Remove-pbiBackupBlob |