r/PowerShell•
harthin

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

53
supermamon•

try Export-CSV.

3
mikorun•

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.

1
OfficialXave•

OK, that's kinda quickly done, just to see if it's feasible that way but I've got that:

$domainControllers = Get-ADDomainController -Filter * 

Get-ADGroupMember -Identity 'Domain Admins' -Recursive | ForEach-Object -Process {
    $user = $_
    $domainControllers |
    ForEach-Object -Process {
        Get-ADUser $user.SamAccountName -Server $_.Name -Properties lastLogon, cn, employeeNumber, accountExpires, PasswordNeverExpires, Enabled, LockedOut, pwdLastSet
    } |
    Sort-Object -Property LastLogon |
    Select-Object -Last 1 -Property SamAccountName, 
                            @{ name = 'LastLogon' ; expression = { [DateTime]::FromFileTime($_.LastLogon) } }, 
                            cn, employeeNumber, 
                            @{ name = 'accountExpires' ; expression = { [DateTime]::FromFileTime($_.accountExpires) } },
                            PasswordNeverExpires, Enabled, LockedOut, 
                            @{ name = 'pwdLastSet' ; expression = { [DateTime]::FromFileTime($_.pwdLastSet) } }
} | Export-Csv .\banana.csv

Basically, I create a list of domain controllers (I could have put it in the loop, but there's no point retrieving the list several time.

Then the loop: for each member of "Domain Admins", you interrogate every domain controller for the relevant information. You sort those by LastLogon and select only the last one, and you output the properties you want. Time to send everything to the CSV file.

Note: you don't care about LastLogonTimeStamp. It's a replicated value that can be off by up to four weeks. Its purpose is to offer a consistent value on every domain controller, but it shouldn't be used unless you're hunting accounts that haven't been used for months. LastLogon on the other hand, is the last time the user logged on this particular domain controller, hence the loop where you interrogate every DC, sort your result by that info, and keep only the last (most recent) one.

And the select makes use of the mighty calculated properties to convert all those weird time formats to something you can read.

1