I’m learning Powershell. I got the code below from a tutorial. It works fine as it is but I’m having a hard time getting the results in CSV format with Export-csv.

I’ve tried “”“$dn”“,$sam,$lastLogon” | Export-Csv $exportPath at the end of the script but I just get a csv file with 3 lines as follows.

#TYPE System.String
Length
98

Any help will be appreciated.

Julio

$Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$ADSearch = New-Object System.DirectoryServices.DirectorySearcher
$ADSearch.PageSize = 100
$ADSearch.SearchScope = "subtree"
$ADSearch.SearchRoot = "LDAP://$Domain"
$arrResults = @()
$exportPath = "C:\LastLogon.csv"
$ADSearch.Filter = "(objectClass=user)"

$ADSearch.PropertiesToLoad.Add("distinguishedName")
$ADSearch.PropertiesToLoad.Add("sAMAccountName")
$ADSearch.PropertiesToLoad.Add("lastLogonTimeStamp")

$userObjects = $ADSearch.FindAll()
foreach ($user in $userObjects)
{
    $dn = $user.Properties.Item("distinguishedName")
    $sam = $user.Properties.Item("sAMAccountName")
    $logon = $user.Properties.Item("lastLogonTimeStamp")
    if($logon.Count -eq 0)
    {
    $lastLogon = "Never"
    }
    else
    {
        $lastLogon = [DateTime]$logon[0]
        $lastLogon = $lastLogon.AddYears(1600)
    }

	"""$dn"",$sam,$lastLogon"
 }
4 Spice ups

That’s because you’re using Write-Host to output your data, essentially [string] data. Cmdlet’s in PowerShell require objects. So you either have to use Select to create your object or use the New-Object cmdlet. In this case I think New-Object is more appropriate (and would require less recoding!)

New-Object PSObject -Property @{
   DistinguishedName = $dn
   SamAccountName = $sam
   LastLogon = $lastLogon
}

You would replace line 30 with the above.

Now when you run your script you’ll get object output, so:

.\myscript.ps1 | Export-CSV myoutput.csv -NoTypeInformation

Now, if you wanted it to all be done within the script, you could do this. Replace line 15 with:

$Results = ForEach ($User in $userObjects)

Then at the very bottom (after line 31) you could:

$Result | Select DistinguishedName,SamAccountName,LastLogon | Export-CSV myOutput.csv -NoTypeInformation

Notice I used Select here, in PowerShell 2.0 when you create an object it doesn’t order the properties properly, so they could come in any random order (lastlogon, then distinguishedName then SamAccountName–and the next time you run it it’d be in ANOTHER order!) so Select takes care of that.

In PowerShell 3.0 we have it better, instead of using the New-Object cmdlet, we can just use the [PSCustomObject] type accelerator:

[PSCustomObject]@{
   DistinguishedName = $dn
   SamAccountName = $sam
   LastLogon = $lastLogon
}

You’ll also see people using [ordered] but they’re the same thing (at least I think they are). What’s nice about this is it will keep the properties in the right order for you. It’s also faster then New-Object, which is nice. Of course, we’re talking thousands of milliseconds so it hardly matters :slight_smile:

Right. The general rule for hash tables (the @{key=value} structure) is that the stored order of the Key/Value pairs is not related to the order they are added to the table. However, in the special case New-Object PSObject -Property @{hashtable} (and the V3+ [PSCustomObject]@{hashtable} accelerator), the PowerShell developers did some magic so that the defined order is retained, but the hash table used for that input that way isn’t itself saved.

Where the [ordered] confusion comes into play is that they also added a new ordered hash table object type in V3 that is persistent within a PS session. The [ordered]@{hashtable} retains the sequence of objects added to it when you process it through the GetEnumerator() method, which is pretty cool when you want to export it to Excel either directly or through a csv file. You can get into some pretty bizarre object structures by nesting ordered and unordered hash tables.

1 Spice up

OK, that makes sense, I had seen them used in the same AND different instances and it was a bit confusing. But that makes sense now. Thanks Art.

Martin,

Thank you for your help. I’ve modified the script as per your instructions. I’m not sure if I’ve made a mistake. Instead of getting DistinguishedName and SamAccountName values, I get “System.DirectoryServices.ResultPropertyValueCollection” for each line. The LastLogon values show up fine in the csv file. Here is the code:

$Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$ADSearch = New-Object System.DirectoryServices.DirectorySearcher
$ADSearch.PageSize = 100
$ADSearch.SearchScope = "subtree"
$ADSearch.SearchRoot = "LDAP://$Domain"
#$arrResults = @()
$exportPath = "C:\LastLogon.csv"
$ADSearch.Filter = "(objectClass=user)"

$ADSearch.PropertiesToLoad.Add("distinguishedName")
$ADSearch.PropertiesToLoad.Add("sAMAccountName")
$ADSearch.PropertiesToLoad.Add("lastLogonTimeStamp")

$userObjects = $ADSearch.FindAll()

$Results = ForEach ($User in $userObjects)
{
    $dn = $user.Properties.Item("distinguishedName")
    $sam = $user.Properties.Item("sAMAccountName")
    $logon = $user.Properties.Item("lastLogonTimeStamp")
    	
	if($logon.Count -eq 0)
    {
    $lastLogon = "Never"
    }
    else
    {
        $lastLogon = [DateTime]$logon[0]
        $lastLogon = $lastLogon.AddYears(1600)
    }

	New-Object PSObject -Property @{
    DistinguishedName = $dn
    SamAccountName = $sam
    LastLogon = $lastLogon
    }   
}
$Results | Select DistinguishedName,SamAccountName,LastLogon | Export-CSV $exportPath -NoTypeInformation

Good old ADSI, one of the reasons I don’t like working with it :slight_smile:

	New-Object PSObject -Property @{
        DistinguishedName = $dn[0]
        SamAccountName = $sam[0]
        LastLogon = $lastLogon
    }

Martin, thank you very much. It works great. I still need to learn a lot about Powershell.

Julio

1 Spice up