批量Ping DHCP预留工作站：如何导出主机名、IP及状态至Excel/CSV

Hey Jason, I get that you're new to this and just need a straightforward solution to get your ping results into Excel easily. Let's break this down with two simple, ready-to-use options—one using a batch file (since you already started with that) and another VBS script that's pre-configured for your needs.

This batch file will read a list of your hostnames and IPs, run pings, and output a CSV file that Excel can open directly with your desired format.

Step 1: Prepare Your Host List #

First, create a text file named hosts.txt in the same folder as your batch file. Each line should have a hostname followed by its reserved IP, separated by a space:

testhostname 192.168.1.1
workstation01 192.168.1.2
workstation02 192.168.1.3

Step 2: The Batch Script #

Copy this code into a new file, save it as batch_ping.bat:

@echo off
setlocal enabledelayedexpansion

:: Define output file (CSV format, works directly in Excel)
set "outputFile=ping_results.csv"

:: Write CSV header
echo "Hostname","IP Address","Status" > %outputFile%

:: Loop through each line in hosts.txt
for /f "tokens=1,2" %%a in (hosts.txt) do (
    set "hostname=%%a"
    set "ip=%%b"
    set "status=Pass"
    
    :: Ping twice with 2-second timeout to speed things up
    ping -n 2 -w 2000 !ip! | findstr /i "Request timed out" >nul
    if not errorlevel 1 (
        set "status=Timedout"
    ) else (
        ping -n 2 -w 2000 !ip! | findstr /i "Destination host unreachable" >nul
        if not errorlevel 1 (
            set "status=Failed"
        )
    )
    
    :: Append result to CSV
    echo "!hostname!","!ip!","!status!" >> %outputFile%
)

echo Done! Results saved to %outputFile%
pause

Step 3: Run It #

Double-click batch_ping.bat. When it finishes, you'll have a ping_results.csv file—just open it with Excel, and you'll see your clean table of hostnames, IPs, and ping statuses.

If you want a slightly more robust solution (it uses Windows Management Instrumentation for better status detection), here's a pre-written VBS script you can use.

Step 1: Same Host List #

Use the same hosts.txt file from Option 1.

Step 2: The VBS Script #

Copy this code into a new file, save it as ping_script.vbs:

' Configuration - match these to your file names
strInputFile = "hosts.txt"
strOutputFile = "ping_results.csv"

' Set up file system tools
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open input file and create output file with header
Set objInput = objFSO.OpenTextFile(strInputFile, 1)
Set objOutput = objFSO.CreateTextFile(strOutputFile, True)
objOutput.WriteLine """Hostname"",""IP Address"",""Status"""

' Process each host in the list
Do Until objInput.AtEndOfStream
    strLine = objInput.ReadLine
    arrHostData = Split(strLine, " ")
    strHostname = arrHostData(0)
    strIP = arrHostData(1)
    
    ' Run ping via WMI
    Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
        ("SELECT * FROM Win32_PingStatus WHERE Address = '" & strIP & "'")
    
    ' Determine ping status
    For Each objStatus In objPing
        If IsNull(objStatus.StatusCode) Then
            strStatus = "Failed"
        ElseIf objStatus.StatusCode = 0 Then
            strStatus = "Pass"
        Else
            Select Case objStatus.StatusCode
                Case 11010, 11011, 11012
                    strStatus = "Timedout"
                Case Else
                    strStatus = "Failed"
            End Select
        End If
    Next
    
    ' Write result to CSV
    objOutput.WriteLine """" & strHostname & """,""" & strIP & """,""" & strStatus & """"
Loop

' Clean up and notify
objInput.Close
objOutput.Close
MsgBox "Ping check finished! Results saved to " & strOutputFile, vbInformation

Step 3: Run It #

Double-click ping_script.vbs. A pop-up will tell you when it's done, and you'll have the same CSV file ready for Excel.

If you don't already have your hostnames and IPs in a list:

1. Open the Windows DHCP Console
2. Right-click the Reservations folder for your scope
3. Select Export List and save it as a text file
4. Open that text file in Excel, delete any extra columns (keep only Hostname and IP Address)
5. Save it as a Text (Tab delimited) file, then replace the tabs with spaces (use Excel's "Find and Replace" for this) to get your hosts.txt ready.

内容的提问来源于stack exchange，提问作者Jason