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.
- Create the directory that the script will go to.
- Catch the error it if it is already made.
- Create the file dynamically so that we can keep a history of the files
- 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
