Solution
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.
Create M365 Group
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.
SharePoint Libraries and Lists
I am using the following SharePoint objects: one SharePoint Library and two SharePoint Lists.
‘IRM HR Data Connector Files’ SharePoint Library
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.
‘HR Data Connector Scenarios’ SharePoint List
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.
‘HR Connector Data Table’ SharePoint List
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.
Canvas App
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.
Canvas App Connections
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.
Examples of Controlling DisplayMode Based on Selected HR Scenario
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)
Adding a Record
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))Saving the Records to CSV file
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);Power Automate Flow to Save CSV File
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:
Uploading CSV File to M365 Purview
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
Additional Notes
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.
Deleting Stale Files (or Not)
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.
Comments