EXPORT RESULT IN CSV

This topic contains 16 replies, has 3 voices, and was last updated by  rosse 3 weeks, 2 days ago.

  • Author
    Posts
  • #101169

    rosse
    Participant

    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

    Olaf Soyk
    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

    rosse
    Participant

    Oh thank you very much it's perfect!

  • #101304

    rosse
    Participant

    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

    Olaf Soyk
    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

      rosse
      Participant

      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

      Olaf Soyk
      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

    Rob Simmers
    Participant

    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

      rosse
      Participant

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

    • #101335

      Olaf Soyk
      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

    rosse
    Participant

    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

    rosse
    Participant

    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

      Olaf Soyk
      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

    rosse
    Participant

    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

      Olaf Soyk
      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

      rosse
      Participant

      ok many thanks

  • #101362

    Rob Simmers
    Participant

    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.

You must be logged in to reply to this topic.