Share via

Set up a connector to import HR data in US Government

  • Article

You can set up a data connector to import human resources (HR) data to your US Government organization. HR-related data includes the date an employee submitted their resignation and date of the employee's last day. This HR data can then be used by Microsoft information protection solutions, such as the insider risk management solution, to help protect your organization from malicious activity or data theft inside your organization. Setting up an HR connector consists of creating an app in Microsoft Entra that's used for authentication by connector, creating a CSV mapping files that contains your HR data, creating a data connector in the compliance center, and then running a script (on a scheduled basis) that ingests the HR data in the CSV file to the Microsoft cloud. Then the data connector is used by the insider risk management tool to access the HR data that was imported to your Microsoft 365 US Government organization.

Tip

If you're not an E5 customer, use the 90-day Microsoft Purview solutions trial to explore how additional Purview capabilities can help your organization manage data security and compliance needs. Start now at the Microsoft Purview trials hub. Learn details about signing up and trial terms.

Before you begin

  • The user who creates the HR connector in Step 3 must be assigned the Data Connector Admin role. This role is required to add connectors on the Data connectors page in the Microsoft Purview portal or the compliance portal. This role is added by default to multiple role groups. For a list of these role groups, see Roles in Microsoft Defender for Office 365 and Microsoft Purview compliance. Alternatively, an admin in your organization can create a custom role group, assign the Data Connector Admin role, and then add the appropriate users as members. For instructions, see the "Create a custom Microsoft Purview role group" section in Permissions in the Microsoft Purview compliance portal.

    Note

    The Data Connector Admin role is currently not supported in US Government GCC High and DoD environments. Therefore, the user who creates the HR connector in GCC High and DoD environments must be assigned the Mailbox Import Export role in Exchange Online. By default, this role isn't assigned to any role group in Exchange Online. You can add the Mailbox Import Export role to the Organization Management role group in Exchange Online. Or you can create a new role group, assign the Mailbox Import Export role, and then add the appropriate users as members. For more information, see the Create role groups or Modify role groups sections in the article "Manage role groups in Exchange Online".

  • You'll need to determine how to retrieve or export the data from your organization's HR system (on a regular basis) and add it to the CSV file that's described in Step 2. The script that you run in Step 4 will upload the HR data in the CSV file to the Microsoft cloud.

  • The sample script that you run in Step 4 will upload HR data to the Microsoft cloud so that it can be used by other Microsoft tools, such as the insider risk management solution. This sample script isn't supported under any Microsoft standard support program or service. The sample script is provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample script and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

Step 1: Create an app in Microsoft Entra ID

The first step is to create and register a new app in Microsoft Entra ID. The app will correspond to the HR connector that you create in Step 3. Creating this app will allow Microsoft Entra ID to authenticate the HR connector when it runs and attempts to access your organization. This app will also be used to authenticate the script that you run in Step 4 to upload your HR data to the Microsoft cloud. During the creation of this Microsoft Entra app, be sure to save the following information. These values are used in later steps.

  • Microsoft Entra application ID (also called the app Id or client Id)
  • Microsoft Entra application secret (also called the client secret)
  • Tenant Id (also called the directory Id)

For step-by-step instructions for creating an app in Microsoft Entra ID, see Register an application with the Microsoft identity platform.

Step 2: Prepare a CSV file with your HR data

The next step is to create a CSV file that contains information about employees who have left your organization. As explained in the Before You Begin section, you'll need to determine how to generate this CSV file from your organization's HR system. The following example shows a completed CSV file (opened in Note Pad) that contains the three required parameters (columns). It's much easier to edit the CSV file in Microsoft Excel.

EmailAddress,TerminationDate,LastWorkingDate
sarad@contoso.com,2019-04-23T15:18:02.4675041+05:30,2019-04-29T15:18:02.4675041+05:30
pilarp@contoso.com,2019-04-24T09:15:49Z,2019-04-29T15:18:02.7117540

The first row, or header row, of the CSV file lists the required column names. The name used in each column header is up to you (the ones in the previous example are suggestions). However, the same column names you use in the CSV file must be specified when you create the HR connector in Step 3. Do not include spaces in the column names.

The following table describes each column in the CSV file:

Column name Description
EmailAddress
Specifies the email address of the terminated employee.
TerminationDate
Specifies the date the person's employment was officially terminated in your organization. For example, this may be the date when the employee gave their notice about leaving your organization. This date may be the different than the date of the person's last day of work. Use the following date format: yyyy-mm-ddThh:mm:ss.nnnnnn+|-hh:mm, which is the ISO 8601 date and time format.
LastWorkingDate Specifies the last day of work for the terminated employee. Use the following date format: yyyy-mm-ddThh:mm:ss.nnnnnn+|-hh:mm, which is the ISO 8601 date and time format.

After you create the CSV file with the required HR data, store it on the same system as the script that you run in Step 4. Be sure to implement an update strategy so the CSV file always contains the most current information. Doing so ensures that that whatever you run the script, the most current employee termination data is uploaded to the Microsoft cloud.

Step 3: Create the HR connector

The next step is to create an HR connector in the Microsoft Purview portal or the compliance portal. After you run the script in Step 4, the HR connector that you create ingests the HR data from the CSV file to your Microsoft 365 organization. In this step, be sure to copy the job ID that's generated when you create the connector. You'll use the job ID when you run the script.

Select the appropriate tab for the portal you're using. Depending on your Microsoft 365 plan, the Microsoft Purview compliance portal is retired or will be retired soon.

To learn more about the Microsoft Purview portal, see Microsoft Purview portal. To learn more about the Compliance portal, see Microsoft Purview compliance portal.

  1. Sign into the Microsoft Purview portal.

  2. Select Settings > Data connectors.

  3. Select My connectors, then select Add connector.

  4. From the list, choose HR.

  5. On the Authentication credentials page, do the following and then select Next:

    1. Type or paste the Microsoft Entra application ID for the Azure app that you created in Step 1.

    2. Type a name for the HR connector.

  6. On the File mapping page, type the names of the three column headers (also called parameters) from the CSV file that you created in Step 2 in each of the appropriate boxes. The names are not case-sensitive. As previously explained, the names that you type in these boxes must match the parameter names in your CSV file. For example, the following screenshot shows the parameter names from the example in sample CSV file shown in Step 2.

    Column heading names match the ones in the CSV file.

  7. On the Review page, review your settings and then select Finish to create the connector.

    A status page is displayed that confirms the connector was created. This page contains two important things that you need to complete the next step to run the sample script to upload your HR data.

    Review page with job ID and link to github for sample script.

    1. Job ID. You'll need this job ID to run the script in the next step. You can copy it from this page or from the connector flyout page.
    2. Link to sample script. Select the here link to go to the GitHub site to access the sample script (the link opens a new window). Keep this window open so that you can copy the script in Step 4. Alternatively, you can bookmark the destination or copy the URL so you can access it again in Step 4. This link is also available on the connector flyout page.

  8. Select Done.

    The new connector is displayed in the list on the Connectors tab.

  9. Select the HR connector that you just created to display the flyout page, which contains properties and other information about the connector.

    Flyout page for new HR connector.

    If you haven't already done so, you can copy the values for the Azure App ID and Connector job ID. You'll need these to run the script in the next step. You can also download the script from the flyout page (or download it using the link in the next step.)

    You can also select Edit to change the Azure App ID or the column header names that you defined on the File mapping page.

Step 4: Run the sample script to upload your HR data

The last step in setting up an HR connector is to run a sample script that will upload the HR data in the CSV file (that you created in Step 2) to the Microsoft cloud. Specifically, the script uploads the data to the HR connector. After you run the script, the HR connector that you created in Step 3 imports the HR data to your Microsoft 365 organization where it can be accessed by other compliance tools, such as the Insider risk management solution. After you run the script, consider scheduling a task to run it automatically on a daily basis so the most current employee termination data is uploaded to the Microsoft cloud. See Schedule the script to run automatically.

  1. Go to window that you left open from the previous step to access the GitHub site with the sample script. Alternatively, open the bookmarked site or use the URL that you copied.

  2. Select the Raw button to display the script in text view.

  3. Copy all the lines in the sample script and then save them to a text file.

  4. Modify the sample script for your organization, if necessary.

  5. Save the text file as a Windows PowerShell script file by using a filename suffix of .ps1; for example, HRConnector.ps1.

  6. Open a Command Prompt on your local computer, and go to the directory where you saved the script.

  7. Run the following command to upload the HR data in the CSV file to the Microsoft cloud; for example:

    .\HRConnector.ps1 -tenantId <tenantId> -appId <appId>  -appSecret <appSecret>  -jobId <jobId>  -csvFilePath '<csvFilePath>'

    The following table describes the parameters to use with this script and their required values. The information you obtained in the previous steps is used in the values for these parameters.

    Parameter Description
    tenantId The Id for your Microsoft 365 organization that you obtained in Step 1. You can also obtain the tenant Id for your organization on the Overview blade in the Microsoft Entra admin center. This is used to identify your organization.
    appId The Microsoft Entra application Id for the app that you created in Microsoft Entra ID in Step 1. This is used by Microsoft Entra ID for authentication when the script attempts to access your Microsoft 365 organization.
    appSecret The Microsoft Entra application secret for the app that you created in Microsoft Entra ID in Step 1. This also used for authentication.
    jobId The job ID for the HR connector that you created in Step 3. This is used to associate the HR data that is uploaded to the Microsoft cloud with the HR connector.
    csvFilePath The file path for the CSV file (stored on the same system as the script) that you created in Step 2. Try to avoid spaces in the file path; otherwise use single quotation marks.

    Here's an example of the syntax for the HR connector script using actual values for each parameter:

     .\HRConnector.ps1 -tenantId d5723623-11cf-4e2e-b5a5-01d1506273g9 -appId 29ee526e-f9a7-4e98-a682-67f41bfd643e -appSecret MNubVGbcQDkGCnn -jobId b8be4a7d-e338-43eb-a69e-c513cd458eba -csvFilePath 'C:\Users\contosoadmin\Desktop\Data\employee_termination_data.csv'

    If the upload is successful, the script displays the Upload Successful message.

    Note

    If you have problems running the previous command because of execution policies, see About Execution Policies and Set-ExecutionPolicy for guidance about setting execution policies.

Step 5: Monitor the HR connector

After you create the HR connector and run the script to upload your HR data, you can view the connector and upload status in the Microsoft Purview portal or the compliance portal. If you schedule the script to run automatically on a regular basis, you can also view the current status after the last time the script ran.

Select the appropriate tab for the portal you're using. Depending on your Microsoft 365 plan, the Microsoft Purview compliance portal is retired or will be retired soon.

To learn more about the Microsoft Purview portal, see Microsoft Purview portal. To learn more about the Compliance portal, see Microsoft Purview compliance portal.

  1. Sign into the Microsoft Purview portal.

  2. Select Settings > Data connectors.

  3. Select My connectors, then select the HR connector that you created to display the flyout page. This page contains the properties and information about the connector.

  4. Under Progress, select the Download log link to open (or save) the status log for the connector. This log contains information about each time the script runs and uploads the data from the CSV file to the Microsoft cloud.

    HR connector log file displays number rows from CSV file that were uploaded.

    The RecordsSaved field indicates the number of rows in the CSV file that uploaded. For example, if the CSV file contains four rows, then the value of the RecordsSaved fields is 4, if the script successfully uploaded all the rows in the CSV file.

If you've haven't run the script in Step 4, a link to download the script is displayed under Last import. You can download the script and then follow the steps in Step 4 to run it.

(Optional) Step 6: Schedule the script to run automatically

To make sure the latest HR data from your organization is available to tools like the insider risk management solution, we recommend that you schedule the script to run automatically on a recurring basis, such as once a day. This also requires that you update the HR data in the CSV file on a similar (if not the same) schedule so that it contains the latest information about employees who leave your organization. The goal is to upload the most current HR data so that the HR connector can make it available to the insider risk management solution.

You can use the Task Scheduler app in Windows to automatically run the script every day.

  1. On your local computer, select the Windows Start button and then type Task Scheduler.

  2. Select the Task Scheduler app to open it.

  3. In the Actions section, select Create Task.

  4. On the General tab, type a descriptive name for the scheduled task; for example, HR Connector Script. You can also add an optional description.

  5. Under Security options, do the following:

    1. Determine whether to run the script only when you're logged on to the computer or run it when you're logged on or not.

    2. Make sure that the Run with the highest privileges checkbox is selected.

  6. Select the Triggers tab, select New, and then do the following things:

    1. Under Settings, select the Daily option, and then choose a date and time to run the script for the first time. The script will run every day at the same specified time.

    2. Under Advanced settings, make sure the Enabled checkbox is selected.

    3. Select Ok.

  7. Select the Actions tab, select New, and then do the following things:

    Action settings to create a new scheduled task for the HR connector script.

    1. In the Action dropdown list, make sure that Start a program is selected.

    2. In the Program/script box, select Browse, and go to the following location and select it so the path is displayed in the box: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe.

    3. In the Add arguments (optional) box, paste the same script command that you ran in Step 4. For example, .\HRConnector.ps1 -tenantId "d5723623-11cf-4e2e-b5a5-01d1506273g9" -appId "c12823b7-b55a-4989-faba-02de41bb97c3" -appSecret "MNubVGbcQDkGCnn" -jobId "e081f4f4-3831-48d6-7bb3-fcfab1581458" -csvFilePath "C:\Users\contosoadmin\Desktop\Data\employee_termination_data.csv"

    4. In the Start in (optional) box, paste the folder location of the script that you ran in Step 4. For example, C:\Users\contosoadmin\Desktop\Scripts.

    5. Select Ok to save the settings for the new action.

  8. In the Create Task window, select Ok to save the scheduled task. You might be prompted to enter your user account credentials.

    The new task is displayed in the Task Scheduler Library.

    The new task is displayed in the Task Scheduler Library.

    The last time the script ran and the next time it's scheduled to run is displayed. You can double-select the task to edit it.

    You can also verify the last time the script ran on the flyout page of the corresponding HR connector in the compliance center.