Retrieve Ipconfig /all in excel Cell

This topic contains 3 replies, has 4 voices, and was last updated by Profile photo of Alexander Johansson Alexander Johansson 2 years, 1 month ago.

  • Author
    Posts
  • #19638
    Profile photo of Lalit Kumar
    Lalit Kumar
    Participant

    Hi,

    I am new to Powershell and I am trying to retrieve ipconfig /all output in an excel cell. Is it possible to get the "cmd" commands output in excel. Like ipconfig /all, net localgroup administrator. My code is mention below. Kindly help me. I want to learn PowerShell and also suggest me some good books.

    $erroractionpreference = "SilentlyContinue"

    $a = New-Object -comobject Excel.Application
    $a.visible = $True

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

    $c.Cells.Item(1,1) = "IP Address"

    $d = $c.UsedRange
    $d.Interior.ColorIndex = 19
    $d.Font.ColorIndex = 11
    $d.Font.Bold = $True

    $intRow = 2

    $colComputers = get-content C:\list.txt
    foreach ($strComputer in $colComputers)
    {

    $IP = ipconfig /all

    $c.Cells.Item($intRow,2) = $IP

    $intRow = $intRow + 1
    }
    $d.EntireColumn.AutoFit()
    cls

  • #19642
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Frankly, this is the hardest possible way I can think of to get a spreadsheet to contain this data.

    First, I wouldn't use IPConfig, as the output is hard to parse. I would either use native commands like Get-NetAdapter (Get-Command -Module NetAdapt*), or on older systems I might use WMI – like the Win32_NetworkAdapterConfiguration class.

    Second, I'd write a script that queried all the info I needed and then assembled that into a custom object. I could then pipe that to Export-CSV and I'd get my "spreadsheet" without having to screw around with Excel. The resulting CSV would easily open in PowerShell.

    But maybe that's just me.

  • #19665
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    If you're likely at some point to be wanting to get this information from more than one system, and they're all in AD, and you have access to the PS AD cmdlets, you could build a script which gets that information directly from it.

    e.g.

    Import-Module -Name ActiveDirectory
    Get-ADComputer -Identity computername -Properties * | Select-Object  -Property Name, IPv4Address | Export-Csv -Path ad.csv -NoTypeInformation
    
  • #19666
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    Hi!

    I can see that you are using the Excel object model, which limits your script to only run on machines with Excel installed on, I would replace that with the Export-CSV cmdlet.

    Secondly I would not use IPConfig either because as Don Jones stated, the output is hard to parse.

    This code snippet might help get you started:

    Get-Content -Path "C:\list.txt" | ForEach-Object{
    	
    	Get-NetIPAddress -CimSession $_ | Export-Csv -Path "C:\example.csv" -NoTypeInformation -UseCulture
    	
    	}

    Use:

    Select-Object

    to get the properties that you want.

    Note that you need to have WinRM configured on the remote machines for this to work, if it is not enabled you should take a look at:

    Enable-PSRemoting

    either via:

    Get-Help Enable-PSRemoting

    or technical articles and such.

    It also assumes that your list.txt file is containing each of your computername on a new row.

You must be logged in to reply to this topic.