Orchestrated provisioning and deprovisioning in external databases using custom scripts

    MS SQL Oracle PostgreSQL

    This use case will list the steps to construct an orchestration policy to automatically update your MS SQL database using custom scripts whenever a user-specific action is performed in ADManager Plus.

    How will this orchestration work?

    User-specific arguments will be used to locate the users in the database and based on the request sent, appropriate action will be carried out in the product.

    • When an insert request is initiated, a row will be inserted into the database table and the values specified in the request will be added to the specified columns.
    • When an update request is initiated, the values in the specified columns will be replaced with the values specified in the request.
    • When a delete request is initiated, the specified columns or rows will be deleted from the database.

    Prerequisite: Ensure that the MS SQL script is downloaded and moved into the <InstallationDirectory>\ADManager Plus\Scripts folder.

    Steps to orchestrate this event

    Creating an orchestration template using custom scripts

    1. Log in to ADManager Plus and navigate to the Automation tab.
    2. In the left pane, under Configuration click Orchestration Template.
    3. Drag and drop the Custom Script logic block under Custom Actions, fill in the following and click Save.
      • Block Name: Enter a suitable name for this Custom Script block.
      • Description: Describe the action that will be executed.
      • Script Type: Select PowerShell as the script type from the drop-down list.
      • Script Path: Enter the absolute file path or filename.
        E.g., MSSQL_Insert.ps1\MSSQL_Update.ps1\MSSQL_Delete.ps1
      • Arguments: Provide the arguments as comma-separated values. Refer to the table below to find the list of arguments that can be passed in the request.
    Action Arguments Example
    Insert Server Name, Database Name, Port Number, Authentication Type, Table Name, Column Names, Column Values, User Name, Password Windows Authentication "EmpServer", "Empdata", "1433", "Windows", "Users", "col1|col2", "%employeeID%|%telephoneNumber%"

    rver Authentication
    "EmpServer", "Empdata", "1433", "Server", "Users", "col1|col2", "%employeeID%|%telephoneNumber%", "sa", "Test@123"

    Update Server Name, Database Name, Port Number, Authentication Type, Table Name, Column Names, Column Values, Where Query, User Name, Password Windows Authentication
    "EmpServer", "Empdata","1433" "Windows","Users","col1|col2" "%employeeID%|%telephoneNumber%","samAccountName='%sAMAccountName%'"

    Server Authentication
    "EmpServer", "Empdata","1433","Server","Users" "col1|123col2" ,"%employeeID%|%telephoneNumber%" ,"samAccountName='%sAMAccountName%'","sa","Test@"

    Delete Server Name, Database Name, Port Number, Authentication Type, Table Name, Where Query, User Name, Password Windows Authentication
    "EmpServer","Empdata","1433","Windows","Users","samAccountName='%sAMAccountName%'"

    Server Authentication
    "EmpServer","Empdata","1433","Server" ,"Users","samAccountName='%sAMAccountName%'","sa","Test@123"

    Creating a new Event-driven Automation to configure the conditions under which the template will be executed

    1. Log in to ADManager Plus as the admin.
    2. Navigate to Automation → Configuration → Event-driven Automation.
    3. Click the Create New Automation button on the top-right corner of the page.
    4. Enter a suitable Name and Description for the profile.
    5. In the Criteria section, add the conditions under which the template needs to be executed. For example, when the Create Single User action or Create Bulk Users option is performed.
    6. In the Event-driven Automation add the Orchestration Template you had created from the list.
    7. Click Save.

    This use case will list the steps to construct an orchestration to automatically update your Oracle database using custom scripts whenever a user-specific action is performed in ADManager Plus.

    How will this orchestration work?

    User-specific arguments will be used to locate the users in the database and based on the request sent, appropriate action will be carried out in the product.

    • When an insert request is initiated, a row will be inserted into the database table and the values specified in the request will be added to the specified columns.
    • When an update request is initiated, the values in the specified columns will be replaced with the values specified in the request.
    • When a delete request is initiated, the specified columns or rows will be deleted from the database.

    Prerequisites:

    Ensure that the following steps are performed before orchestrating this event:

    • Copy the Oracle.DataAccess.dll from {Oracle-Home}\ODP.NET\bin\4\ and paste it in the <InstallationDirectory>\ADManager Plus\bin folder.
    • Download and move the Oracle script into the <InstallationDirectory>\ADManager Plus\Scripts folder.

    Steps to orchestrate this event

    Creating an orchestration template using custom scripts

    1. Log in to ADManager Plus and navigate to the Automation tab.
    2. In the left pane, under Orchestration, click Orchestration Template.
    3. Drag and drop the Custom Script logic block under Custom Actions, and fill in the following and click Save.
      • Block Name: Enter a suitable name for this Custom Script block.
      • Description: Describe the action that will be executed.
      • Script Type: Select PowerShell as the script type from the drop-down list.
      • Script Path: Enter the absolute file path or filename.
        E.g., Oracle_Insert.ps1/Oracle_Update.ps1/Oracle_Delete.ps1
      • Arguments: Provide the arguments as comma-separated values. Refer to the table below to find the list of arguments that can be passed in the request.
    Action Arguments Example
    Insert Host ID, Port Number, Service Name, User Name, Password, DBA Privilege, Table Name, Column Names, Column Values "localhost", "1521", "sys", "Oracle", "orcl.test", "SYSDBA" "Users", "col1|col2", "%employeeID%|%telephoneNumber%"
    Update Host Id, Port Number, Service Name, User Name, Password, DBA Privilege, Table Name, Column Names, Column Values, Where Query "localhost", "1521", "sys", "Oracle", "orcl.test", "SYSDBA" "Users", "col1|col2", "%employeeID%|%telephoneNumber%", "samAccountName='%sAMAccountName%'"
    Delete Host Id, Port Number, Service Name, User Name, Password, DBA Privilege, Table Name, Where Query "localhost", "1521", "sys", "Oracle", "orcl.test", "SYSDBA" "Users", "samAccountName='%sAMAccountName%'"

    Creating a new orchestration profile to configure the conditions under which the template will be executed

    1. Log in to ADManager Plus as the admin.
    2. Navigate to Automation → Configuration → Event-driven Automation.
    3. Click the Create New Profile button on the top-right corner of the page.
    4. Enter a suitable Name and Description for the profile.
    5. In the Profile Criteria section, add the conditions under which the template needs to be executed. For example, when the Action selected is Create Single User or Create Bulk Users.
    6. In the Orchestration Profile, add the orchestration template you had created from the list.
    7. Click Save.

    This use case will list the steps to construct an orchestration to automatically update your PostgreSQL database using custom scripts whenever a user-specific action is performed in ADManager Plus.

    How will this orchestration work?

    User-specific arguments will be used to locate the users in the database and, based on the request sent, appropriate action will be carried out in the product.

    • When an insert request is initiated, a row will be inserted into the database table and the values specified in the request will be added to the specified columns.
    • When an update request is initiated, the values in the specified columns will be replaced with the values specified in the request.
    • When a delete request is initiated, the specified columns or rows will be deleted from the database.

    Prerequisites:

    Ensure that the following steps are performed before orchestrating this event:

    • Download and install PostgreSQL ODBC driver from here.
    • Download and move the PostgreSQL script into the <InstallationDirectory>\ADManager Plus\Scripts folder.

    Steps to orchestrate this event:

    Creating an orchestration template using custom scripts

    1. Log in to ADManager Plus and navigate to the Automation tab.
    2. In the left pane, under Orchestration, click Orchestration Template.
    3. Drag and drop the Custom Script logic block under Custom Actions, fill in the following, and click Save.
      • Block Name: Enter a suitable name for this Custom Script block.
      • Description: Describe the action that will be executed.
      • Script Type: Select PowerShell as the script type from the drop-down list.
      • Script Path: Enter the absolute file path or filename.
        E.g.,PostgreSQL_Insert.ps1/PostgreSQL_Update.ps1/PostgreSQL_Delete.ps1
      • Arguments: Provide the arguments as comma-separated values. Refer to the table below to find the list of arguments that can be passed in the request.
    Action Arguments Example
    Insert Driver Name, Server, Port Number, Database, User Name, Password, Table Name, Column Names, Column Values "PostgreSQL Unicode(x64)", "TestServer1", "45678", "TestDB", "loginUser", "Test@123" "Users", "col1|col2", "%employeeID%|%telephoneNumber%"
    Update Driver Name, Server, Port Number, Database, User Name, Password, Table Name, Column Names, Column Values, Where Query "PostgreSQL Unicode(x64)", "TestServer1", "45678", "TestDB", "loginUser", "Test@123" "Users", "col1|col2", "%employeeID%|%telephoneNumber%" "samAccountName='%sAMAccountName%'"
    Delete Driver Name, Server, Port Number, Database, User Name, Password, Table Name, Where Query "PostgreSQL Unicode(x64)", "TestServer1", "45678", "TestDB", "loginUser", "Test@123" "Users", "samAccountName='%sAMAccountName%'"

    Creating a new orchestration profile to configure the conditions under which the template will be executed

    1. Log in to ADManager Plus as the admin.
    2. Navigate to Automation → Configuration → Event-driven Automation.
    3. Click the Create New Profile button on the top-right corner of the page.
    4. Enter a suitable Name and Description for the profile.
    5. In the Profile Criteria section, add the conditions under which the template needs to be executed. For example, when the Create Single User action or Create Bulk Users option is performed.
    6. In the Orchestration Profile, add the orchestration template had created from the list.
    7. Click Save.