Output variable values as separate rows in a column in a csv file

Welcome Forums General PowerShell Q&A Output variable values as separate rows in a column in a csv file

Viewing 4 reply threads
  • Author
    Posts
    • #234634
      Participant
      Topics: 1
      Replies: 2
      Points: 15
      Rank: Member

      I need your help. It is a basic query where I am failing to get the right output. I am posting the relevant code from my powershell file.

      I am outputting the variable using calculated property.

      @{n="Definition";e={$D}}
      
      

      Output of $D is as follows in the last column of the .CSV File.

      1 2 3 4
      1 2 3 4
      1 2 3 4
      1 2 3 4

      I want the output to be as follows in the last column.

      1
      2
      3
      4
      $D1 = (Get-Content 'C:\1.DAT' | where {$_ -like "CurDefs*"}).Replace('CurDefs=','')
      $D2 = (Get-Content 'C:\2.DAT' | where {$_ -like "CurDefs*"}).Replace('CurDefs=','')
      $D3 = (Get-Content 'C:\3.DAT' | where {$_ -like "CurDefs*"}).Replace('CurDefs=','')
      $D4 = (Get-Content 'C:\4.DAT' | where {$_ -like "CurDefs*"}).Replace('CurDefs=','')
      $D = ($D1, $D2, $D3, $D4)
      
      (Invoke-RestMethod -Uri https://xxx:xxxx/sepm/api/v1/computers -Headers $header).content | select-object -first 4 @{n="Computer";e={$_.computerName}}, @{n="Online";e={$_.onlineStatus -replace '1','YES' -replace '0','NO'}}, @{n="AV";e={$_.avEngineOnOff -replace '1','ON' -replace '0','OFF'}}, @{n="Firewall";e={$_.firewallOnOff -replace '1','ON' -replace '0','OFF'}}, @{n="Tamper_Protection";e={$_.tamperOnOff -replace '1','ON' -replace '0','OFF'}}, @{n="Last_Contact";e={[TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]'1/1/1970').AddMilliSeconds($_.lastUpdateTime)}}, @{n="Last_Scan";e={[TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]'1/1/1970').AddMilliSeconds($_.lastScanTime)}}, @{n="Last_Virus_Detection";e={[TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]'1/1/1970').AddMilliSeconds($_.lastVirusTime)}}, @{n="Agent_Version";e={$_.agentVersion}}, @{n="Definition";e={$D}} | Export-Csv -Path 'C:\1.csv' -NoTypeInformation -UseQuotes Never
    • #234640
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

      You could use a nested loop or a kind of “counter” inside your expression definition in your calculated property for the property “Definition“.

      BTW: You should do your self (and all others) a favor and add some line breaks to make your code easier to read.

    • #234643
      Participant
      Topics: 1
      Replies: 2
      Points: 15
      Rank: Member

      You could use a nested loop or a kind of “counter” inside your expression definition in your calculated property for the property “Definition“.

      BTW: You should do your self (and all others) a favor and add some line breaks to make your code easier to read.

      Thank you for your reply. Sorry for the messy code. I will make it clean.

      Any example how can I use the nested loop or the counter which you are talking about inside the expression definition?

      There is one thing which I need to do to make the code cleaner.  I am converting the epoch time. I am using this in each expression.

      [TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]'1/1/1970').AddMilliSeconds

      I tried to put this into a variable and tried to call the expression in this format. {$variable[$._whatever]} but the output is empty. Therefore I am using the line to convert epoch time in each expression causing the code to look much bigger than actually it is. Any idea why the variable in expression of calculated property is not outputting the same result as when I use it manually?

    • #234649
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

      I meant something like this:

      $ID = 0
      (Invoke-RestMethod -Uri https://xxx:xxxx/sepm/api/v1/computers -Headers $header).content | 
      Select-Object -first 4 |
      ForEach-Object {
          [PSCustomObject]@{
              Online               = $_.onlineStatus -replace '1', 'YES' -replace '0', 'NO'
              AV                   = $_.avEngineOnOff -replace '1', 'ON' -replace '0', 'OFF'
              Firewall             = $_.firewallOnOff -replace '1', 'ON' -replace '0', 'OFF'
              Tamper_Protection    = $_.tamperOnOff -replace '1', 'ON' -replace '0', 'OFF'
              Last_Contact         = [TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]$_.lastUpdateTime)
              Last_Scan            = [TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]$_.lastScanTime)
              Last_Virus_Detection = [TimeZone]::CurrentTimeZone.ToLocalTime([Datetime]$_.lastVirusTime)
              Agent_Version        = $_.agentVersion
              Definition           = $D[$ID]
          }
          $ID++
      } | 
      Export-Csv -Path 'C:\1.csv' -NoTypeInformation -UseQuotes Never
    • #234655
      Participant
      Topics: 1
      Replies: 2
      Points: 15
      Rank: Member

      I meant something like this:

      I can’t thank you enough. You solved all my problems with a much cleaner code. 🙂

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