CODEWITHSUNDEEP
powershellactive-directoryazure-active-directory
M.T Davis
M.T Davis

Reputation: 1098

Exporting PowerShell Results In To CSV for Each User In The Domain That Last Changed Their Password

I have a Powershell script that queries for the pwdLastSet attribute for every user in the Active Directory domain. Essentially, the script determines when each user in the domain last changed their password. However, when I try and output the result using scriptname.ps1 | Export-Csv "filename.csv" it creates the file, however, I'm not getting the results I see in the console. I'm getting the following:

enter image description here

When I run the script without Export-Csv the results I desire display correctly.

This is the Powershell script:

Trap {"Error: $_"; Break;}

$D = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$Domain = [ADSI]"LDAP://$D"
$Searcher = New-Object System.DirectoryServices.DirectorySearcher
$Searcher.PageSize = 200
$Searcher.SearchScope = "subtree"

$Searcher.Filter = "(&(objectCategory=person)(objectClass=user))"
$Searcher.PropertiesToLoad.Add("distinguishedName") > $Null
$Searcher.PropertiesToLoad.Add("pwdLastSet") > $Null
$Searcher.PropertiesToLoad.Add("userAccountControl") > $Null
$Searcher.SearchRoot = "LDAP://" + $Domain.distinguishedName

$Results = $Searcher.FindAll()
ForEach ($Result In $Results)
{
    $DN = $Result.Properties.Item("distinguishedName")
    $PLS = $Result.Properties.Item("pwdLastSet")
    $UAC = $Result.Properties.Item("userAccountControl")
    # Retrieve user password settings to check if password can expire.
    $blnPwdExpires = -not (($UAC.Item(0) -band 64) -or ($UAC.Item(0) -band 65536))
    If ($PLS.Count -eq 0)
    {
        $Date = [DateTime]0
    }
    Else
    {
        # Interpret 64-bit integer as a date.
        $Date = [DateTime]$PLS.Item(0)
    }
    If ($Date -eq 0)
    {
        # 0 really means never.
        $PwdLastSet = "<Never>"
    }
    Else
    {
        # Convert from .NET ticks to Active Directory Integer8 ticks.
        # Also, convert from UTC to local time.
        $PwdLastSet = $Date.AddYears(1600).ToLocalTime()
    }
    "$DN;$blnPwdExpires;$PwdLastSet"
    
}

Upvotes: 1

Views: 1299

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 60518

There are two possible issues on your code, the first one, Export-Csv is expecting an object or object[] as input and will convert it to CSV format, you're already passing a formatted semi-colon delimited string[].

In this case you should use | Out-File path\to\csv.csv instead of Export-Csv.

Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties.

An example of what you're passing to the cmdlet and what it actually expects:

PS \> 0..5 | ForEach-Object{ 'asd;asd;asd' } | ConvertTo-Csv
#TYPE System.String
"Length"
"12"
"12"
"12"
"12"
"12"
"12"

PS \> 0..5 | ForEach-Object{ [pscustomobject]@{col1='asd';col2='asd';col3='asd'} } | ConvertTo-Csv -Delimiter ';'
#TYPE System.Management.Automation.PSCustomObject
"col1";"col2";"col3"
"asd";"asd";"asd"
"asd";"asd";"asd"
"asd";"asd";"asd"
"asd";"asd";"asd"
"asd";"asd";"asd"
"asd";"asd";"asd"

The alternative to this, and cleaner approach in my opinion, would be to cast a [pscustomobject]on each iteration of your loop and then pass the resulting array to Export-Csv (code below).

The other possible issue, assuming you're choosing the path of using [pscustomobject], could be that $Result.Properties.Item(...) will yield an object of the type System.DirectoryServices.ResultPropertyValueCollection and you would need to convert it to [string] before passing the results to Export-Csv (also code below).

# Save the resulting pscustomobject array to the $output variable
$output = ForEach ($Result In $Results)
{
    ...
    ...
    ...
    
    # All code should be as is up until:
    # "$DN;$blnPwdExpires;$PwdLastSet" => Remove this line
    
    [pscustomobject]@{
        DistinguishedName = [string]$DN
        blnPwdExpires = [string]$blnPwdExpires
        pwdLastSet = [string]$PwdLastSet
    }
}

# Then pipe the result to Export-Csv
$output | Export-Csv path\to\csv.csv -NoTypeInformation -Delimiter ';'

Upvotes: 1

Related Questions