ExcelList Lookup

Question

Is it possible to assign free phone numbers automatically to the phone number field, when we create a user from a excel list and update the list when the user left the organization?

Comments

Hello,

Sorry for the confusion, but we are not sure what exactly you mean. Could you, please, describe the desired workflow in all the possible details with live examples?

---

I have a list of phone number from a range of say 000-999, when a new employee joins he gets the number which is free from the range and when an employee leaves the organization, the list gets updated , as the given number has to be set to be free. The list is currently maintained in excel and it is a manual work when we create a user in AD, by looking the excel list for available free numbers and assign the user. And when the user leaves we set the number as free manually in the excel list. Is there a way in which we can automate this process. I can even create a list somewhere else in Adaxes, just not sure how?

Thanks alot

---

Hello,

Is there a way in which we can automate this process.

Yes, it is possible using scripts in your provisioning and deprovisioning processes. The first script will pick the first number from the file, set it for the user and remove from the file. The other script will do the opposite during user deprovisioning. Would it be convenient for the list to be stored in a CSV file rather then Excel as it is more dedicated to work with using scripts?

I can even create a list somewhere else in Adaxes, just not sure how?

Unfortunately, there is no possibility to store the list in Adaxes so that it can be viewed\modified without using scripts.

---

Thank you very much, i would try doing it, do u have a sample script for it which i could modify as per my needs.

Thanks very much for your support.

---

Hello,

Unfortunately, we do not have such a script, but we will write it for you. Please, send us (support@adaxes.com) a sample CSV file to be used.

Answer 1

Hello,

Thank you for all the provided details. Below are the two scripts to fulfil the requirements.

Script for new user creation

The script should be executed in a business rule triggering After creating a user. In the script:

• $csvFilePath – Specifies the path to the CSV file.
• $userNamePropertyName – Specifies the LDAP name of the property whose value will be added to the User column of the CSV file.
• $telephonePropertyName – Specifies the LDAP name of the property that will be updated with the value of the phone number taken from the CSV file.

$csvFilePath = "\\Server\Share\PhoneList.csv" # TODO: modify me
$userNamePropertyName = "samaccountName" # TODO: modify me
$telephonePropertyName = "telephoneNumber" # TODO: modify me

# Import CSV
$records = import-csv $csvFilePath

# Get user name
try
{
    $username = $Context.TargetObject.Get($userNamePropertyName)    
}
catch
{
    $Context.LogMessage("User %fullname% has no name specified in property $userNamePropertyName.", "Warning")
    return
}

$phoneNumerbFounded = $False
foreach ($record in $records)
{
    if (![System.String]::IsNullOrEmpty($record.User))
    {
        continue
    }

    # Update the user
    $Context.TargetObject.Put($telephonePropertyName, $record."Phone Number")
    $Context.TargetObject.SetInfo()

    $record.User = $username
    $record."Assigned Date" = "%whenCreated:format[dd.MM.yyyy]%"
    $phoneNumberFounded = $True
    break
}

if (!$phoneNumberFounded)
{
    $Context.LogMessage("No available phone number found", "Warning")
    return
}

# Update the CSV file
$records | Export-Csv -Path $csvFilePath -Force -NoTypeInformation

Script for user deprovisioning

The script be executed as part of your deprovisioning process (e.g. in a custom command). In the script:

• $csvFilePath – Specifies the path to the CSV file.
• $telephonePropertyName – Specifies the LDAP name of the property that stores the phone number. Must be the same as in the first script.

$csvFilePath = "\\Server\Share\PhoneList.csv" # TODO: modify me
$telephonePropertyName = "telephoneNumber" # TODO: modify me

# Get user phone number
try
{
    $phoneNumber = $Context.TargetObject.Get($telephonePropertyName)    
}
catch
{
    $Context.LogMessage("User %fullname% has no phone number specified in property $telephonePropertyName.", "Warning")
    return
}

# Import CSV
$records = import-csv $csvFilePath

$phoneNumerbFounded = $False
foreach ($record in $records)
{
    if ($record."Phone Number" -ne $phoneNumber)
    {
        continue
    }

    # Update the user
    $Context.TargetObject.Put($telephonePropertyName, $NULL)
    $Context.TargetObject.SetInfo()

    # Update CSV file record    
    $record.User = ""
    $record."Assigned Date" = ""
    $phoneNumberFounded = $True
    break
}

if (!$phoneNumberFounded)
{
    $Context.LogMessage("No record matching phone number '$phoneNumber' found", "Warning")
    return
}

# Update the CSV file
$records | Export-Csv -Path $csvFilePath -Force -NoTypeInformation