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 ) Get-PowerBIReport -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "reports/$Id" Invoke-PowerBIRestMethod -Url $Url -Method Delete } Get-PowerBIDataSet -Workspace (Get-PowerBIWorkspace -Name $Workspace) | foreach { $Id =$_.Id $Url = "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 ="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 ="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 ="datasets/$Id/DiscoverGateways" $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $GatewayId=$data.value.Id $Body=@" {"gatewayObjectId": "$GatewayId"} "@ $url="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} |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="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=$False, #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 ) $ErrorActionPreference = "Stop" $errormessage="" Check-pbiModules $Datasets =@() $Errors =@{} $ProcessKeys =@{} $StartTimes =@{} $Failed =$false ForEach ($w in $Workspace) { $wDatasets=@() $WorkspaceId=(Get-PowerBIWorkspace -Name $w) | Select -ExpandProperty "Id" $wDatasets=Get-PowerBIDataset -WorkspaceId $WorkspaceId | Where {$_.Name -like $Dataset -and $_.IsRefreshable -eq $True } [Int]$percentComplete=0 [Int]$maxPercent=0 $DatasetCount=$wDatasets.Count $progressTime =$false #Het Ladt Refresh Timing for Progress Bar forEach ($d in $wDatasets) { $id=$d.Id $url="datasets/$Id/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json if (($data.value)) { $progressTime =$false $maxPercent=$DatasetCount Break } else { if ($data.value.endTime) {$maxPercent+= (NEW-TIMESPAN –Start $data.value.startTime –End $data.value.endTime ).Seconds} if ($maxPercent -gt 0) {$progressTime=$true} } } if ($maxPercent -eq 0) {$progressTime=$false; $maxPercent=$DatasetCount} forEach ($d in $wDatasets) { $Id =$d.Id $Datasets+=$Id $DatasetName=$d.Name $IsOnPremGatewayRequired =$d.IsOnPremGatewayRequired if ($MailOnFailure.IsPresent) {$notifyOption="MailOnFailure"} else { $notifyOption="NoNotification" } $Body='{"notifyOption": "' + $notifyOption+ '"}' $url="datasets/$Id/refreshes" 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, "Starting","ViaApi" Write-Verbose $Query $Key= (invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query)[0] $ProcessKeys.Add($id,$key) } Write-Progress -Activity "PBI Refresh" -CurrentOperation "$DatasetName" -Status "Starting" $res=Invoke-PowerBIRestMethod -Url $Url -Method Post -Body $Body if ($SqlServer) { $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='InProgress' 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("Bad Request")) {$error ="Refresh Limit Reached" } write-Warning ("`r`nRefresh $w\$DatasetName. $error " ) Write-Verbose -Message $_.Exception $Errors.Add($id,$error) $StartTimes.Add($id,(Get-Date)) 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 } continue } catch { $failed=$True write-Warning ("`r`nRefresh $w\$DatasetName. $_.Exception.message" ) Write-Verbose -Message $_.Exception $Errors.Add($id,$_.Exception ) $StartTimes.Add($id,(Get-Date)) if ($SqlServer) { $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='Failed' WHERE ProcessKey={2}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $_.Exception, $key invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } continue } } #Synchrous Processing if Required. By Default is Asyncronous $startTime=get-date if ($Wait.IsPresent) { While (!($Complete)) { $InProgress=$false $i=0 $errorCount=$Errors.Count $lastError="" $errorDataset="" ForEach ($d in $wDataSets) { $id=$d.Id if ($Errors.Contains( $id)) { $i+=1 $lastError=$Errors[$id] $errorDataset=$d.Name + ":" } else { $url="datasets/$Id/refreshes?" + '$top=1' $json=Invoke-PowerBIRestMethod -Url $Url -Method Get $data = ConvertFrom-Json $json $status=$data.value.status if ($status -eq "Failed" -or $status -eq "Completed") {$i+=1 } if ($status -eq "Unknown") {$InProgress=$true } if ($status -eq "InProgress") {$InProgress =$true } if ($status -eq "Failed") {$failed=$true ; $errorCount +=1; $lastError =$data.value.serviceExceptionJson; $errorDataset=$d.Name + ":"} } } $seconds= (new-timespan $startTime (get-date) ).TotalSeconds if ($progressTime) {$percentComplete = $seconds / $maxPercent* 100 } else { $percentComplete = $i/$maxPercent *100} Write-Progress -activity ("PBI Refresh. {0}/{1} Complete. {2} Errors." -f $i, $DatasetCount , $errorCount) -status ("In Progress. $errorDataset$lastError") -PercentComplete $percentComplete if ($i -lt$DatasetCount) { $InProgress = $true; } else {$InProgress =$false ;$Complete=$true } if (!($Complete)) {Start-Sleep -Seconds $WaitTime} } } } #Log Results to Table if ((($SqlServer) -and ($SqlDatabaseName)) -or (!($NoOutput ))) { $status=Get-pbiStatus -Workspace $Workspace -Dataset $Dataset -Errors $Errors -StartTimes $StartTimes } if (!($NoOutput )) { $Status | Select } if (($SqlServer) -and ($SqlDatabaseName)) { Write-Verbose -Message "Logging to $SqlServer.$SqlDatabaseName.$SqlSchemaName.$SqlTableName" $Status | Select-Object -Property * | ForEach-Object { $key=$ProcessKeys[$_.DataSetId] if ($SqlServer -and $_.refresshStatus -ne "Failed") { $Query="UPDATE [{0}].[{1}].[{2}] SET EndTime=getdate(), refreshStatus='{3}' , Error='{4}' WHERE ProcessKey={5}" -f $SqlDatabaseName,$SqlSchemaName, $sqlTableName, $_.refreshStatus, $_.Error , $key invoke-sqlcmd -ServerInstance $SqlServer -Database $SqlDatabaseName -Query $Query } } $Status | ForEach-Object { if (!($_.Error)) { $s="Workspace: " + $_.WorkSpace + ",Dataset:" + $_.DataSet + ",Error:" + $_.Error if ($_.refreshStatus -eq "Failed") { Write-Error -Message $s } } } } if ($failed) { EXIT 1 } } 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 = "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 = "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 } 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 Set-pbiSqlServer export-modulemember -function Import-pbiAuditLogs export-modulemember -function Import-pbiMsolUser |