Articles in this section

Export data from Microsoft Active Directory using Simple or SQL view

Anitha Abraham
Updated

The Microsoft Active Directory has two form views—Simple and SQL—that you can use to export data.

Simple view: Enables you to easily view and specify the resource, columns, and operations to filter and export data. This simple option is helpful if you're not very familiar with SQL and would like to

  • view all the resources and related options available

  • make UI selections rather than write SQL queries to export data

SQL view: Enables you to quickly write SQL queries. The SQL option is helpful if you're a technical user familiar with SQL and would like to

  • use SQL programming rather than the graphical user interface (GUI) for quicker database interactions

  • write SQL queries including complex ones to export data

Export settings

The export settings under the What would you like to export? section differ in Simple and SQL view. Any settings you configure in this section is saved in that view only. If you switch views, this data is cleared. For example, if you switch from SQL to Simple view, then after the switch, the data in the SQL view is cleared. But, you can open the export and select the SQL view to access the most recently saved version of the data.

Enter all the required * fields in the sections below.

Settings

Instructions

General

Name your export *

Provide a clear and distinguishable name. You will have the option to choose this export throughout integrator.io, and a unique identifier will prove helpful later when selecting among a list of exports/lookups that you’ve created.

Description

Describe your export so that you and others can quickly understand its purpose. Be sure to highlight any nuances that a user should be aware of before using this import in a flow. As you make changes to the resource, be sure to keep this description up to date.

Connection *

Select the connection if you have an existing Microsoft Active Directory connection. If you want to create a new connection, see Set up a connection to on-premise Microsoft Active Directory.

What would you like to export? (Simple view)

Resource *

Select a resource from the list. For more information, see Microsoft Active Directory tables & parameters.

Select columns

By default, all the columns for the resource are selected, and the number of selected columns is shown.  If you want to select specific columns or review columns, click Select columns. In the Configure columns page that opens, based on your requirements, 

  • To deselect all columns, clear the Column box

  • To quickly select specific columns, use the search box

  • To filter and view columns, choose All, Selected, or Unselected

Filtering conditions

To filter records, use the logical and conditional operators.

Logical operators: Use any of the following logical operators to define rules for data:

  • NOT: Select the checkbox if you want to use the NOT operator. NOT excludes any data that meets the criteria you define in the rules. If the checkbox is not selected, the filter includes any data that meets your defined rule criteria.

  • AND: Select AND if you want data that meets the combined criteria defined in the rules.

  • OR: Select OR if you want data that meets any criteria defined in the rules.

If you want to exclude all records that belong to a subsidiary company, select NOT. You can also select AND or OR to create filters that combine multiple rules for your filtration criteria. AND filters records that fit all listed rules in the group. OR filters records that fit any listed rules in the group.

Conditional operators: Choose any of the following operators for the selected column. Enter any required fields for a specified operator.

  • equals: Filters records with values equal to the one you specify.

  • not equals: Filters records with values that don't equal the one you specify.

  • is greater-than: Filters records with values that are greater than the one you specify.

  • is greater-than or equals: Filters records with values greater than or equal to the one you specify.

  • is less-than: Filters records with values less than the one you specify.

  • is less-than or equals: Filters records with values less than or equal to the one you specify.

  • between: Filters records with values that are within the range you specify.

  • not between: Filters records with values that are outside the range you specify.

  • like: Filters records with values that are similar to the pattern (using wildcards) you specify.

  • not like: Filters records with values that are not similar to the pattern (using wildcards) you specify.

  • contains: Filters records with values that contain details you specify.

  • does not contain: Filters records with values that don't contain details you specify.

  • in: Filters records with values that match the ones you specify. Use commas to separate the values If you don't use commas, it will be considered as an array.

  • not in: Filters records with values that don't match the ones you specify. Use commas to separate the values. If you don't use commas, it will be considered as an array.

  • null: Filters records that don't have values in the specified column.

  • is not null: Filters records that have values in the specified column.

What would you like to export? (SQL view)

SQL query *

Write the SQL query to retrieve records based on your requirements.

Tip

  • If you're familiar with SQL, enter your query in the box.

  • If you would like to use handlebars, click handlebars_editor.svg to open the Handlebars editor and create your SQL query. To build your SQL query easily and quickly, you can ask Celigo AI to generate a query. Enter a description of your query in plain English in the space provided, and Celigo AI will attempt to generate a query based on your prompt.

See also, Examples for querying Microsoft Active Directory

Configure export type

Export type *

Select one of the following export types:

  • All: To export all data, always

  • Delta: To export only data that has changed since the last time the data flow was run

  • Once: To export only data that has not already been exported

  • Limit: To export a limited set of records, primarily used for testing to avoid syncing lots of data

    Note

    When you select the export type as Limit - export a set number of records , another setting How many records would you like to export? is enabled for you to enter the maximum number of records to export each time the flow runs. The default value is set to one. You can export a maximum of 100 records.

    For more information, see Four different export types to retrieve data in Fine-tune integrator․io for optimal performance and data throughput.

After you configure the required settings, click Preview to check the export. If the SQL query is not executed successfully, revise the Query or Export type values. Then, click Save.

Configure optional settings

These export settings are optional, and typically don't have to be configured. However, if required in your scenario, you can configure these settings.

Optional sections/settings

Instructions

Would you like to group records?

Group records by fields

See Sort and group records

Mock output

Mock output

See Provide mock response data

Advanced

Page size

See Choosing the right page size in Fine-tune integrator․io for optimal performance and data throughput

Data URI template

When your flow runs but has data errors this field can be really helpful in that it allows you to make sure that all the errors in your job dashboard have a link to the original data in the export application.

Use a handlebars template to generate the dynamic links based on the data being exported. For example, if you are exporting a customer record from Shopify, you would most likely set this field to the following value https://your-store.myshopify.com/admin/customers/{{{id}}}. Or, if you are just exporting a CSV file from an FTP site then this field could simply be one or more columns from the file: {{{internal_id}}, {{{email}}}, etc...

Do not store retry data

Check this box if you do NOT want integrator.io to store retry data for records that fail in your flow. Storing retry data can slow down your flow's overall performance if you are processing very large numbers of records that are failing.

Override trace key template

If this field is set, you will override the platform default trace key field.

integrator.io uses the trace key to identify a unique record. You can use a single field such as {{{field1}}} or use a handlebar expression. For example, this syntax {{join “_” field1 field2}} will generate a trace key template field1_field2.