如何从excel检索数据ip ping .. [英] How to retrieve data from excel for ip ping..

问题描述

我一直在从一个文本文件中抽取ip地址的脚本，然后将它们永久循环，如果有的话，那么状态输出变为红色，并且发送一个用于警报的电子邮件。

我的脚本..

  $ hostnamestxt =C： 
 $ servers = get-content$ hostnamestxt
 $ date = get-date 
 
 while（$ true）{
 $ i ++ 
写主机 - 圆$ i机器 - ＃-foregroundcolor black -backgroundcolor green 
 foreach（$ server in $ servers）{
 if（test-Connection -ComputerName $ server  - Count 2 -Quiet）
 
 {
 write-host$ server is ONLINE-foregroundcolor black -backgroundcolor green 
} 
 else 
 {
 $ to = $ server 
 switch（$ to）
 {
192.168.252.37{$ device =RAPID; break} 
192.168.252.39{$ device =Sash line Welder83; break} 
192.168.252.40{$ device =Sash line Welder84; break} 
 
 
 default {$ device =error; exit} 
} 
 
 write-host$ device，$ server is OFFLINE /不可访问的-foregroundcolor black -backgroundcolor red 
 send-mailmessage -tomyemail-subject$ device，$ server is OFFLINE / UNREACHABLE at $ date-Body$ device，$ server is OFFLINE / UNREACHABLE at $日期
} 
} 
} 
  

以上工作正常，但我的问题是，当添加新的设备来监视时，我必须在txt文件中添加ip，然后还修改脚本和案例状态。

我想做什么，但不知道该怎么做，请从列A中读取ip地址和excel电子表格，然后名称将在列B中。

如果任何设备关闭，它将从B列翻译名称，并将其放入一个变量中以便稍后处理。任何帮助都非常感激，因为我现在很困难。

解决方案

不用指定文本文件，请使用CSV。然后，您可以使用Import-CSV命令行，它将将列表转换为对象的集合，这些对象将具有IP和Name等属性，具体取决于您在列表中的内容。你甚至可以添加一些关键的东西。我使用你的脚本，并做了几个非常快速的变化。

CSV：

  IP，名称
 192.168.252.37，RAPID 
 ... 
  

脚本：

  $ hostnamestxt =C：\ping_machines.csv
 $ servers = Import-Csv$ hostnamestxt
 $ date = get-date 
 
 while（$ true）{
 $ i ++ 
 Write-Host -  Round $ i Machines--＃-foregroundcolor black -backgroundcolor绿色
 foreach（$ server in $ servers）{
 $ serverName = $ server.Name 
 $ serverAddress = $ server.IP 
 if（test-Connection -ComputerName $ serverAddress  - Count 2 -Quiet）
 
 {
写主机$ serverName是ONLINE-foregroundcolor黑色-backgroundcolor绿色
} 
 else 
 {
 
 write-host$ serverName，$ serverAddress is OFFLINE / UNREACHABLE-foregroundcolor black -backgroundcolor red 
 send-mailmessage -tomyemail-subject$ serverName，$ serverAddress is OFFLINE / UNREACHABLE在$ date-Body$ s erverName，$ serverAddress是OFFLINE / UNREACHABLE在$ date
} 
} 
} 
  

I have been working on script that pulls ip addresses from a text file, and then pings them in a forever loop, if any are dead then the status output turn red, and also sends an email for alerting.

my script..

$hostnamestxt = "C:\ping_machines.txt"
$servers = get-content "$hostnamestxt"
$date = get-date

while ($true) {
$i++
Write-Host "-- Round $i Machines--" # -foregroundcolor black -backgroundcolor             green 
foreach($server in $servers){
if (test-Connection -ComputerName $server -Count 2 -Quiet )  

{   
    write-host "$server is ONLINE" -foregroundcolor black -backgroundcolor green 
}  
    else 
{  
    $to = $server
        switch ($to) 
        { 
                "192.168.252.37" {$device="RAPID"; break}
                "192.168.252.39" {$device="Sash line Welder83"; break}
                "192.168.252.40" {$device="Sash line Welder84"; break}


         default {$device="error";exit}       
        }

    write-host "$device, $server is OFFLINE/UNREACHABLE" -foregroundcolor black -backgroundcolor red
    send-mailmessage -to "myemail" -subject "$device, $server is OFFLINE/UNREACHABLE at $date" -Body "$device, $server is OFFLINE/UNREACHABLE at $date"
} 
}
}

the above works ok, but the issue i have is that when adding new devices to monitor, i have to add the ip in the txt file then also amend script and into the case statment.

what i would like to do, but not sure how to do is.. read the ip addresses from and excel spreadsheet, say from column A, and then the names will be in column B.

If any device is down it then translates the name from column B, and puts that into a variable to process later.

Any help much appreciated, as i am pretty much stuck at the moment.

解决方案

Instead of specifying a text file, use a CSV. Then you can use the Import-CSV commandlet and it will turn the list into a collection of objects, which will have properties like IP and Name, depending on what you put in the list. You could even add something for criticality. I used your script and made a couple of really quick changes. Something like this should work.

CSV:

IP,Name
192.168.252.37,RAPID
...

Script:

$hostnamestxt = "C:\ping_machines.csv"
$servers = Import-Csv "$hostnamestxt"
$date = get-date

while ($true) {
$i++
Write-Host "-- Round $i Machines--" # -foregroundcolor black -backgroundcolor             green 
foreach($server in $servers){
$serverName = $server.Name
$serverAddress = $server.IP
if (test-Connection -ComputerName $serverAddress -Count 2 -Quiet )  

{   
    write-host "$serverName is ONLINE" -foregroundcolor black -backgroundcolor green 
}  
    else 
{  

    write-host "$serverName, $serverAddress is OFFLINE/UNREACHABLE" -foregroundcolor black -backgroundcolor red
    send-mailmessage -to "myemail" -subject "$serverName, $serverAddress is OFFLINE/UNREACHABLE at $date" -Body "$serverName, $serverAddress is OFFLINE/UNREACHABLE at $date"
} 
}
}

这篇关于如何从excel检索数据ip ping ..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！