VBScripts BLOG

13 Jul 2009 @ 3:33 PM

Scan ping powershell script with output to Microsoft Excel

Here is a small script for something you could do with any other tool but is was fun building it in Powershell. I needed to scan a few subnets in our infrastructure. I know there are tools who can do this task but I thought why not use powershell.

This is what I made of it. The function pinger is straight forward. It does a ping and checks if the output of the pinger function is success. If so the host is up and running otherwise the host is down.

I ran in to a small problem when I wanted to export the output of this powershell script to Microsoft Excel because, I don´t live in the US, my local settings are NL and not en-US. If you use the code found on the Internet you get a error message like this one.

Exception calling "Add" with "0" argument(s): "Old format or invalid
type library. (Exception from HRESULT: 0×80028018
(TYPE_E_INVDATAREAD))"

The code below is unusable if you don’t have you local settings set to
en-US.

$a = New-Object -comobject Excel.Application

$a.Visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "A value in cell A1."
$b.SaveAs("C:\Scripts\Test.xls")

$a.Quit()

The code used in my code fixes this by using the invoker function to force the use of en-US in the Excel workbook. I found this invoker function on the Internet but I don’t know who wrote it so thanks to mister/miss X.

function pinger
{
    param($ip)
    $ping = New-Object System.Net.NetworkInformation.Ping
    $alive = $ping.send($ip)
    if ($alive.Status -eq "Success")
        {
            $out = $ip  + ", Is alive"
            return $out
        }
    else
        {
             $out = $ip  + ", Is dead"
            return $out
        }
}

function Invoke([object]$m, [string]$method, $parameters)
{
    $ciUS = [System.Globalization.CultureInfo]'en-US'
    #----------one line start-------------------------- 
    $m.PSBase.GetType().InvokeMember(
    $method, [Reflection.BindingFlags]::InvokeMethod,
    $null, $m, $parameters,$ciUS)
    #----------one line end----------------------------
} 

#----------------User settings-------------------------
$count = 1
$Max = 10
$ipbase="10.10.10."
#------------------------------------------------------

$objExcel = New-object -com Excel.Application
$objExcel.visible = $True
$objWorkbook = Invoke $objExcel.Workbooks Add
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$objWorksheet.Cells.Item($count,1).Formulalocal = "IP number"
$objWorksheet.Cells.Item($count,2).Formulalocal = "State"

while($count -le $max)
{
    $out=pinger $ipbase$count
    $parts=$out.Split(",")
    $objWorksheet.Cells.Item($count+1,1).Formulalocal = $parts[0]
    $objWorksheet.Cells.Item($count+1,2).Formulalocal = $parts[1]
    $count++
}

Invoke $objWorkbook SaveAs "C:\Ping states.xlsx" > $null
Invoke $objWorkbook Close 0 > $null
$objExcel.Quit()

Tags: Network, PowerShell
Categories: PowerShell Posted By: Richard
Last Edit: 13 Jul 2009 @ 07 44 PM

Email • Permalink • Comments (0)

Login

Search

Tagging

UserOnline

Disclaimer

Posts tagged as ' Network ' ...

Scan ping powershell script with output to Microsoft Excel

About me