Scheduled Task for PowerShell Only Exports One Row

Welcome Forums General PowerShell Q&A Scheduled Task for PowerShell Only Exports One Row

Viewing 4 reply threads
  • Author
    Posts
    • #183630
      Participant
      Topics: 12
      Replies: 17
      Points: 152
      Rank: Participant

      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):

      
      # 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
      
      

      Any idea why it would export only 1 record?

      Thanks,

      Frank

    • #183660
      Participant
      Topics: 3
      Replies: 68
      Points: 366
      Helping Hand
      Rank: Contributor

      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?

      • #183764
        Participant
        Topics: 12
        Replies: 17
        Points: 152
        Rank: Participant

        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
    • #183918
      Naw
      Participant
      Topics: 4
      Replies: 45
      Points: 90
      Rank: Member

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

    • #183981
      Participant
      Topics: 1
      Replies: 9
      Points: 84
      Helping Hand
      Rank: Member

      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.

    • #184134
      Participant
      Topics: 12
      Replies: 17
      Points: 152
      Rank: Participant

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

      Thanks for everyone's feedback!

Viewing 4 reply threads
  • You must be logged in to reply to this topic.