Ping компьютеров из *.csv

PowerShell Help
1

Привет всем.
Стоит следующая задача: Необходимо отследить компьютеры, которые не выключаются на ночь.
Есть User_Comp.csv следующего содержания:

#TYPE Selected.Microsoft.ActiveDirectory.Management.ADUser
"pager","name","title","department"
"IVANOV_PC","Иванов Иван Иванович","Заместитель начальник","Отдел начальников"
"PC_BARANOV","Баранов Михаил Петрович","Главный надзиратель","Отдел надзирателей"
,"Горьков Михаил Денисович","Советник слесаря","Отдел слесарей"
"PETROV","Петров Максим Альбертович","Слесарь","Отдел слесарей"

В поле “pager” содержится имя компьютера (но не всегда. Если нет, то пропускать), которое и необходимо пинговать. Если пингуется, то записывать в новый *.csv-файл:

#TYPE Selected.Microsoft.ActiveDirectory.Management.ADUser
"pager","IP","name","title","department"
"PC_BARANOV","IP","Баранов Михаил Петрович","Главный надзиратель","Отдел надзирателей"
"PETROV","IP","Петров Максим Альбертович","Слесарь","Отдел слесарей"

Если не пигуется, то ничего не записывать в новый *.csv-файл (имя **csv-файла должно быть: дата_время.csv ).

Ранее пользовался скриптом, который пингует все компьютеры (которые лежат в определенной OU) в домене:

# Enter CSV file location
$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('MM-dd-yyyy_hh-mm-ss')
$csv = "C:\Temp\Ping_$CurrentDate.csv"
# Add the target OU in the SearchBase parameter
$Computers = Get-ADComputer -Filter * -SearchBase "OU=SuperComputers,DC=domain,DC=ru" | Select Name | Sort-Object Name
$Computers = $Computers.Name
$Headers = "ComputerName,IP Address"
$Headers | Out-File -FilePath $csv -Encoding UTF8 -Delimiter ";"
foreach ($computer in $Computers)
{
Write-host "Pinging $Computer"
$Test = Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue -ErrorVariable Err
if ($test -ne $null)
{
$IP = $Test.IPV4Address.IPAddressToString
$Output = "$Computer,$IP"
$Output | Out-File -FilePath $csv -Encoding UTF8 -Append
}
Else
{
$Output = "$Computer,$Err"
$output | Out-File -FilePath $csv -Encoding UTF8 -Append
}
cls
}

Но теперь чуть другая задача.
Подсобите, plz, скриптом.

2

Hello @Kapgep,
If I understood your requirements correctly you need to adjust line #6 to something like this:

#Get info from csv file
$Computers=Import-Csv -Path User_Comp.csv

and line #7 to this:

#Get only computer names and filter blank ones
$Computers = $Computers.pager | where-object {$_ -ne ""}

and remove else block completely.
Modified code:

# Enter CSV file location
$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('MM-dd-yyyy_hh-mm-ss')
$csv = "C:\Temp\Ping_$CurrentDate.csv"
#Read data from csv
$Computers=Import-Csv -Path User_Comp.csv
$Computers = $Computers.pager | where-object {$_ -ne ""}
$Headers = "ComputerName,IP Address"
$Headers | Out-File -FilePath $csv -Encoding UTF8 -Delimiter ";"
foreach ($computer in $Computers)
{
Write-host "Pinging $Computer"
$Test = Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue -ErrorVariable Err
if ($test -ne $null)
{
$IP = $Test.IPV4Address.IPAddressToString
$Output = "$Computer,$IP"
$Output | Out-File -FilePath $csv -Encoding UTF8 -Append
}
}

Hope that helps.

3

Но в файл пишется лишь имя компьютера и его IP-адрес. А необходимо, чтобы писалось так же:
“pager”,“IP”,“name”,“title”,“department”

4

@Kapgep,
This forum is not for script requests. You need to try and experiment yourself and then come back for help if you get stuck. You can not just provide requirements and demand script.

Please keep that in mind when you will need help in future.

In your case you need to change couple more lines to make that happen.

# Enter CSV file location
$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('MM-dd-yyyy_hh-mm-ss')
$csv = "C:\Temp\Ping_$CurrentDate.csv"
$Results=New-Object System.Collections.Generic.List[PSObject]
#Read data from csv
$Computers=Import-Csv -Path User_Comp.csv
$Computers = $Computers| where-object {$_.pager  -ne ""}

foreach ($computer in $Computers)
{
    Write-host "Pinging $($Computer.pager)"
    $Test = Test-Connection -ComputerName $($Computer.pager) -Count 1 -ErrorAction SilentlyContinue -ErrorVariable Err
    if ($test -ne $null)
    {
        $IP = $Test.IPV4Address.IPAddressToString
        $p=[ordered]@{
            "Pager"=$Computer.Pager
            "IP"=$IP
            "name"=$Computer.Name
            "title"=$Computer.Title
            "department"=$Computer.Department
        }
        $Obj=New-Object -Type PsObject -Property $p
        $Results.Add($Obj)
   }
}
$Results | Export-Csv -NoTypeInformation -Path $csv

Hope that helps.

5

Спасибо большое.
Я учту Ваше пожелание.
Всё работает как нужно.

1 Like
6

Я начинаю изучать PowerShell, поэтому не всё так сразу. :slight_smile:
Подскажите, пожалуйста, возможно ли добавить в начало файла:

C:\Temp\Ping_$CurrentDate.csv

дату / время создания файла?
И второй вопрос: каким образом, можно реализовать список исключений? Т.е. есть список исключений - C:\Temp\exceptions_ping.csv, следующего формата:

“pager”,“IP”,“name”,“title”,“department”

который не должен попадать, в C:\Temp\Ping_$CurrentDate.csv

7

I’m starting to learn PowerShell, so it’s not all at once.
Please tell me if it is possible to add to the beginning of the file:

C:\Temp\Ping_$CurrentDate.csv

date / time when the file was created?
And the second question: how can you implement a list of exceptions? Those. there is a list of exceptions - C:\Temp\exceptions_ping.csv, in the following format:

“pager”,“IP”,“name”,“title”,“department”

which shouldn’t end up in C:\Temp\Ping_ $ CurrentDate.csv

8

So you want the filename with datetime for the file creation ?

Exceptions for what ? Can you tell us what this script will be used ?

9

You need to add the date / time to the file itself - “C:\Temp\Ping_$CurrentDate.csv”.

# Enter CSV file location
$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('MM-dd-yyyy_hh-mm-ss')
$csv = "C:\Temp\Ping_$CurrentDate.csv"
$Results=New-Object System.Collections.Generic.List[PSObject]
#Read data from csv
$Computers=Import-Csv -Path User_Comp.csv
$Computers = $Computers| where-object {$_.pager  -ne ""}

foreach ($computer in $Computers)
{
    Write-host "Pinging $($Computer.pager)"
    $Test = Test-Connection -ComputerName $($Computer.pager) -Count 1 -ErrorAction SilentlyContinue -ErrorVariable Err
    if ($test -ne $null)
    {
        $IP = $Test.IPV4Address.IPAddressToString
        $p=[ordered]@{
            "Pager"=$Computer.Pager
            "IP"=$IP
            "name"=$Computer.Name
            "title"=$Computer.Title
            "department"=$Computer.Department
        }
        $Obj=New-Object -Type PsObject -Property $p
        $Results.Add($Obj)
   }
}
$Results | Export-Csv -NoTypeInformation -Path $csv

The script checks the availability of computers that are in this list:

$Computers=Import-Csv -Path User_Comp.csv

Also, there is a list of exceptions. Those. a list of those computers that should not be included in this file:

$csv = "C:\Temp\Ping_$CurrentDate.csv"
10

You can filter the computers to be excluded like below.
If you have the list of computers in an array $ExcludeComputerList

$Computers = $Computers| where-object {$_.pager  -ne "" -and $_.Name -notin $ExcludeComputerList}

And for the file name

$csv = "C:\Temp\Ping_{0}.csv" -f (Get-Date -Format 'MM-dd-yyyy_hh-mm-ss')
1 Like