EXPORT RESULT IN CSV

Welcome Forums General PowerShell Q&A EXPORT RESULT IN CSV

This topic contains 16 replies, has 3 voices, and was last updated by

 
Participant
6 months, 2 weeks ago.

  • Author
    Posts
  • #101169

    Participant
    Points: 0
    Rank: Member

    hello all

    i use the code below and i want to put the result in a CSV file
    could you help me please???

    $PC=import-csv ("C:\Program Files\XXX\etc\apps\XXX\lookups\SAP.csv") | select -ExpandProperty host

    function get-onlinetest
    {
    Param
    (
    # Name of computer
    $PC
    )

    # fonction de test
    $onlinetest = Test-Connection -computername $PC -Count 1 -quiet
    if ($onlinetest -eq $false)
    {

    write-host "$PC offline"
    }
    else
    {
    #code ร  executer apres la vรฉrification
    write-host "$PC online"
    }

    }

    #execution du code
    $computers = $PC
    $Results = foreach ($PC in $computers){get-onlinetest $PC}

  • #101170

    Participant
    Points: 241
    Helping Hand
    Rank: Participant
    $PC = import-csv -Path 'C:\Program Files\XXX\etc\apps\XXX\lookups\SAP.csv' | Select-Object -ExpandProperty host
    
    function get-onlinetest {
        Param(
            [System.String[]]
            $ComputerName
        )
    
        foreach ($Computer in $ComputerName) {
            if (Test-Connection -ComputerName $Computer -Quiet -Count 1) {
                [PSCustomObject]@{
                    ComputerName = $Computer
                    Online       = $true
                }
            }
            else {
                [PSCustomObject]@{
                    ComputerName = $Computer
                    Online       = $false
                }
            }
        }
    }
    
    get-onlinetest -ComputerName $pc |
        Export-Csv -Path 'C:\output\SAP-Out.csv' -Delimiter ',' -NoTypeInformation

    ... untested ... there are many more improvemnets thinkable but this should guide you to the right direction. And please format your code as code here in the forum. Thanks.
    Edit: Now I tested it – it works.

  • #101178

    Participant
    Points: 0
    Rank: Member

    Oh thank you very much it's perfect!

  • #101304

    Participant
    Points: 0
    Rank: Member

    hi
    i would like a new evolution please
    i m going to use a scheduled task which runs every 30 minutes
    in fact i would detect the machine which dont ping since 30 minutes
    so in SAP-Out.csv' i would like to have a third column with the date and the time when the script is played (now) and in a fourth colum the date and the time when the script is played again (now + 30 min)
    when the fourth colum will be full the script will be able to write again on the third column
    my goal is to monitore the KO ping which lasts
    thank you very much for your help

  • #101308

    Participant
    Points: 241
    Helping Hand
    Rank: Participant

    I would recommend not to overcomplicate things without the need.

    $PC = import-csv -Path 'C:\Program Files\XXX\etc\apps\XXX\lookups\SAP.csv' | Select-Object -ExpandProperty host
    
    function get-onlinetest {
        Param(
            [System.String[]]
            $ComputerName
        )
        $timeStamp = Get-Date
        foreach ($Computer in $ComputerName) {
            if (Test-Connection -ComputerName $Computer -Quiet -Count 1) {
                [PSCustomObject]@{
                    ComputerName = $Computer
                    Online       = $true
                    TimeStamp    = $timeStamp
                }
            }
            else {
                [PSCustomObject]@{
                    ComputerName = $Computer
                    Online       = $false
                    TimeStamp    = $timeStamp
                }
            }
        }
    }
    
    get-onlinetest -ComputerName $pc |
        Export-Csv -Path 'C:\output\SAP-Out.csv' -Delimiter ',' -NoTypeInformation -Append

    If you really need the date and time information and you want to have it all in one file you could add 1 more column with the date and time information and append the data every time you run the function. This way you can filter the data table in Excel if you need or you could export it to a database or whatever.

    • #101322

      Participant
      Points: 0
      Rank: Member

      Hello Olaf and thanks
      it works
      but when you say "add more column with the date" it s in SAP-Out.csv" or in another csv
      my aim is to pick up the first date and time when i run the fonction and the second date and time when i run again the fonction and to have a message like this :
      "The computer$ is not pinging since (delay between TimeStamp 2 minus TimeStamp1) minutes"
      i know its complicated ๐Ÿ˜‰
      thanks

    • #101340

      Participant
      Points: 241
      Helping Hand
      Rank: Participant

      to have a message like this :
      "The computer$ is not pinging since (delay between TimeStamp 2 minus TimeStamp1) minutes"
      i know its complicated

      If it's not just for learning purposes – are you sure you wnat to do something like this with a script? There are monitoring solution out there designed for those purposes. ๐Ÿ˜‰

  • #101311

    Participant
    Points: 303
    Helping Hand
    Rank: Contributor

    You can simplify this script using calculated expressions. If you set this up to run every 30 minutes (or really whatever interval), this will check the last run CSV and see if a host is currently offline and offline during the last run.

    CSV

    "Host"
    "www.google.com"
    "www.yahoo.com"
    "badhost"
    

    Code

    $results = Import-Csv "C:\Scripts\test.csv" | 
               Select-Object -Property Host,
                                       @{Name='PrevOnline';Expression={$_.Online}}, #Get the prev online property with calc expression
                                       @{Name='PrevDateTime';Expression={$_.DateTime}}, #Get the prev datetime property with calc expression
                                       @{Name='Online';Expression={Test-Connection -ComputerName $_.Host -Quiet -Count 1}}, #Get the current online property with calc expression
                                       @{Name='DateTime';Expression={Get-Date}} #Get the current datetime property with calc expression
    
    #Get the hosts that have been offline since the last run and are still offline
    $results | Where{$_.PrevOnline -eq $false -and $_.Online -eq $false} 
    
    #Overwrite the last csv to use for the next run
    $results |
    Export-CSV "C:\Scripts\test.csv" -NoTypeInformation
    

    Results

    Host         : badhost
    PrevOnline   : False
    PrevDateTime : 5/28/2018 12:23:32 PM
    Online       : False
    DateTime     : 5/28/2018 12:33:28 PM
    
    • #101328

      Participant
      Points: 0
      Rank: Member

      olaf
      is it also possible to have online or offline for "Online" instead true or false?
      thanks

    • #101335

      Participant
      Points: 241
      Helping Hand
      Rank: Participant

      ... is it also possible to have online or offline for "Online" instead true or false?

      The code I posted is just a suggestion. ๐Ÿ˜‰ There is no copyright on it. Feel free to change and extend it to your needs. ๐Ÿ˜€

  • #101325

    Participant
    Points: 0
    Rank: Member

    hello rob

    it s difficult to adapt your code with my data
    when i run your script that i have modified a little

    $results = import-csv -Path 'C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\SAP.csv' | Select-Object -ExpandProperty host |
    
    
               Select-Object -Property Host,
                                       @{Name='PrevOnline';Expression={$_.Online}}, #Get the prev online property with calc expression
                                       @{Name='PrevDateTime';Expression={$_.DateTime}}, #Get the prev datetime property with calc expression
                                       @{Name='Online';Expression={Test-Connection -ComputerName $_.Host -Quiet -Count 1}}, #Get the current online property with calc expression
                                       @{Name='DateTime';Expression={Get-Date}} #Get the current datetime property with calc expression
    
    #Get the hosts that have been offline since the last run and are still offline
    $results | Where{$_.PrevOnline -eq $false -and $_.Online -eq $false} 
    
    #Overwrite the last csv to use for the next run
    $results |
    Export-Csv -Path 'C:\temp\SAP-Out2.csv' -NoTypeInformation

    i just obtain:
    Host,"PrevOnline","PrevDateTime","Online","DateTime"
    ,,,,"29/05/2018 08:18:46"
    ,,,,"29/05/2018 08:18:46"
    ,,,,"29/05/2018 08:18:46"
    ,,,,"29/05/2018 08:18:46"
    ,,,,"29/05/2018 08:18:46"
    ,,,,"29/05/2018 08:18:46"

    thanks

  • #101347

    Participant
    Points: 0
    Rank: Member

    last question
    when my data are copying into SAP-Out.csv' I would like that datas overwrite old datas (actually data are written at the end of old datas)
    thanks

    • #101350

      Participant
      Points: 241
      Helping Hand
      Rank: Participant

      Powershell is out of the box not able to "edit" csv files. You can add additional rows with the parameter -Append but you cannot change existing rows. You would have to read it completely, change the contained data and write it completely new.

  • #101355

    Participant
    Points: 0
    Rank: Member

    Olaf

    i know there is no copyright but i dont reach to put offline or online
    could you help me please? (i just begin PowerShell)
    . 😀

    • #101367

      Participant
      Points: 241
      Helping Hand
      Rank: Participant

      but i dont reach to put offline or online

      In my code example you replace $true with 'online' and $false with 'offline' (including the quotes please! ๐Ÿ˜‰ ).
      You might take a little step back and start with learning the very basics of Powershell from scratch. In this thread you'll find some great sources to start from: Beginner Sites and Tutorials. Espacially the free video courses from the Microsoft Virtual Academy are really highly recommended.

    • #101425

      Participant
      Points: 0
      Rank: Member

      ok many thanks

  • #101362

    Participant
    Points: 303
    Helping Hand
    Rank: Contributor

    Note in my example, the source and destination path is the same. Copy the file from C:\Program Files\Splunk\etc\apps\Splunk_TA_windows\lookups\SAP.csv to your desktop. Use the same path and test the script and understand what it is doing. As Olaf said, the script will completely replace the CSV and use it like a database. Review the CSV, run the script and look at the CSV again. Test, test, test and understand, then attempt to implement.

The topic ‘EXPORT RESULT IN CSV’ is closed to new replies.