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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s