We have written a PowerShell script for extracting audit log data for Power BI Pro users. Previously it was working fine, from one month onwards, we are getting access dined error while running script. We are using correct credentials and also tried a non-MFA account, the same issue coming. Error message as below:
New-PSSession : [outlook.office365.com] Connecting to remote server outlook.office365.com failed with the following error message: Access is denied. For more information, see the about_Remote_Troubleshooting Help topic. At E:\location\Folder\Auditreport.ps1:66 char:12
$Session = New-PSSession –ConfigurationName Microsoft.Exchange -Conne ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CategoryInfo : OpenError: (System.Manageme....RemoteRunspace:RemoteRunspace) [New-PSSession], PSRemotingTransportException FullyQualifiedErrorId : AccessDenied,PSSessionOpenFailed Import-PSSession : Cannot validate argument on parameter 'Session'. The argument is null. Provide a valid value for the argument, and then try running the command again. At E:\location\Folder\Auditreport.ps1:70 char:18 Import-PSSession $Session -DisableNameChecking -AllowClobber
Powershell Script
cls Set-ExecutionPolicy RemoteSigned [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent Add-Type -Path (Resolve-Path "$PSScriptRoot\Microsoft.SharePoint.Client.dll") Add-Type -Path (Resolve-Path "$PSScriptRoot\Microsoft.SharePoint.Client.Runtime.dll") # getting data from config file $Data = [Xml] (Get-Content $PSScriptRoot\Config.xml) $PowerBIUser=$Data.data.record |where {$_.key -eq "PowerBIUser"} | select value $PowerBIUser=$PowerBIUser.value $SecurePasswordpath=$Data.data.record |where {$_.key -eq "Securepasswordpath"} | select value $SecurePasswordpath=$PSScriptRoot+"\"+$SecurePasswordpath.value $AppId=$Data.data.record |where {$_.key -eq "AppId"} | select value $AppId=$AppId.value $AppSecret=$Data.data.record |where {$_.key -eq "AppSecret"} | select value $AppSecret=$AppSecret.value $SourceUrl=$Data.data.record |where {$_.key -eq "SourceUrl"} | select value $SourceUrl=$SourceUrl.value $SourceListProUsers=$Data.data.record |where {$_.key -eq "SourceListProUsers"} | select value $SourceListProUsers=$SourceListProUsers.value $securePassword = Get-Content $SecurePasswordpath | ConvertTo-SecureString $UserCredential = New-Object System.Management.Automation.PSCredential($PowerBIUser, $securePassword) Connect-PnPOnline -Url $SourceUrl -ClientId $AppId -ClientSecret $AppSecret $startDate=(get-date).AddDays(-90) $endDate=(get-date) $scriptStart=(get-date) #Establish session to exchange online and import unified audit log commandlets required for security and compliance audit log search $Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection #Import-PSSession -Session $Session | Out-Null Import-PSSession $Session -DisableNameChecking -AllowClobber $ItemsPBIProUsers = Get-PnPListItem -List PBIProUsers If($ItemsPBIProUsers) { $ItemsPBIProUsers | ForEach { $UserEmail=$_['UserPrincipalName']; #write-host $UserEmail $sessionName = (get-date -Format 'u')+'pbiauditlog' # Reset user audit accumulator $aggregateResults = @() $i = 0 # Loop counter Do { #$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBI #$UserEmail="[email protected]"; $currentResults = Search-UnifiedAuditLog -UserIds $UserEmail -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBI Write-Host( $UserEmail + "-" + $currentResults.Count); if ($currentResults.Count -gt 0) { #Write-Host( $UserEmail + "-" + $currentResults.Count); #Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName ) # Accumulate the data $aggregateResults += $currentResults # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user if ($currentResults.Count -lt 1000) { $currentResults = @() } else { $i++ } } } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- # try { $aggregateResults= $aggregateResults | Sort CreationDate -Descending if ($aggregateResults.Count -gt 0) { $CreationDate=$aggregateResults[0].CreationDate.Date $_['LastActivityDate'] = $CreationDate $_.SystemUpdate() Invoke-PnPQuery } } catch { $ErrorMessage = $_.Exception.Message write-host $ErrorMessage } } } <# $data=@() foreach ($auditlogitem in $aggregateResults) { $datum = New-Object –TypeName PSObject $d=convertfrom-json $auditlogitem.AuditData $datum | Add-Member –MemberType NoteProperty –Name Id –Value $d.Id $datum | Add-Member –MemberType NoteProperty –Name CreationTime –Value $auditlogitem.CreationDate $datum | Add-Member –MemberType NoteProperty –Name CreationTimeUTC –Value $d.CreationTime $datum | Add-Member –MemberType NoteProperty –Name RecordType –Value $d.RecordType $datum | Add-Member –MemberType NoteProperty –Name Operation –Value $d.Operation $datum | Add-Member –MemberType NoteProperty –Name OrganizationId –Value $d.OrganizationId $datum | Add-Member –MemberType NoteProperty –Name UserType –Value $d.UserType $datum | Add-Member –MemberType NoteProperty –Name UserKey –Value $d.UserKey $datum | Add-Member –MemberType NoteProperty –Name Workload –Value $d.Workload $datum | Add-Member –MemberType NoteProperty –Name UserId –Value $d.UserId $datum | Add-Member –MemberType NoteProperty –Name ClientIP –Value $d.ClientIP $datum | Add-Member –MemberType NoteProperty –Name UserAgent –Value $d.UserAgent $datum | Add-Member –MemberType NoteProperty –Name Activity –Value $d.Activity $datum | Add-Member –MemberType NoteProperty –Name ItemName –Value $d.ItemName $datum | Add-Member –MemberType NoteProperty –Name WorkSpaceName –Value $d.WorkSpaceName $datum | Add-Member –MemberType NoteProperty –Name DashboardName –Value $d.DashboardName $datum | Add-Member –MemberType NoteProperty –Name DatasetName –Value $d.DatasetName $datum | Add-Member –MemberType NoteProperty –Name ReportName –Value $d.ReportName $datum | Add-Member –MemberType NoteProperty –Name WorkspaceId –Value $d.WorkspaceId $datum | Add-Member –MemberType NoteProperty –Name ObjectId –Value $d.ObjectId $datum | Add-Member –MemberType NoteProperty –Name DashboardId –Value $d.DashboardId $datum | Add-Member –MemberType NoteProperty –Name DatasetId –Value $d.DatasetId $datum | Add-Member –MemberType NoteProperty –Name ReportId –Value $d.ReportId $datum | Add-Member –MemberType NoteProperty –Name OrgAppPermission –Value $d.OrgAppPermission #option to include the below JSON column however for large amounts of data it may be difficult for PBI to parse #$datum | Add-Member –MemberType NoteProperty –Name Datasets –Value (ConvertTo-Json $d.Datasets) #below is a poorly constructed PowerShell statemnt to grab one of the entries and place in the DatasetName if any exist foreach ($dataset in $d.datasets) { $datum.DatasetName = $dataset.DatasetName $datum.DatasetId = $dataset.DatasetId } $data+=$datum } $datestring = $startDate.ToString("yyyyMMdd") $fileName = ("c:\PBIAuditLogs\" + $datestring + ".csv") Write-Host (" writing to file {0}" -f $fileName) $data | Export-csv $fileName #> Remove-PSSession -Id $Session.Id 
