Hi all,
I’m stuck on trying to fix a script when I try to output the content to CSV by using Export-CSV.
This is the script in question:
<#
.SYNOPSIS
Get-ADComputerQuery retrieves the computername and logged on user for all active AD Computers found in the requested OU.
.DESCRIPTION
Get-ADComputerQuery retrieves the computername and logged on user for all active AD Computers found in the requested OU.
It uses the PowerShell Active Directory module to query Active Directory for all ADComputer objects
and then uses WMI to query each machine for it’s currently logged on User.
.PARAMETER Searchbase
This is the LDAP location of the OU you would like to query
For example CN=Computers,DC=CONTOSO,DC=com
,PARAMETER ErrorLog
Here you can define the location of error logfile created
.PARAMETER LogErrors
This switch parameter defines if you want to log errors or not.
If you set this parameter, it will automatically log errors to the file located in the $ErrorLog parameter
.EXAMPLE
Get-ADComputerQuery -SearchBase “CN=Computers,DC=CONTOSO,DC=com” -LogErrors | Out-Gridview
This will Query the computers in the CN=Computers,DC=CONTOSO,DC=com OU, logs all errors to the default location and displays the result in Gridview.
.EXAMPLE
Get-ADComputerQuery -SearchBase “CN=Computers,DC=CONTOSO,DC=com” | Export-CSV -NoTypeInformation .\Output.csv
This will Query the computers in the the CN=Computers,DC=CONTOSO,DC=com OU and exports the information to a CSV file called Output.csv.
#>
[CmdletBinding()]
Param (
[Parameter(Mandatory=$true,
HelpMessage=‘Enter the LDAP OU location you want to have searched’)]
[string]$SearchBase,
[string]$ErrorLog = ".\Get-ADComputerQuery_ErrorLog-$(((get-date).ToUniversalTime()).ToString('ddMMyyyyThhmm')).txt",
[switch]$LogErrors
)
BEGIN {
Write-Verbose “Errors will be written to the logfile located at $ErrorLog”
# Imports the required ActiveDirectory module. If this step fails, all other steps won't work and the script will stop running.
Write-Verbose 'Adding the Active Directory PowerShell module required to query'
Import-Module ActiveDirectory -ErrorAction Stop
# Create an error log when LogErrors is enabled
if ($LogErrors) {
New-Item -Path $ErrorLog -Type File -Force
Add-Content -Path $ErrorLog -Value "ComputerName,LastLogonDateDT `n"
Write-Verbose "Log file created called $ErrorLog"
}
}
PROCESS {
Write-Verbose “Obtaining all AD Computers from provided OU: $SearchBase”
$ADComputers = Get-ADComputer -Filter * -SearchBase $SearchBase -Properties Name,OperatingSystem,OperatingSystemServicePack,LastLogonTimestamp,DistinguishedName
foreach ($ADComputer in $ADComputers){
$ADComputerName = $ADComputer.name
# Convert LastLogonTimestamp to a readable format
$Time = $ADComputer.LastLogonTimestamp
$LastLogon = [DateTime]::FromFileTime($Time)
Write-Verbose "Querying computer $ADComputerName"
# Query Computer through WMI. If this fails, write computername to error log and disregard other queries.
Try {
$Everything_ok = $true
$CS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $ADComputerName -ErrorAction Stop
}
Catch {
$Everything_ok = $false
Write-Warning "$ADComputerName failed"
if ($LogErrors) {
$ADComputerName + ',' + $LastLogon | Out-File -FilePath $ErrorLog -Append -Encoding ascii
Write-Warning "Error logged to $ErrorLog"
}
}
if ($Everything_ok) {
$CSP = Get-WmiObject -Class Win32_ComputerSystemProduct -ComputerName $ADComputerName
# Create new set of properties based on retrieved information
$properties = @{'ComputerName'=$ADComputerName;
'UserName'=$CS.UserName;
'LastLogonDT'=$LastLogon;
'OperatingSystem'=$ADComputer.OperatingSystem;
'ServicePack'=$ADComputer.OperatingSystemServicePack;
'DistinguishedName'=$ADComputer.DistinguishedName
'Vendor'=$CS.Manufacturer;
'Model'=$CS.Model;
'SerialNumber'=$CSP.IdentifyingNumber}
Write-Verbose 'WMI Queries completed'
# Create a new object containing set of properties
$obj = New-Object -TypeName psobject -Property $properties
# Outputting newly created object [this output can be piped to commands such as Export-CSV for example]
Write-Output $obj
# Outputting newly created object to graphical view
# $obj | Out-GridView
}
}
}
END {}
When I use this as an example:
Get-ADComputerQuery -SearchBase “CN=Computers,DC=CONTOSO,DC=com” | Export-CSV -NoTypeInformation .\Output.csv
I get a .csv file, but this seems to contain the following “mess”:
“PSPath”,“PSParentPath”,“PSChildName”,“PSDrive”,“PSProvider”,“PSIsContainer”,“VersionInfo”,“BaseName”,“Mode”,“Name”,“Length”,“DirectoryName”,“Directory”,“IsReadOnly”,“Exists”,“FullName”,“Extension”,“CreationTime”,“CreationTimeUtc”,“LastAccessTime”,“LastAccessTimeUtc”,“LastWriteTime”,“LastWriteTimeUtc”,“Attributes”
“Microsoft.PowerShell.Core\FileSystem::C:\users\administrator.PICTORIGHT\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt”,“Microsoft.PowerShell.Core\FileSystem::C:\users\administrator.PICTORIGHT\documents”,“Get-ADComputerQuery_ErrorLog-02052014T1150.txt”,“C”,“Microsoft.PowerShell.Core\FileSystem”,“False”,“File: C:\users\administrator.PICTORIGHT\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt
InternalName:
OriginalFilename:
FileVersion:
FileDescription:
Product:
ProductVersion:
Debug: False
Patched: False
PreRelease: False
PrivateBuild: False
SpecialBuild: False
Language:
“,“Get-ADComputerQuery_ErrorLog-02052014T1150”,”-a—”,“Get-ADComputerQuery_ErrorLog-02052014T1150.txt”,“0”,“C:\users\administrator.CONTOSO\documents”,“C:\users\administrator.CONTOSO\documents”,“False”,“True”,“C:\users\administrator.CONTOSO\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt”,“.txt”,“5/2/2014 1:50:45 PM”,“5/2/2014 11:50:45 AM”,“5/2/2014 1:50:45 PM”,“5/2/2014 11:50:45 AM”,“5/2/2014 1:50:45 PM”,“5/2/2014 11:50:45 AM”,“Archive”
,
,
,
,
,
,
,
,
,
When I run the script without the Export-CSV everything works just fine…
Any tips on what’s causing this behaviour?
And while I’m at it, any tips on how I can convert this to a WorkFlow so I can run this parallel [I understand that’s a whole different question, but perhaps someone knows the answer to both 
]
Kind regards
Robert