Monday, 16 March 2015

Powershell output handling using CSV

How to export powershell output to CSV - Using hash table ?


           We have many ways to export a PowerShell output to CSV format, and the simple one is just using Expoer-CSV which is familiar to all... Below is the method exporting a powershell output to CSV format by using hash table. Here we are updating (Appending) hash table from a For-each loop and exporting the result to CSV format. At the end using Expoer-CSV to export the hash table values.Benefit is to make out result as per our customized way.

That's all I am trying here...

Scenario : Just pulling few details of a grop of machines

Technet - Using export-csv

001
Get-Process | export-csv C:\vivek\process.csv

Passing the result to an hash table and exporting to CSV

If we want to export a customized powershell output to CSV file, we can use a hash table to do that. Here I am giving a scenario based example for creating a csv output using hash table.

Scenario : Pinging a group of system and exporting the result to csv file.And also checking the Operating system name and RAM size of the system,

1. Creating an empty hash table to store the result and Getting Machine Name from a .txt file located on our desktop.
You can use your own way to input the system details.

001
002
003
004
$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{

2. Ping checking (Offline) -  This script portion will start based on ping result.If the ping result is false, it just create an hash table with predefined content.And if the result is true, there are some wmi query to check the system details.

Forming hash table and adding the values to PSObject based on ping result
Using [ordered]@ table to keep the order as we given

001
002
003
004
005
006
007
008
009
010
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
$Result = [ordered]@{
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE OFFLINE"
OS_NAME = "N/A"      
TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}

We have to create a PSObject to store and append the hash table values. After the for-each loop, we will be exporting this psobject to comma separate value (csv) 

001
$Details += New-Object PSObject -Property $Result

3. Ping Checking (Online) -  As I mentioned previously, this is the true portion of ping result. If the ping result is true, we are checking the OS_NAME and TOTAL PHYSICALMEMORY of the system. 

001
002
003
004
005
006
$osname = "N/A"
$physicalmemory = "N/A"
$sysdetails = "N/A"
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

Forming hash table and adding the values to PSObject based on ping result

001
002
003
004
005
006
007
$Result = [ordered]@{  
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE ONLINE"
OS_NAME = "$OSNAME"
TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
$Details += New-Object PSObject -Property $Result

At the end exporting all the result to .CSV file. I just created a location path with a customized name. You can give anything which you like

001
002
003
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation

Output will looks like below  :)












Now putting all the script together :

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
#Machine availability check
#Handling Output using CSV

$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
    $Result = [ordered]@{
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE OFFLINE"
    OS_NAME = "N/A"      
    TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}
else
{
$osname = $N
$physicalmemory = "N/A"
$sysdetails = ""
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

    $Result = [ordered]@{  
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE ONLINE"
    OS_NAME = "$OSNAME"
    TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
    $Details += New-Object PSObject -Property $Result
}
}
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation


Download Script

No comments:

Post a Comment