Search value into CSV and put all to variable

Welcome Forums General PowerShell Q&A Search value into CSV and put all to variable

This topic contains 8 replies, has 2 voices, and was last updated by

 
Participant
2 weeks, 2 days ago.

  • Author
    Posts
  • #174190

    Participant
    Topics: 8
    Replies: 27
    Points: -22
    Rank: Member

    Hi people,

    I get a log file from an equipment to view a specific information. I already read the csv, but I don't put de all lines of log to a foreach. The script is below:

    
    $DGI = Import-Csv c:\DGI.CSV -Header @("device","port","alarm","description","time","status","severity")
    
    foreach ($line in $DGI) {
    
    $port = $line.port
    
    $time = $line.time
    
    $column1 = @($port)
    
    $column2 = @($time)
    
    $i=0
    
    $alarmdgi = ($column1,$column2)[0]|foreach {
    
    new-object psobject -property @{
    
    ONU = $Column1[$i]
    
    TIME = $column2[$i++]
    
    }
    
    } |where ONU -ge 0 |%{write-output "[ONU: $($_.ONU) TIME: $($_.TIME)]"} | sort} 

    When I run the script the result is:

    
    

    DGI: [ONU: gpon-1/1/5/37 TIME: 2019-08-14 10:32:29 UTC-3] 

    But, I know that have 2 informations to appear. Something like:

    DGI: [ONU: gpon-1/1/5/37 TIME: 2019-08-14 10:32:29 UTC-3][ONU: gpon-1/1/7/45 TIME: 2019-08-14 10:00:00 UTC-3] 

     

    Thank you for any help.

     

     

     

  • #174196

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    I'm struggling to recognize what exactly you like to do with your code. You might describe that a little more detailed. You may post a few lines of your CSV files
    (formatted as code as well please)

    Regardless of that: Could you please try to avoid to post a lot of unnecessary white space? Thanks.

    • #174199

      Participant
      Topics: 8
      Replies: 27
      Points: -22
      Rank: Member

      Hi,

      Here the original CSV:

      onu,gpon-1/1/5/25,GPON_DGi,Receive dying gasp of ONU,2019-09-01 05:46:42 UTC-3,false,CRITICAL
      onu,gpon-1/1/5/37,GPON_DGi,Receive dying gasp of ONU,2019-08-14 10:32:29 UTC-3,false,CRITICAL

      I need to get the second and fifth columns: customer and time event respectively. Into Powershell code I am putting a label of columns and trying to create a foreach to put into a variable.

      Thanks and sorry about white spaces.

  • #174202

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    Hmmm ... with this description this code snippet should be enough:

    $DGI  = @'
    onu,gpon-1/1/5/25,GPON_DGi,Receive dying gasp of ONU,2019-09-01 05:46:42 UTC-3,false,CRITICAL
    onu,gpon-1/1/5/37,GPON_DGi,Receive dying gasp of ONU,2019-08-14 10:32:29 UTC-3,false,CRITICAL
    '@ | 
        ConvertFrom-Csv -Delimiter ',' -Header device, port, alarm, description, time, status, severity |
        Select-Object -Property port,time
    $DGI

    Instead of the ConvertFrom-CSV you should use your Import-CSV cmdlet of course.

    • #174214

      Participant
      Topics: 8
      Replies: 27
      Points: -22
      Rank: Member

      Hello Olaf, thanks for your time and kindness,

      I didn't understand this part of your code:

       $ DGI = @ '
      un, gpon-1/1/5/25, GPON_DGi, receives a dying breath from the UN, 2019-09-01 05:46:42 UTC-3, false, CRITICAL
      UN, gpon-1/1/5/37, GPON_DGi, receives a dying breath from the UN, 2019-08-14 10:32:29 UTC-3, false, CRITICAL
      '@ | 

      Do I need to put log content in plain text? Or is Working with a variable in which I import CSV?
      Thank you in advance.

    • #174220

      Participant
      Topics: 1
      Replies: 1528
      Points: 2,581
      Helping Hand
      Rank: Community Hero

      Rodrigo,

      That's just a way to deliver the data together with the code and make sure it works as intended. Of course you should use the Import-Csv you posted initially ... like this:

      $DGI = Import-Csv c:\DGI.CSV -Delimiter ',' -Header device, port, alarm, description, time, status, severity |
          Select-Object -Property port, time
      $DGI

      Boa noite 😉

    • #174223

      Participant
      Topics: 8
      Replies: 27
      Points: -22
      Rank: Member

      Thank you Olaf! we are almost there!!

      Clientes DGI:
      gpon-1/1/2/39 2019-09-02 18:07:18 UTC-3
      gpon-1/1/2/41 2019-09-02 16:48:25 UTC-3
      gpon-1/1/5/22 2019-09-02 18:31:42 UTC-3
      gpon-1/1/5/25 2019-09-01 05:46:42 UTC-3
      gpon-1/1/5/37 2019-08-14 10:32:29 UTC-3 
      

      Now, I would like two things: remove the " UTC-3" and separate each group information with a [ ] Like that:

      [gpon-1/1/2/39 2019-09-02 18:07:18]
      [gpon-1/1/2/41 2019-09-02 16:48:25]
      [gpon-1/1/5/22 2019-09-02 18:31:42]
      [gpon-1/1/5/25 2019-09-01 05:46:42]
      [gpon-1/1/5/37 2019-08-14 10:32:29]
      

      Again, thank you so much!!

  • #174226

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    As usual you have several options ...

    Either with a calculated property ...

    $DGI = Import-Csv c:\DGI.CSV -Delimiter ',' -Header device, port, alarm, description, time, status, severity |
    Select-Object -Property @{Name = 'Rodrigos Output'; Expression = { "[{0} {1}]" -f $_.port, $($_.time -replace '\s+UTC-3$') } }
    $DGI

    ... or with a PSCustomObject ...

    $DGI = Import-Csv c:\DGI.CSV -Delimiter ',' -Header device, port, alarm, description, time, status, severity |
    ForEach-Object {
        [PSCustomObject]@{
            'Rodrigos Output' = "[{0} {1}]" -f $_.port, $($_.time -replace '\s+UTC-3$')
        }
    }
    $DGI

    ... or ... 😉

    • #174262

      Participant
      Topics: 8
      Replies: 27
      Points: -22
      Rank: Member

      Olaf, thank you so much!!! worked exactly how I wished!!

      many thanks!!!

You must be logged in to reply to this topic.