0 votes

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?

by (1.1k points)

1 Answer

0 votes
by (270k points)
selected by
Best answer

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
0

Thanks! Works great!

Any way to remove the time?

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

0

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
0

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?

0

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
0

Works great! Thanks!

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

0

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
}

Related questions

0 votes
1 answer

Hello when I export a report like "Active computers" to XLSX, column Last-Logon-Timestamp will only be recogniced in Excel as text format. Is this a normal ... We have English Windows installation with German Input language, Format and Location. regards Helmut

asked Feb 19, 2021 by a423385 (510 points)
0 votes
1 answer

Hello, Similar to exporting the members of a group to a csv file: https://www.adaxes.com/script-repository/export-group-members-to-csv-file-s184.htm I am looking to ... would like to include the memberof csv report in the email as well. Thanks in advance!

asked Feb 7, 2023 by JonnyBGood (20 points)
0 votes
1 answer

We would like to be able to export logs from the Adaxes service? Is there a way to do this either through the service or reporting?

asked Oct 31, 2022 by scoutcor (120 points)
0 votes
1 answer

So I need to export a list of all user's Line URI's to a CSV file. Running Adaxes 2021 Version 3.14.18804.0 (64 bit) and Teams Powershell 4.1.0 ... a Microsoft 365 account } finally { # Close the connection and release resources Disconnect-MicrosoftTeams }

asked Aug 4, 2022 by TheLexicon (200 points)
0 votes
1 answer

HI i'm looking for a way to export the user details (first, last, start date, dept, mgr) once a user gets created so that it sends the details into a "tasking" sheet for our ... online? If not, can i save them locally as a csv or xls with a custom command?

asked Feb 8, 2021 by bboese (20 points)
3,326 questions
3,026 answers
7,727 comments
544,678 users