Tuesday, January 16, 2018

PowerShell – HostName to IP along with Ping in Excel

The script that I am sharing today is variation of below script:

PowerShell – Ping Machines and report in Excel

This script will use list of Host Names , pings them, report if its up or down and fetch the IP address from PING command.

This has been requested by our windows team as they have list of hostnames for hundereds of server and they wanted

to note down their IPAddress for inventory/ Documentation.

Download and extract  the solution from below link

https://gallery.technet.microsoft.com/scriptcenter/PowerShell-HostName-to-IP-d5d14b76



 

Update the MachineList.txt file and launch the batch file.(Excel will open & start populating with results)

Note: Excel is required component for this script to work.




################################################################### 
##           Script to check the status of machines               
##           Author: Vikas Sukhija   http://SysCloudPro.com                              
##           Date: 01-28-2016  
##             Update: 12/12/2017 
##           Update:  Converted from VBscript 
##              Modified: Host Name to IP(orignal : IP to hostanme) 
##https://gallery.technet.microsoft.com/scriptcenter/Ping-Machines-and-report-19d590ce           
################################################################### 
 
$path = ".\results.xls" 
 
$objExcel = new-object -comobject excel.application  
 
if (Test-Path $path)  
{  
$objWorkbook = $objExcel.WorkBooks.Open($path)  
$objWorksheet = $objWorkbook.Worksheets.Item(1)  

 
else {  
$objWorkbook = $objExcel.Workbooks.Add()  
$objWorksheet = $objWorkbook.Worksheets.Item(1) 

 
$objExcel.Visible = $True 
 
#########Add Header#### 
 
$objWorksheet.Cells.Item(1, 1) = "HostName" 
$objWorksheet.Cells.Item(1, 2) = "Result" 
$objWorksheet.Cells.Item(1, 3) = "MachineIP" 
 
$machines = gc .\machinelist.txt 
$count = $machines.count 
 
$row=2 
 
$machines | foreach-object{ 
$ping=$null 
$iname =$null 
$machine = $_ 
$ping = Test-Connection $machine -Count 1 -ea silentlycontinue 
 
if($ping){ 
 
$objWorksheet.Cells.Item($row,1) = $machine 
$objWorksheet.Cells.Item($row,2) = "UP" 
     
$iname = $ping.IPV4Address.IPAddressToString 
 
$objWorksheet.Cells.Item($row,3) = $iname 
         
$row++
else { 
 
$objWorksheet.Cells.Item($row,1) = $machine 
$objWorksheet.Cells.Item($row,2) = "DOWN" 
 
$row++

 
#################################################################




Thanks for downloading

Vikas Sukhija

http://SysCloudPro.com

2 comments:

  1. Request for PowerShell that can ping Device and export data in Excel with status and mail

    ReplyDelete
  2. I love this script but is there a way to have the IP cell populated even if the computer is Down?

    ReplyDelete