Export Users to CSV AccountExpires date problem

Question

I tried the script located here:
http://www.adaxes.com/script-repository ... s-s346.htm

The script works well for most fields (including adm-custom fields), but when I export the field "AccountExpires" I get a value something like "129915720000000000" instead of a date.

Any way to resolve this?

Answer 1

Hello,

You need to update the following line in the script:

$record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $searchResult.Properties[$propertyName].Value

Replace with the block:

$value = $searchResult.Properties[$propertyName].Value
if ($propertyName -eq "accountExpires")
{
    if (($value -eq 0) -or ($value -eq "9223372036854775807"))
    {
        $value = "Never"
    }
    else
    {
        $value = [DateTime]::FromFiletime([Int64]::Parse($value))
    }
}
$record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $value

Comments

Thanks! Works great!

Any way to remove the time?

Also, how do I limit this to run on a specific OU?

---

Hello,

Find the updated script below. You can create a Custom Command configured for the Organizational Unit object type and run it on any of your OUs.
Account Expires will be written into the file without time.

$usersCSVFilePath = "\\Server\share\users.csv" # TODO: modify me
$computerCSVFilePath = "\\Server\share\computers.csv" # TODO: modify me
$dateFormat = "dd/MM/yyyy" # TODO: modify me

function BuildReport($filter, $properties, $containerDN, $dateFormat)
{
    # Find objects in the container
    $searcher = $Context.BindToObjectByDN($containerDN)
    $searcher.SearchFilter = $filter
    $searcher.SearchScope = "ADS_SCOPE_SUBTREE"
    $searcher.PageSize = 500
    $searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
    $searcher.SetPropertiesToLoad($properties)

    try
    {
        $searchResultIterator = $searcher.ExecuteSearch()
        $searchResults = $searchResultIterator.FetchAll()

        foreach ($searchResult in $searchResults)
        {
            # Add object to the CSV file
            $record = New-Object PSObject
            foreach ($propertyName in $properties)
            {
                $value = $searchResult.Properties[$propertyName].Value
                if ($propertyName -eq "accountExpires")
                {
                    if (($value -eq 0) -or ($value -eq "9223372036854775807"))
                    {
                        $value = "Never"
                    }
                    else
                    {
                        $value = ([DateTime]::FromFiletime([Int64]::Parse($value))).ToString($dateFormat)
                    }
                }
                $record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $value
            }
            $record
        }
    }
    finally
    {
        $searchResultIterator.Dispose()
    }
}

# Create CSV file for users
$userReport = BuildReport "(sAMAccountType=805306368)" @("telephoneNumber", "mobile", "ipPhone", "mail", "manager", "distinguishedName", "accountExpires") "%distinguishedName%" $dateFormat
$userReport | Export-Csv -NoTypeInformation -Path $usersCSVFilePath

# Create CSV file for computers
$computerReport = BuildReport "(&(objectCategory=computer)(!(userAccountControl:1.2.840.113556.1.4.803:=8192)))" @("managedBy", "distinguishedName") "%distinguishedName%" $dateFormat
$computerReport | Export-Csv -NoTypeInformation -Path $computerCSVFilePath

---

Thanks! Running on OU worked great!

The time removal partly worked. Some dates came out as DD/MM/YYYY instead of MM/DD/YYYY for some reason. I just removed it for now.

is there a field for AccountIsDisabled that I can add to my CSV?

---

Hello,

Some dates came out as DD/MM/YYYY instead of MM/DD/YYYY for some reason.

The format of the date is defined in the $dateFormat variable. For information on how to update the date format, check the following article: https://msdn.microsoft.com/en-us/librar ... .110).aspx.

is there a field for AccountIsDisabled that I can add to my CSV?

We added the AccountDisabled column to be exported to CSV file. If an account is disabled the value in the column will be True. Here is the updated script:

$usersCSVFilePath = "\\Server\share\users.csv" # TODO: modify me
$computerCSVFilePath = "\\Server\share\computers.csv" # TODO: modify me
$dateFormat = "dd/MM/yyyy" # TODO: modify me

function BuildReport($filter, $properties, $containerDN, $dateFormat)
{
    # Find objects in the container
    $searcher = $Context.BindToObjectByDN($containerDN)
    $searcher.SearchFilter = $filter
    $searcher.SearchScope = "ADS_SCOPE_SUBTREE"
    $searcher.PageSize = 500
    $searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
    $searcher.SetPropertiesToLoad($properties)

    try
    {
        $searchResultIterator = $searcher.ExecuteSearch()
        $searchResults = $searchResultIterator.FetchAll()

        foreach ($searchResult in $searchResults)
        {
            # Add object to the CSV file
            $record = New-Object PSObject
            foreach ($propertyName in $properties)
            {
                $value = $searchResult.Properties[$propertyName].Value
                if ($propertyName -eq "accountExpires")
                {
                    if (($value -eq 0) -or ($value -eq "9223372036854775807"))
                    {
                        $value = "Never"
                    }
                    else
                    {
                        $value = ([DateTime]::FromFiletime([Int64]::Parse($value))).ToString($dateFormat)
                    }
                }
                elseif ($propertyName -eq "userAccountControl")
                {
                    $propertyName = "AccountDisabled"
                    if ($value -band [Softerra.Adaxes.Interop.Adsi.PersistentObjects.ADS_USER_FLAG_ENUM]::ADS_UF_ACCOUNTDISABLE)
                    {
                        $value = "True"
                    }
                    else
                    {
                        $value = "False"
                    }
                }
                $record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $value
            }
            $record
        }
    }
    finally
    {
        $searchResultIterator.Dispose()
    }
}

# Create CSV file for users
$userReport = BuildReport "(sAMAccountType=805306368)" @("telephoneNumber", "mobile", "ipPhone", "mail", "manager", "distinguishedName", "accountExpires", "userAccountControl") "%distinguishedName%" $dateFormat
$userReport | Export-Csv -NoTypeInformation -Path $usersCSVFilePath

# Create CSV file for computers
$computerReport = BuildReport "(&(objectCategory=computer)(!(userAccountControl:1.2.840.113556.1.4.803:=8192)))" @("managedBy", "distinguishedName", "userAccountControl") "%distinguishedName%" $dateFormat
$computerReport | Export-Csv -NoTypeInformation -Path $computerCSVFilePath

---

Works great! Thanks!

Any chance I can get a version that I can run against Business Units?

---

Hello,
Find an updated script below. In the script, $businessUnitName specifies the name of the Business Unit members of which should be exported to CSV.

If you have any other requirements regarding this script, please mention all of them.

$businessUnitName = "My Business Unit" # TODO: modify me
$usersCSVFilePath = "\\Server\share\users.csv" # TODO: modify me
$computerCSVFilePath = "\\Server\share\computers.csv" # TODO: modify me
$dateFormat = "dd/MM/yyyy" # TODO: modify me

function BuildReport($filter, $properties, $dateFormat)
{
    # Find objects in the container
    $searcher = $Context.BindToObject("Adaxes://RootDSE")
    $searcher.SearchFilter = $filter
    $searcher.SearchScope = "ADS_SCOPE_SUBTREE"
    $searcher.PageSize = 500
    $searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
    $searcher.SetPropertiesToLoad($properties)
    $searcher.VirtualRoot = $True

    try
    {
        $searchResultIterator = $searcher.ExecuteSearch()
        $searchResults = $searchResultIterator.FetchAll()

        foreach ($searchResult in $searchResults)
        {
            # Add object to the CSV file
            $record = New-Object PSObject
            foreach ($propertyName in $properties)
            {
                $value = $searchResult.Properties[$propertyName].Value
                if ($propertyName -eq "accountExpires")
                {
                    if (($value -eq 0) -or ($value -eq "9223372036854775807"))
                    {
                        $value = "Never"
                    }
                    else
                    {
                        $value = ([DateTime]::FromFiletime([Int64]::Parse($value))).ToString($dateFormat)
                    }
                }
                elseif ($propertyName -eq "userAccountControl")
                {
                    $propertyName = "AccountDisabled"
                    if ($value -band [Softerra.Adaxes.Interop.Adsi.PersistentObjects.ADS_USER_FLAG_ENUM]::ADS_UF_ACCOUNTDISABLE)
                    {
                        $value = "True"
                    }
                    else
                    {
                        $value = "False"
                    }
                }
                $record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $value
            }
            $record
        }
    }
    finally
    {
        $searchResultIterator.Dispose()
    }
}

# Find the Business Unit
$businessUnitsPath = $Context.GetWellKnownContainerPath("BusinessUnits")
$searcher = $Context.BindToObject($businessUnitsPath)
$searcher.SearchFilter = "(&(objectCategory=adm-BusinessUnit)(name=$businessUnitName))"
$searcher.PageSize = 500
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"

try
{
    $searchResult = $searcher.ExecuteSearch()
    $objects = $searchResult.FetchAll()

    if ($objects.Length -gt 1)
    {
        $Context.LogMessage("Found more than one Business Unit with name '$businessUnitName'.", "Warning")
        return
    }
    if ($objects.Length -eq 0)
    {
        $Context.LogMessage("Business Unit '$businessUnitName' does not exist.", "Error")
        return
    }

    # Get the Business Unit Members
    $unit = $Context.BindToObject($objects[0].AdsPath)
}
finally
{
    $searchResult.Dispose()
}

$membershipRules = $unit.GetMembershipRules()
$memberGuidsBytes = $unit.GetMemberGuids($membershipRules)

$filter = New-Object "System.Text.StringBuilder"
$filter.Append("(|") | Out-Null
foreach ($guidBytes in $memberGuidsBytes)
{
    $filterPart = [Softerra.Adaxes.Ldap.FilterBuilder]::Create("ObjectGuid", $guidBytes)
    $filter.Append($filterPart) | Out-Null
}
$filter.Append(")") | Out-Null

# Create CSV file for users
$userFilter = "(&(sAMAccountType=805306368)" + $filter.ToString() + ")"
$userReport = BuildReport $userFilter @("telephoneNumber", "mobile", "ipPhone", "mail", "manager", "distinguishedName", "accountExpires", "userAccountControl") $dateFormat
if ($userReport)
{
    $userReport | Export-Csv -NoTypeInformation -Path $usersCSVFilePath
}

# Create CSV file for computers
$computerFilter = "(&(objectCategory=computer)(!(userAccountControl:1.2.840.113556.1.4.803:=8192))" + $filter.ToString() + ")"
$computerReport = BuildReport $computerFilter @("managedBy", "distinguishedName", "userAccountControl") $dateFormat
if ($computerReport)
{
    $computerReport | Export-Csv -NoTypeInformation -Path $computerCSVFilePath
}