Scheduled Task for PowerShell Only Exports One Row

PowerShell Help
1

Hello,

I have a script that, when executed manually, runs as desired. The script outputs n amount of records to a .csv file. However, when the script runs in Task Scheduler, it only exports 1 record even thought there could be n+1 records.

Here is the export command I’m using in the script (not sure if it needs to be changed for Task Scheduler):

[pre]

Build File and Output to Path

$dateString = $startDate.ToString(“yyyyMMdd”)
$fileName = ("C:\PowerShell\output" + $dateString + “.csv”)

Remove Column Header and Input Data

#$data | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Export-Csv -Path $fileName
$data | Select-Object -Skip 1 | Export-Csv -NoTypeInformation -Path $fileName
Remove-PSSession -Id $session.Id

[/pre]

Any idea why it would export only 1 record?

Thanks,

Frank

2

When the script is executed by Task Scheduler, are you sure that it has n+1 records to export? Is the same script responsible for collecting the records that are to be exported, or are the records generated by another method? Which user is executing the script in Task Scheduler, and does it have the necessary permissions to access the records?

How does information get stored in the $data variable, and what are its contents before the Export-Csv command?

3

Yes, I’m using the same script that I run manually which returns the correct amount of records. I’m running it with my account which is a local admin.

I’d think that if it was a permission issue, a file wouldn’t be written at all? That’s why I’m unsure why it would write the file and insert one record.

Below is the script I’m running:

Start-Transcript -Path "C:\PowerShell\output\transcript.txt" -NoClobber -Append
Disconnect-AzAccount
Import-Module AZ

$dashLine = "----------------------------------------------------------------------------------------"

Write-Host "Remote PowerShell Session"
Write-Host $dashLine

#region
#--------------------------------------------------------------------------
# Set up 'Connection Object' for Remote PoSh Session
$username = ''
$password = Get-Content "C:\PowerShell\key\tenantName.key" | ConvertTo-SecureString
$oLiveCred = New-Object System.Management.Automation.PSCredential $username, $password
#--------------------------------------------------------------------------
#endregion

#region
#--------------------------------------------------------------------------
# Remote PoSh Session attach
Write-Host "Creating Remote PowerShell Session connection..."
$session = New-PSSession -ConfigurationName Microsoft.Exchange -Authentication Basic -ConnectionUri https://ps.com -AllowRedirection:$true -Credential $oLiveCred
Import-PSSession $session -AllowClobber
#--------------------------------------------------------------------------
#endregion

#region
#--------------------------------------------------------------------------
# Declare variables
$startDate = (Get-Date).AddDays(-1)
$endDate = (Get-Date)
$scriptStart = (Get-Date)
$sessionName = (Get-Date -Format 'u') + 'pbiauditlog'
#--------------------------------------------------------------------------

# Reset User Audit Accumulator
$aggregateResults = @()
# Loop Counter
$i = 0
Do {
    $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBi 
    if ($currentResults.Count -gt 0) {
        Write-Host ("Finished {3} search #{1}, {2} records: {0} minutes." -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $username.UserPrincipalName )
        # Accumulate Data
        $aggregateResults += $currentResults
        # No need to do another query if the # of records returned are <1k
        if ($currentResults.Count -lt 1000){
            $currentResults = @()
        } else {
            $i++
        }
        }
    } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #         

$data = @()
    foreach ($auditLogItem in $aggregateResults){
        $datum = New-Object -TypeName PSObject 
        $d = ConvertFrom-Json $auditLogItem.AuditData
        $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $auditlogitem.CreationDate
        $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $d.Operation
        $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 Activity -Value $d.Activity
        $datum | Add-Member -MemberType NoteProperty -Name Item -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 ObjectId -Value $d.ObjectId
    

    foreach ($dataset in $d.$datasets){
        $datum.DatasetName = $dataset.DatasetName
        $datum.DatasetId = $dataset.DatasetId
    }
    $data += $datum
    }

#region
#--------------------------------------------------------------------------
# Build File and Output to Path
$dateString = $startDate.ToString("yyyyMMdd")
$fileName = ("C:\PowerShell\output\" + $dateString + ".csv")
# Remove Column Header and Input Data
#$data | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Export-Csv -Path $fileName
$data | Select-Object -Skip 1 | Export-Csv -NoTypeInformation -Path $fileName
Remove-PSSession -Id $session.Id
#--------------------------------------------------------------------------
#endregion

Stop-Transcript
4

Remove start-transcript and stop-transcript if you are running it from Scheduled Task. You may have to log your write-host output differently.

5

There seems to be no issue in your script. I created a similar script after looking at yours and it worked perfectly fine even after running it from task schedular.

One thing I would like you to check is the details in the transcript file. May be, that will help you.

6

I got it working. I was using an older script. Second set of eyes caught the mistake!

Thanks for everyone’s feedback!