Attempting to output usable CSV from ForEach loop
I have a script that is intended to export all of the domain users along with information like last login, etc. I am developing it for Auditors so it has to come out and not need editing. I usually use "New-Item" and "Add-Content" but some of the items have commas so it throws off the output.
The script takes each account in Domain Admins and checks each domain controller for the most recent lastLogon. The script below is what I have that outputs the information but not correctly. I am sure there is an easier way to do this but I've taught myself so probably missed that item. I tried using arrays and hash tables without success.
Any help would be greatly appreciated.
Import-Module ActiveDirectory
function Get-ADUserLastLogon([string]$userName) { $dcs = Get-ADDomainController -Filter {Name -like "*"} $time = 0 foreach($dc in $dcs) { $hostname = $dc.HostName $user = Get-ADUser $userName -Server $hostname -Properties lastLogon,lastLogonTimestamp,cn,employeeNumber,accountExpires,PasswordNeverExpires,Enabled,LockedOut,pwdLastSet -ErrorAction SilentlyContinue $user.Name If(Test-Connection $dc -Count 1 -ErrorAction SilentlyContinue) { if($user.LastLogon -gt $time) { $time = $user.LastLogon $timeb = $user.lastLogonTimestamp } } } $dt = [DateTime]::FromFileTime($time) $dtb = [DateTime]::FromFileTime($time) $expiration = [DateTime]::FromFileTime($user.accountExpires) $output = $username + "," + $dt + "," + $dtb + "," + $user.cn + "," + $user.employeeNumber + "," + $expiration + "," + $user.PasswordNeverExpires + "," + $user.Enabled + "," + $user.LockedOut + "," + $user.pwdLastSet Add-Content $outputFile $output Write-Host $username "last logged on at:" $dt "or" $dtb }
$users = Get-ADGroupMember "Domain Admins" -Recursive
$outputFile = "\Server\Powershell\Output" + $targetFolder + "DomainAdminLastLogonDateAudit.csv" New-Item $outputFile -ItemType File -Force $header = "Username,lastLogon,lastLogonTimestamp,cn,employeeNumber,accountExpires,PasswordNeverExpires,Enabled,Lockout,pwdLastSet" Add-Content $outputFile $header $userCount = $users.count
ForEach($user in $users) { $user.SamAccountName Get-ADUserLastLogon -UserName $user.SAMAccountName }
Posted Jun 10, 2015, 11:22 PM UTC
try Export-CSV.
Yep, this is the right commandlet to get the job done. I believe by default, Export-CSV adds double quotes as text qualifiers so commas in the data shouldn't affect your columns.
If I read your code right, this might work: $users | Export-CSV -path $outputFile
But if commas are still messing with your output, you can set your delimiter to t for tab delimited: $users | Export-CSV -delimiter "`t" -path $outputFile
You should be able to export directly to an excel spreadsheet as well with Export-Excel.