2 CSV files - combine into one CSV

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 7:54 am

You can avoid concatenating files by doing it this way:
PowerShell Code
Double-click the code block to select all.
# Block of code for Remote calls
$Block = { 
    $OperatingSystem = gwmi win32_operatingsystem 
    $ComputerSystem = gwmi win32_computersystem 
    $TimeZone = gwmi Win32_TimeZone 
    $NIC=gwmi win32_NetworkAdapterConfiguration -Filter 'IPEnabled=True' | 
        ForEach-Object{[string::Join('|',$_.IPAddress)}
    $Properties=@{ 
        Name = $OperatingSystem.CSName 
        OperatingSystem = $OperatingSystem.Caption
        ServicePack= $OperatingSystem.CSDVersion 
        PhysicalMemory = $ComputerSystem.TotalPhysicalMemory
        NumberOfProcessors = $ComputerSystem.NumberOfProcessors
        LastBootTIme = $OperatingSystem.ConvertToDateTime($OperatingSystem.LastBootupTime) 
        IPAddress = $NIC.IPAddress 
        TimeZone = $TimeZone.Caption 
    }

    New-Object PsObject -Property $Properties

}

# Retrieve most recent list of Computers from AD
$sessions=Get-ADComputer -Filter *|
    ForEach-Object{
        new-pssession -ComputerName $_.Name
}

# Retrieve most recent list of Computers from AD
$sessions+=Get-QADComputer -searchroot 'site B name'|
    ForEach-Object{
        new-pssession -ComputerName $_.Name
}

Invoke-Command -ScriptBlock $block -Session $sessions | 
    Export-Csv $InventoryCSVb -NoTypeInformation 

$sessions | Remove-PSSession
All sessions are combined first then added. With a little bit more stuff you can extract the site into the CSV file.

User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 8:44 am

Note that you can also get all computers in the forest this way:
PowerShell Code
Double-click the code block to select all.
$forest=''DC=forestroot,DC=com'
get-qadcomputer -searchRoot $forest -SizeLimit 0 -UseGlobalCatalog

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Wed Oct 02, 2013 11:02 am

Thank you for your help - truly appreciated.

Ill try it, though there was a method to my madness. :) The servers pulled from AD include decommissioned servers and new server objects, not on the network - WMI yet. New-pssession strips the servers from the csv if they arent on the network.

User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 12:23 pm

juneb1022 wrote:Thank you for your help - truly appreciated.

Ill try it, though there was a method to my madness. :) The servers pulled from AD include decommissioned servers and new server objects, not on the network - WMI yet. New-pssession strips the servers from the csv if they arent on the network.
No it doesn't.

How can you access them if they are not on the network. You are querying AD for the names.

You are not being very clear about what you are trying to do. I thought you wanted to know how to add two CSV files together.

User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 12:25 pm

juneb1022 wrote:Thank you for your help - truly appreciated.

Ill try it, though there was a method to my madness. :) The servers pulled from AD include decommissioned servers and new server objects, not on the network - WMI yet. New-pssession strips the servers from the csv if they arent on the network.
Are you saying that you want a record in the file for servers that don't exist? We have to use a completely different method to do that but it only takes another two lines.

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Wed Oct 02, 2013 2:04 pm

I need the servername, description, and managedby column from AD, and the rest of the columns from WMI (cpu, mem, timezone, ip, etc) into 1 spreadsheet. If there are servers in AD that are not on the network yet - these need to be included in the list too.
Im trying to get the code to work without conactentating files.

User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 5:17 pm

Very, very simple:
Ping the servers and generate a dummy record whenever the server is missing.

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Thu Oct 03, 2013 6:47 am

Ping is disabled in our network, I'd have to use DNS.

User avatar
jvierra
Posts: 13790
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Thu Oct 03, 2013 7:25 am

DNS cannot help you test if a machine is available.

You will need to trap on the error.

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Thu Oct 03, 2013 3:17 pm

jvierra,


Is there any way to get this to work faster? Quering AD then creating the remote sessions is too slow. 15 minutes at 20% completion is not good. I removed the ones I know that are cluster resources,disabled, and decommissioning.

# Retrieve most recent list of Computers from AD
$sessions = Get-QADComputer -searchroot 'sitename' | `
Where-Object { $_.description -notlike "ClusterResource*" -and $_.description -notlike "Disaster*" -and $_.accountisdisabled -eq $False -and $_.description -notlike "*Decom?" } |
ForEach-Object { new-pssession -ComputerName $_.Name
}

Locked