Updated: Aug 25, 2024
Watch a YouTube video that walks you through the process:
Here is the code featured in the video:
Import-Module ExchangeOnlineManagement
Connect-IPPSSession -UserRPSSession:$false
Get-RetentionCompliancePolicy | Where-Object {$_.RestrictiveRetention -eq $True}
Set-RetentionCompliancePolicy -RestrictiveRetention $True -Identity "Preservation Lock Test"
Updated: Aug 28, 2024
In the previous article, I showed you how to create and set up the IRM HR Data Connector to monitor risky users, such as those who submitted a resignation or had a bad performance review.
It is very easy to set up and use, but it still requires technical skills. For instance, generating the ISO 8601 Date Format using PowerShell and executing the PowerShell script that uploads the CSV file to M365. Additionally, executing the script means that parameters such as TenantID, Application ID, Secret, and ObjectId for the Connector must be used every time the script is run, which could impose potential security risks. Even creating entries in the CSV file could be confusing and prone to errors.
I am going to demonstrate a solution to solve these problems. I created a Canvas App for entering the values using more user-friendly controls. The Canvas App then uses a Power Automate Flow to save the CSV file in the SharePoint Library. Then, there is another Power Automate Flow that is executed when the file is added to the SharePoint Library. This Flow will upload the CSV file to M365.
Why do we even consider HR workers entering data themselves? Ideally, HR Systems should be integrated with the HR Data Connector to automatically update connector data when an employee submits a resignation or receives a bad performance review. Why do we even need to bother with entering this data somewhere else?
While it is definitely the ideal solution to integrate the HR System with IRM, the reality is that integration might not be feasible. While it is not rocket science to use web services or, if your HR system is built on D365 ERP, in many cases you will be met with the following show-stoppers:
1. HR System runs on Legacy platforms.
2. Getting a blessing from the Architecture Board and Security could become an adventure.
3. The owners of HR Systems usually guard it well.
4. Depending on how your HR system is designed, it might or might not be easy to differentiate between the events that you need for the IRM Data Connector without modifications to the HR system.
5. Integration testing could be quite an exercise.
Integration with the HR system would be a solution that involves many teams and will take a while to implement. It is easier and faster to create a very simple Canvas App and a couple of simple Power Automate Flows. Sure, the HR officers would be required to use this Canvas App to enter the information yet again, but there are literally four mandatory fields at most. And how often do people submit resignations in any given day or are demoted?
Even if it’s a large organization that would be able to commit to integrating the HR system with IRM, this Canvas App could be used as an interim solution for your Insider Risk Management implementation.
Needless to say, HR data is highly sensitive and we must protect it well. So, we are going to start with creating an Security Group in Microsoft Entra.
This group will be for the people who can run the Canvas App, access the SharePoint Library where the CSV files are uploaded, and access two SharePoint Lists.
Go to MS Entra Admin Center.
Click the “Identity” blade to expand it, and select "Groups."
3. Select “New Group.”
4. "Microsoft 365" as the group type.
5. Enter the group name.
6. Keep the "Assigned" membership type.
7. Add group owners.
8. Add group members.
After the group is created, note the Group Object ID – you will be using this ID in the Canvas App.
I am using the following SharePoint objects: one SharePoint Library and two SharePoint Lists.
This will be a repository for the CSV files created with the help of the Canvas App and Power Automate Flow. The library will have standard settings, but we want to ensure the CSV files don’t show up in search results, even though Security Trimming would prevent that. I like to always take extra measures.
Go to Library Settings.
Choose ‘Advanced Settings.’
Scroll down to the ‘Search’ section.
Set ‘Allow items from this document library to appear in search results?’ to No.
Click ‘OK’ to save.
Next, we are going to make sure that only authorized HR officers can access this library – remember, the CSV files will contain very sensitive information. We will restrict library access to the members of the group we created earlier.
Go to the Library Settings.
Select ‘Permissions for this document library.’
Click on “Stop inheriting permissions.” This is required to create specific permissions for this library.
4. After this setting applies (you might need to refresh the page), select ‘Grant Permissions.’
5. Select ‘Invite people’ in the popup dialog box.
6. Type the group name that you created in the previous step.
7. Click ‘Share.’
8. Remove all other groups and users (e.g., “members”). I did keep the Owners membership.
This list contains all HR scenarios that you will be using in your IRM HR Data Connector. It will be used to populate the drop-down list with the scenarios in the Canvas App. This list should also be restricted to the members of the custom group that we created earlier.
This list represents the empty structure of the table for the connector data. It is here for convenience – I am using a Table Control in the Canvas App, and it was just too tempting to create something straightforward to bind it to the Table Control. Restrict access to the members of the custom group. While there is no sensitive information, we just want to ensure the integrity of the structure of this SharePoint List.
Let’s now move to the Canvas App – the centerpiece of this solution. I am going to show you some critical pieces of functionality and code. However, keep in mind that this is not a tutorial on Power Apps. I will not be going over things such as how to set up the Default property of a control or how to change the DisplayMode of a control. This tutorial is for the IRM HR Data Connector, and I will be concentrating on the Canvas App code related to that.
The Canvas App has two screens:
one for entering or updating the HR entry
2. and the other for displaying all the records that have been added during the session.
We will need a connection to the SharePoint Library and the two SharePoint Lists that we previously created.
Additionally, we will add the Office365Users connection to help get the identity of the user who is executing the Canvas App and for supplying all of the active users, and Office365Groups, which will help us ensure that only users who are members of the IRM HR Data Connector Group can run this application.
In the App OnStart event we are creating a collection to hold the HR Scenarios, that are stored in the SharePoint List:
ClearCollect(colScenarios, 'HR Data Connector Scenarios');
This collection will be used for the drop down list Scenarios:
For the cboEmails, we are going to use the following formula to filter Active M365 Profiles:
Filter(Office365Users.SearchUserV2({searchTerm:Trim(Self.SearchText), isSearchTermRequired:false}).value,AccountEnabled=true && Not ("#EXT" in UserPrincipalName))
Since we consolidated different HR scenarios into a single CSV file, we need to set the DisplayMode of the controls according to the selected HR scenario. Below are the screenshots that display which controls will be toggled for the different scenarios.
These screenshots will help you visualize how the controls will be dynamically adjusted based on the selected HR scenario. This ensures that the right fields are shown for each specific case, making data entry more streamlined and error-free.
Here are a couple of examples of how this is controlled using the controls’ DisplayMode:
If ((ddScenarios.Selected.Title = "Resignation" Or ddScenarios.Selected.Title = "Job level change"), DisplayMode.Disabled, DisplayMode.Edit)
If (ddScenarios.Selected.Title = "Resignation", DisplayMode.Edit, DisplayMode.Disabled)
Here is the code that adds a newly created record to the Table (it is executed within the OnSelect event of the button 'Add':
Set(fldResignationDate, If(dpResignationDate.DisplayMode = DisplayMode.Disabled, Blank(), (DateValue(dpResignationDate.SelectedDate))));
Set(fldLastWorkingDate, If(dpLastWorkingDate.DisplayMode = DisplayMode.Disabled, Blank(), (DateValue(dpLastWorkingDate.SelectedDate))));
Set(fldEffectiveDate, If(dpEffectiveDate.DisplayMode = DisplayMode.Disabled, Blank(), (DateValue(dpEffectiveDate.SelectedDate))));
If (IsBlank(cboUsers.Selected), Notify("Please select a user", NotificationType.Error),
Patch(
'HR Connector Data Table',
Defaults('HR Connector Data Table'),
{
HRScenario: ddScenarios.Selected.Title,
EmailAddress: cboUsers.Selected.Mail,
ResignationDate: fldResignationDate,
LastWorkingDate: fldLastWorkingDate,
EffectiveDate: fldEffectiveDate,
Remarks: txtRemarks.Text,
Rating: txtRating.Text,
OldLevel: txtOldLevel.Text,
NewLevel: txtNewLevel.Text
}
)
);
As soon as the record is added to the ‘HR Connector Data Table’ we can see them in the Table control on the next Screen (the DataSource of the Table control is set to ‘HR Connector Data Table’).
We can also edit an existing record. We use the OnSelect event of the Table control to set the tempSelectedItem to the Selected Item:
Set(tempSelectedItem, tblDataEntries_1.Selected)I then use the following code for the OnSelect event of the “Edit” button:
Set(selectedItem2, tempSelectedItem);
Navigate('HR Data Entry', ScreenTransition.Fade)Back in the first Screen, ‘HR Data Entry’, I use DefaultDisplay property of the controls to set their properties to show the values of the record the user is currently editing. Here are a couple of examples: DefaultDisplay of the txtRemarks:
If((!IsBlank(selectedItem2.Remarks)),Blank(),selectedItem2.Remarks)DefaultDisplay of the Data Picker dpEffectiveDate:
If(
IsBlank(selectedItem2) || IsBlank(selectedItem2.EffectiveDate),
Today(),
DateValue(selectedItem2.EffectiveDate)
)We can delete the entry using the following script in the OnSelect event of the ‘Delete’ button:
Remove('HR Connector Data Table', LookUp('HR Connector Data Table', ID = tblDataEntries_1.Selected.ID))I am sure that you’ll be able to create the Canvas App and take care of other controls and events without any issues – it’s a no-brainer.
I am now going to concentrate on the main functionality – saving the records to the CSV file.
I do it in two stages.
First, I create the content of the comma-delimited CSV file in the Canvas App, and then I run the Power Automate Flow that will create this file in the SharePoint Library we created earlier.
There are a couple of ways to accomplish this. You can just send the content of the table to the Flow as is and create an action in Flow that will create a comma-delimited CSV file. In this example, I create a comma-delimited string right here in the Canvas App, and the Flow just saves it in SharePoint. Either way would work.
Here is a code for the OnSelect event of the ‘Save’ button:
// Convert the collection to CSV format with manually formatted dates in ISO 8601 format
ClearCollect(
csvCollection,
Concat(
'HR Connector Data Table',
HRScenario & "," &
EmailAddress & "," &
If(!IsBlank(ResignationDate),
Text(Year(ResignationDate), "0000") & "-" &
Text(Month(ResignationDate), "00") & "-" &
Text(Day(ResignationDate), "00") & "T" &
Text(Hour(ResignationDate), "00") & ":" &
Text(Minute(ResignationDate), "00") & ":" &
Text(Second(ResignationDate), "00") & "." &
Left(Text(Value(ResignationDate - Date(1970,1,1)) * 86400, "0000000"), 7) & "+05:30",
"") & "," &
If(!IsBlank(LastWorkingDate),
Text(Year(LastWorkingDate), "0000") & "-" &
Text(Month(LastWorkingDate), "00") & "-" &
Text(Day(LastWorkingDate), "00") & "T" &
Text(Hour(LastWorkingDate), "00") & ":" &
Text(Minute(LastWorkingDate), "00") & ":" &
Text(Second(LastWorkingDate), "00") & "." &
Left(Text(Value(LastWorkingDate - Date(1970,1,1)) * 86400, "0000000"), 7) & "+05:30",
"") & "," &
If(!IsBlank(EffectiveDate),
Text(Year(EffectiveDate), "0000") & "-" &
Text(Month(EffectiveDate), "00") & "-" &
Text(Day(EffectiveDate), "00") & "T" &
Text(Hour(EffectiveDate), "00") & ":" &
Text(Minute(EffectiveDate), "00") & ":" &
Text(Second(EffectiveDate), "00") & "." &
Left(Text(Value(EffectiveDate - Date(1970,1,1)) * 86400, "0000000"), 7) & "+05:30",
"") & "," &
Remarks & "," &
Rating & "," &
OldLevel & "," &
NewLevel & Char(10)
)
);
// Create the CSV string
Set(
csvContent,
"HRScenario,EmailAddress,ResignationDate,LastWorkingDate,EffectiveDate,Remarks,Rating,OldLevel,NewLevel" & Char(10) & Concat(csvCollection, Value)
);Note that IRM HR Data Connector expects Dates in the ISO 8601 format.
We are going to call the Power Automate Flow to save this string as CSV file.
Set(FileURL, SaveIRMHRDataConnectortoCSV.Run(csvContent).fileurl);The Flow also returns the name of the newly generated file and displays to the user:
If(!IsBlank(FileURL),Notify("CSV Data Connector File " & FileURL & " was created and uploaded to SharePoint Library", NotificationType.Success));We then clear all records from the Table:
RemoveIf('HR Connector Data Table', true);Create New Instant Cloud Flow:
Choose the following trigger: When Power Apps calls a flow (V2) (Power Apps) and name your Flow:
The flow is super simple:
The Input parameter is our comma-delimited string.
We then have an action that generates the csv file name. We are using the dynamic content to accomplish that:
concat('HRRecord_', formatDateTime(utcNow(), 'yyyy-MM-ddTHHmmss'), '.csv')We then create an action that generates the csv file. The file will be created in the SharePoint Library 'IRM HR Data Connector Files' that we created in the beginning of this tutorial:
Next action returns the name of the file that we just created back to the Canvas App:
Here is the file created in the Library:
Now that we have created the file, we need to automate uploading it to M365.
Microsoft offers a Power Automate Flow to accomplish that task.
This Flow is triggered when a file is created and uploads the CSV file that was added to the Library to Microsoft Purview.
You can download the zip file from the GitHub.
I am not going to go over the Power Automate Flow and how to use it. Microsoft already has an article explaining how to do so, which you can find here: https://learn.microsoft.com/en-us/purview/import-hr-data?tabs=microsoft-purview-portal#optional-step-7-upload-data-using-power-automate-templates
However, I am going to explain the modifications I made to the Flow so that it works with the solution I created.
The Flow is designed to work with OneDrive. I use a SharePoint Library instead. So, if you decide to follow my steps in creating a similar solution, here is how you would need to modify the Flow:
Delete Trigger (that was monitoring OneDrive) and add Trigger for SharePoint 'When a file is created (properties only)'.
Add SharePoint Site and the Library that we created for CSV files.
Be mindful of the error in the Flow: The URI in the Flow is incorrect during creation of this blog entry. It is https://webhook-df.ingestion.office.com/api/signals But it must be https://webhook.ingestion.office.com/api/signals
I already mentioned that when you upload an additional CSV file to the HR Data Connector in Microsoft Purview Insider Risk Management (IRM), the data is cumulative. This means that each new upload adds to the existing data rather than replacing it.
Since the newly uploaded CSVs will merge with existing data, it doesn’t really matter how the CSV files are created. The user can add one record, and the solution will upload a CSV with one record only. Or, the user can upload a bunch of new records.
Therefore, I am not concerned about modifying the records that already exist from the previous CSV generations.
I might provide an update if I notice any anomalies.
The files that accumulate in the SharePoint Library could become a liability, or they could provide historical information. This depends on how you or your client views it.
Should you decide to get rid of the obsolete files, you can add a Delete File action to the Flow after it uploads the CSV file to MS Purview.
Updated: Dec 13, 2024
HR Data Connector is one of the most valuable features of Insider Risk Management.
It allows you to monitor user activities when the users are considered a potential risk to the organization.
For example, if the user is being terminated, or the user gave a resignation notice, they might be departing not only with grudges but also with the company’s secrets. Additionally, if the user received a bad performance review or was demoted, it might be a strong risk indicator as well, and the activities of such users should be monitored.
Even when employees excel at their jobs and receive promotions, it could be worth monitoring their activities: if the promotion grants access to sensitive information or top secrets, you need to ensure that the motivation behind the career advancement was indeed to contribute to the company, and not to rise to the top in order to commit data exfiltration on behalf of another entity, whether it is for another government or corporation.
In this article, I am going to show you how to create an HR Data Connector.
In the next article, I am going to show you how you can quickly create a Power Platform Canvas App for your HR Department so that HR officers can easily enter new information. Additionally, I will show you how to automate the HR Data Connector so that M365 is updated right away.
But first, let’s look at the basics of the HR Data Connector. Microsoft made it very simple.
Data is saved in a comma-delimited .CSV file.
The file supports specific HR scenarios, such as resignation or termination. You can combine all HR events into a single file if you want, or have separate .CSV files for each scenario.
The reason you might want to have different CSV files is if you have different HR Teams or Systems that deal with different HR actions (for instance, if only one system or team deals with employment terminations).
You will then use a PowerShell script that is provided by Microsoft to upload the CSV file to M365. The Insider Risk Management will use this data to generate risk indicators.
Here is a table that lists which IRM Policy templates can use HR Data Connector Scenarios:
Policy template | HR data type |
Data theft by departing users | Employee resignations |
Data leaks by risky users | Job level changes, Performance reviews, Performance improvement plans |
Security policy violations by departing users | Employee resignations |
Security policy violations by risky users | Job level changes, Performance reviews, Performance improvement plans |
Healthcare policy | Employee profile |
You must have a Data Connector Admin role to implement the HR Data Connector.
If you are a Compliance Administrator, you already have that role.
But for personnel that will be taking care of the HR Data Connector it's better to create a custom role group with the Data Connector Admin role to adhere to the principle of least privilege.
You need to create an Entra ID App Registration for your HR Data Connector.
Login to Microsoft Entra Admin Center
Expand the Applications section and click “App registrations”.
Click “+ New registration” link in the app registrations.
Enter Name, in my case it’s IRM HR Data Connector
Select supported account type
Click “Register”
7. Note and save the Application (client) ID when your App Registration is created. You will be using it in two of the next steps.
8. Next, go to the "Certificates & secrets".
9. Click "+ New Client Secret" 10. In the "Add a client secret" Panel add Description and Expiry.
11. Click "Add" button
12. Copy the value of your secret - you will be using it in the script when uploading the data for your Data Connector.
Now we are going to create a CSV file for our connector.
Microsoft provides sample entries on its website:
https://learn.microsoft.com/en-us/purview/import-hr-data?tabs=microsoft-purview-portal#csv-file-for-employee-resignation-data
For this example, we are going to use one single file. Let’s consolidate all these data into one CSV file.
Note that the first column in this file is the HRScenario column, which will have the following values:
Resignation
Job level change
Performance review
Performance improvement plan
If you look at the sample file above, you will notice that some columns apply only to specific HR scenarios. Additionally, while some columns are mandatory for certain scenarios, others are not. Empty values will be ignored unless they are mandatory. If mandatory values are missing, the entry will be skipped during the data upload process, and you will receive an error. Missing data for non-mandatory values will produce a warning message.
Below are the screenshots of the Canvas App that I will cover in the next posting. They will help you quickly see which values are used by specific HR scenarios – they will be either enabled or disabled. The red asterisk marks mandatory fields for the HR scenario.
When entering dates into your CSV file for the CSV file, it is crucial to use the ISO 8601 format to ensure consistency and proper parsing.
In PowerShell, you can easily convert dates to this format using the following command:
Get-Date -Format 'o'
For example, to set a resignation date as yesterday and a last working date as two weeks from the resignation date, you can use the following PowerShell commands:
$resignationDate = (Get-Date).AddDays(-1) | Get-Date -Format 'o'
$lastWorkingDate = (Get-Date).AddDays(13) | Get-Date -Format 'o'
In MS Purview, go to ‘Data connectors’.
Scroll down to find HR Data Connector
Click to select
Click the ‘Add connector’ button
Provide App ID (this is the App Registration ID that you created in Microsoft Entra in the previous step)
Enter a unique name for your HR Data Connector
Select HR Scenarios
8. Choose '.csv file - Comma delimited' format of the file
9. Choose 'Upload a sample file' option
10. "Upload sample file" button will be activated. Click to upload your .CSV file. The actual values in the file don't matter at this point. The objective is to have all of the HR Scenarios and to map the columns.
11. After the file successfully uploaded, click Next
12. In the next screen you will be mapping the scenarios to the columns. The columns in your CSV file can have any names you want, as long as the are properly mapped in this step:
Here are some mapped columns:
13. Review and finish creation of your connector:
14. At the end of the Connector Creation Wizard, you will be provided with a jobId, which you will be using in the next step.
You will now need to download the PowerShell script to upload your CSV file to M365. You can find the script at this URL.
Click Raw and copy the script or use this link to get raw script.
Save the file as HRConnector.ps1 and run it with the following parameters:
.\HRConnector.ps1 -tenantId <tenantId> -appId <appId> -appSecret <appSecret> -jobId <jobId> -filePath '<filePath>'
The filePath is the path to your CSV file that you created.
AppID – the AppID that we created in MS Entra when we Registered the App.
JobId – the JobId was created when we set up the HR Data Connector in MS Purview.
The rest of the parameters are self-explanatory.
NOTE: The CSV must contain valid UPNs (User Principal Names), otherwise, the script will not run successfully.
ATTENTION: As of the writing of this article, the Microsoft example had two major errors in the example they posted in the article.
See the screenshot below and note that the Job Level Change Scenario has Resignation Date, but is missing Effective Date, which is a mandatory column for this Scenario. Your script will skip records if you use Microsoft's example.
When you run your script, it will run with warnings if it finds empty values for columns that are not mandatory. You can disregard those warnings as long as you don’t have any errors.
If the script finds errors in your CSV file, it will skip the entries that caused the error and will continue to the next entry (row in your CSV file).
Here is how you can check the status of your HR Data Connector.
In MS Purview, go to ‘Data connectors’
In Data connectors, click on ‘My Connectors’
Select your HR Data Connector
In Properties, find the Admin Log File and click on the ‘Download log’ link
When you upload an additional CSV file to the HR Data Connector in Microsoft Purview Insider Risk Management (IRM), the data is cumulative. This means that each new upload adds to the existing data rather than replacing it.
For example, if you initially upload a CSV file containing data for 10 users, and then later upload another CSV file with data for 6 additional users, the system will now have data for 16 users in total. The new upload does not substitute the existing data; instead, it appends the new entries to the existing ones.
If you need to update information for users that were previously uploaded, you can include their updated details in the new CSV file, and it will be merged with the existing data. This allows for continuous data integration without the risk of losing previously uploaded information.
In the next articles, I am going to show you how to use Canvas App to add data to your CSV file and how to automate refreshing your HR Data Connector with new records. The solution will be more user friendly as you don’t expect HR officers to use PowerShell to run the scripts or even generate date in the format that connector expects.
