I'm having some trouble with the output from the Get-ADComputer command when exporting it to CSV. Most entries look fine, like this:
mot.sz0144/WinServ Backup Server/Z001S001LBS01 [10.1.1.71](http://10.1.1.71) 10/7/2023 15:52,
but some entries are breaking into two lines. For example:
"mot.sz0144/Site Containers Hosts/z001s001rfe02
CNF:65a00800-62ab-4225-923d-619d0080371c" [10.101.1.229](http://10.101.1.229) 10/23/2023 10:30.
When I open the CSV in Excel, everything looks great, but the output I'm trying to parse comes out in two lines instead of one. Here's the command I'm using:
Get-ADComputer -Filter * -properties canonicalname,IPv4Address,LastLogonDate | select canonicalname,IPv4Address,LastLogonDate | Export-CSV c:computers_by_OU.csv -NoTypeInformation. Any tips on how to fix this?
4 Answers
It sounds like you're dealing with some special characters in your CN, possibly slashes or line breaks that are causing the output to split. Instead of using the default export, have you considered the Import-Excel module? It allows a lot more control over formatting and can handle special characters better. If you can't install additional modules, try running this command:
`Get-ADComputer -Filter * -Properties canonicalname, IPv4Address, LastLogonDate | Select-Object @{Name='canonicalname'; Expression={$_.canonicalname -replace "[rn]", ""}}, IPv4Address, LastLogonDate | Export-CSV c:computers_by_OU.csv -NoTypeInformation`
This should help clean up those problematic lines!
Those CNF:* objects pop up when conflicting updates happen on different AD controllers. It might be worth looking into the infrastructure if these 'problem' lines all relate to devices not seen in over a year. There could be some underlying issues there.
That's interesting! I hadn't made that connection yet.
I think the issue stems from the CN containing a CR or LF in some cases. You might want to tweak your parsing to handle these line breaks better. A clever regex could help replace those before exporting, or you might look for a CSV parser that can manage multiline content seamlessly. It's worth a try!
Have you tried using `Import-Csv` when reading the output? It might streamline how you're parsing this data rather than relying on the default methods. Just a thought!
Thanks for the suggestion! I'll definitely give that a shot.