Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET
PowerShell

PowerShell Problem Solver: Fun with CSV Imported Objects

The last several PowerShell Problem Solver articles have sprung from a need to update a CSV file that contains computer information with IP addresses. I’ve been using this scenario to demonstrate a number of PowerShell commands like Import-CSV, Export-CSV and Test-Connection.

In this article, we’re going to pick up where we left off, and that includes exploring how to think about this process with objects instead of text in a file. As with many of my article series, take a few minutes to get caught up on the previous articles if you’re just joining us.

Today, I’m going to use a copy of my computers.csv file that has some IP information already entered, and I know the addresses are valid.

​
I'm going to import this CSV into my PowerShell session.
​
You should know by now that $computers is a collection of custom objects.

Displaying an imported object (Image Credit: Jeff Hicks)
Displaying an imported object (Image Credit: Jeff Hicks)
Some of these objects will have an empty IPAddress property. As I showed last time, I can use a regular expression pattern to skip objects with an IP address and use Test-Connection to get the IP address for those that are lacking.
​


I inserted a Write-Host command because you wouldn't know what is happening, otherwise.

Updating objects with an IP address (Image Credit: Jeff Hicks)
Updating objects with an IP address (Image Credit: Jeff Hicks)
Now look at what we have in $computers.
Updated objects (Image Credit: Jeff Hicks)
Updated objects (Image Credit: Jeff Hicks)
An added benefit is that I can also update individual objects.
​
Here you can see the before and after.

Updating a single object (Image Credit: Jeff Hicks)
Updating a single object (Image Credit: Jeff Hicks)
Or perhaps I want to normalize some of the property values. I like computer names to be in upper case. They are not in my CSV file and imported objects. But that is easy to correct.
​
I can display the objects in $computers using the PowerShell cmdlets you already know.

Displaying sorted and formatted results. (Image Credit: Jeff Hicks)
Displaying sorted and formatted results. (Image Credit: Jeff Hicks)
When I'm ready, I can export whatever results I want CSV files.
​
When you are working in PowerShell, you should always be thinking about how you can use and re-use what you have. Once $computers has all the data I want, I easily created three different CSV files from the same information. I could also have exported $computers back to the original file, overwriting it with updated information.

Given everything I've shown you, there are a few caveats. There is an assumption that each server only has a single IP address. If that is not the case, then exporting to a CSV will be problematic. Suppose one of the computer entries looks like this:

A multi-value property (Image Credit: Jeff Hicks)
A multi-value property (Image Credit: Jeff Hicks)
The IPAddress property is an array or collection. When I attempt to turn $computers into a CSV file, I'm going to get an unexpected result.
An exported CSV with a multiple value property (Image Credit: Jeff Hicks)
An exported CSV with a multiple value property (Image Credit: Jeff Hicks)
I used ConvertTo-CSV to make it easier to demonstrate. The result would be the same using Export-CSV. If you have many properties like this, then a CSV file is the wrong format and you should switch to XML, which will preserve everything for you.
Exporting to an XML format (Image Credit: Jeff Hicks)
Exporting to an XML format (Image Credit: Jeff Hicks)
Otherwise, your option is to create a single string for all the IP addresses like this:
​
Now when you convert or export to CSV, you don't lose anything.

Joining multiple values together into a single string (Image Credit: Jeff Hicks)
Joining multiple values together into a single string (Image Credit: Jeff Hicks)
The other potential issue is that the computer must be configured to respond to a ping, otherwise Test-Connection will fail, even if the computer is up and running. But there are a few other name resolution tricks you could use and I'll cover those in a wrap-up article to this scenario.

Sponsored Content

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

Related Topics:

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (0)

Leave a Reply

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: