Out-GridView vs. Export-Csv

This question is something that came to me recently. It came to me because I have a wonderful script that dumps out server specs to a Out-GridView. The Out-GridView works great for quickly seeing information in a grid. The one down fall I have noticed is when selecting the data from it and copying it to me a program like Excel the header rows do not come with the copy.

Well with the script I talked about earlier I almost always end up viewing the data in Excel. So, I figured why not skip the copy paste to excel step.

$List is the data that has been collected previous to this point that I want to export.

This is how I viewed the data before.

$List | Out-GridView 

Ok, so lets change the output.

$List | Export-Csv ‘c:\scripts\export\export.csv

Well that worked it dumped the information to a csv and I open it in excel it has the headers.

At this point I said why am I opening Excel and then opening it in excel. Lets automate it.

Invoke-Item ‘c:\scripts\export\export.csv

This works great except for some reason there is some file header information. The header information was:

#TYPE System.Management.Automation.PSCustomObject

I found out this is the type of the output and can be removed with the –notype option like this.

Invoke-Item ‘c:\scripts\export\export.csv –noType

This worked nicely but I wanted to clean it up in a couple of ways.

1. Create the directory that the script will go to.
1. Catch the error it if it is already made.
2. Create the file dynamically so that we can keep a history of the files
1. this will be done dynamically with the get-date cmdlet.

This is what I ended up with.

 

$randomString = Get-Date -format M.d.yyyy.HH.mm.ss

 

try{

md "c:\scripts\export\"

}

Catch{

}

 

$csvFileName = 'c:\scripts\export\' + $randomString + '_GetSpecsExport.csv'

 

$csvFileName

 

$List | Export-Csv $csvFileName -noType

 

 

Invoke-Item $csvFileName

 

Here is the whole script for anyone who wants to see it.

 

# Get Server Hardware specs

# 2015 Edward

# CPU / RAM / Disk Size / IP Address

 

 

 

 

##### Set Variables #######

$fileCount = 0

$List = @()

$i = 0

 

 

 

#get the list of servers to scan from serverlist.txt

$serverlist = "C:\scripts\computers.txt"

 

 

 

 

#get the count of servers for updates to the user.

 

$fileCount = (Get-Content $serverlist | Measure-Object).Count

 

 

 

#Loop the server list.

foreach ($server in Get-Content $serverlist) {

 

#increment the number of records proccessed

$i++

 

#Write to the screen whats happening.

Write-Progress -activity "Connecting to server $server" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

 

#do a try if there is an issue spit out the error.

try {

 

    #IF there is an error stop and go to the next record.

    $ErrorActionPreference = 'Stop'

       

    $bios = Get-WmiObject Win32_BIOS -ComputerName $server

    Write-Progress -activity "Scanning server $server -  getting BIOS information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

 

    Write-Progress -activity "Scanning server $server - getting Processor information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $Proc = Get-WmiObject Win32_processor -ComputerName $server | Select-Object -First 1

   

 

    Write-Progress -activity "Scanning server $server - getting Memory information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $memory = Get-WmiObject Win32_physicalmemory -ComputerName $server

   

 

    Write-Progress -activity "Scanning server $server - getting System information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $system= Get-WmiObject Win32_ComputerSystem -ComputerName $server

   

 

    Write-Progress -activity "Scanning server $server - getting Disk information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $disks = Get-WmiObject -class Win32_LogicalDisk -ComputerName $server -Filter {DriveType=3}

   

 

 

    $disklist = " "

    foreach ($disk in $disks) {

        IF ($disklist -eq " "){$putAcomma = ""} ELSE {$putAcomma = ", "}  

        $size = [math]::Round(([int64]$disk.Size / 1073741824))

        $disklist = $disklist + $putAcomma + $disk.DeviceID + " " + [string]$size + " GB"

    }

    Write-Progress -activity "Scanning server $server" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

 

 

    #Set the array for the output.

 

    $List += [pscustomobject]@{

    'ComputerName'         = $server

    'Manufacturer'        = $bios.Manufacturer

    'Model'               = $system.Model

    'BIOS Version'        = $bios.Version

    'Serial Number'       = $bios.SerialNumber

    'Processor Number'    = $system.NumberOfProcessors

    'Processor Name'      = $proc.name

    'CPUs'                 = $system.NumberOfLogicalProcessors

    'Speed (MHZ)'          = $proc.CurrentClockSpeed

    'RAM (GB)'             = $system.TotalPhysicalMemory / 1GB -as [int]

    'Used RAM slot'       = $memory.count

    'DiskList'             = $disklist

    }

 

    } catch {

    $List += [pscustomobject]@{

    'ComputerName'         = $server

    'CPUs'                 = " ERROR "

    'Speed (MHZ)'          = " ERROR "

    'RAM (GB)'             = " ERROR "

    'DiskList'             = $error[0].exception

    }

  }

 

}

 

 

write-progress -activity "Completed."

 

$randomString = Get-Date -format M.d.yyyy.HH.mm.ss

 

try{

md "c:\scripts\export\"

}

Catch{

}

 

$csvFileName = 'c:\scripts\export\' + $randomString + '_GetSpecsExport.csv'

 

$csvFileName

 

$List | Export-Csv $csvFileName -noType

 

 

Invoke-Item $csvFileName

 

Get a list of IPs VIA System.Net.Dns

So needed to look up more IPs via scripts again. The last script I used worked great but didn't grab everything because of the environment I am in. So I took the rest of the systems I needed IPs for and noticed I could use nslookup to get the IPs. Of course nslookup is not made for automation so I went looking again. And again powershell came to the rescue.

In the System.Net.Dns Class.

The script i found came from this website.
http://community.spiceworks.com/scripts/show/1201-powershell-script-dns-forward-lookup-script-with-auto-generate-excel-file

Here is the script from that page. It basically grabs a list of names, or ips from text file you supply and it spits back out into an excel document.. SWEEET...

 ###########################################################################
#
# NAME: DNS Forward Lookup Script with Auto Generate Excel File
#
# AUTHOR: J.Malek (www.malekjakir.com) Email: malek dot one zero four zero at gmail dot com
#
# COMMENT: This script can be used to check list of Servers for Forward NSLookup to get the IP Addresses.
# Line#29 - Please Change the path of Servers.txt to your file location.
# NOTE: One server name per line.
#
# VERSION HISTORY:
# 1.0 2/17/2012 -
#
###########################################################################
$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) = "Server Hostname"
$c.Cells.Item(1,2) = "IP Address"
$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$intRow = 2

$colComputers = get-content "C:\SCRIPTS\DNS_Lookup\ForwardLookup\Servers.txt"
foreach ($strComputer in $colComputers)
{
$FWDIP = [System.Net.Dns]::GetHostAddresses($strComputer) | Add-Member -Name HostName -Value $strComputer -MemberType NoteProperty -PassThru | Select HostName, IPAddressToString

$c.Cells.Item($intRow,1) = $FWDIP.Hostname
$c.Cells.Item($intRow,2) = $FWDIP.IPAddressToString
$intRow = $intRow + 1
}
$d.EntireColumn.AutoFit()
cls
Write-Host "######## This Script is completed now ########"

Thank you internet..

Get IP addresses remotely

So recently had a task to get all the IPs from remote computers. Well it turns out this isn't as easy as it should be. After some major googleing I ended up using a PS script I found here, http://techibee.com/powershell/powershell-get-ip-address-subnet-gateway-dns-serves-and-mac-address-details-of-remote-computer/1367.

Here is the script.

[cmdletbinding()]
param (
 [parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
    [string[]]$ComputerName = $env:computername
)            

begin {}
process {
 foreach ($Computer in $ComputerName) {
  if(Test-Connection -ComputerName $Computer -Count 1 -ea 0) {
   $Networks = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer | ? {$_.IPEnabled}
   foreach ($Network in $Networks) {
    $IPAddress  = $Network.IpAddress[0]
    $SubnetMask  = $Network.IPSubnet[0]
    $DefaultGateway = $Network.DefaultIPGateway
    $DNSServers  = $Network.DNSServerSearchOrder
    $IsDHCPEnabled = $false
    If($network.DHCPEnabled) {
     $IsDHCPEnabled = $true
    }
    $MACAddress  = $Network.MACAddress
    $OutputObj  = New-Object -Type PSObject
    $OutputObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Computer.ToUpper()
    $OutputObj | Add-Member -MemberType NoteProperty -Name IPAddress -Value $IPAddress
    $OutputObj | Add-Member -MemberType NoteProperty -Name SubnetMask -Value $SubnetMask
    $OutputObj | Add-Member -MemberType NoteProperty -Name Gateway -Value $DefaultGateway
    $OutputObj | Add-Member -MemberType NoteProperty -Name IsDHCPEnabled -Value $IsDHCPEnabled
    $OutputObj | Add-Member -MemberType NoteProperty -Name DNSServers -Value $DNSServers
    $OutputObj | Add-Member -MemberType NoteProperty -Name MACAddress -Value $MACAddress
    $OutputObj
   }
  }
 }
}            

end {}

I then used a txt file to input all the servers into the script. This was in the comments sections so I will add it in here..

"get-Content c:\temp\computers.txt | Get-IpDetails.ps1 | ft -auto
where c:\temp\computers.txt is the file that contains computers list."

I used this:
get-Content .\computers.txt | .\Get-IpDetails.ps1 | ft -auto

Also remember that you are running a scriptlet so you need to set this unless you have something else done on your system. 

Set-ExecutionPolicy Unrestricted

Enjoy.