Using PnP Modern Search to display today's work anniversaries

In a way this is part 2 on how to using PnP Modern Search to display days of celebration. In Using PnP Modern Search to display today's birthdays I showed how to display birthdays, so a natural progression is to show how to display work anniversaries.

I was really surprised how difficult this proved to be. I even discussed the topic with Mikael Svenson and Marc Anderson and I realized that I had to cheat in order for it to work.

The problem is that date manipulation in KQL is hard at best and sometimes impossible. In order to find a work anniversary I must compare Today and the Hiredate managed property, but ONLY the day and month part.

Calculating how many years the employee has been with the company is also required and I have NO idea how to get that using KQL.

Objective

I wanted to be able to display two options: 

• Employees having a work anniversary today, and also how many years they have been with the company
• Employees having a work anniversary within the next 7 days + number of years.

How to cheat

In order to achieve the objectives, I had to get:

• The account 
• The hiredate, but with the year segment being the current year
• The number of years the employee has been with company at the next anniversary

 

I added birthdayThisYear while I was at it, in order to be able to show upcoming birthdays.

The basic idea is that I change the hiredate from eg. 10/26/2005 to 10/26/2023 as that allows me to compare Today with this value 😀

(RefinableDate12 is mapped to the HiredateThisYear property)

Who is having a work anniversary today?

RefinableDate12=Today

Anniversary within the next x days is also a piece of cake:

 RefinableDate12<{Today+7} AND RefinableDate12>{Today}

Implementation

Asking the intern to keep the list above in sync would be a cruel and unusual punishment, and hence actually forbidden in the USA, something to do with a constitution or something like that.

The list should of course be synced with the source, in this case the User Profile Application, at least once each month. The reason I am not using Graph is that birthday is not in the schema.

Prereq: Map SPS-Birthday and SPS-Hiredate to RefinableDate00/01

Once data is showing up in those RefinableDate properties you should be ready to create the list.

Grab the script here . It will create a few Site Columns and a Content type on the site collection of your choosing. The list is then created, and the Content type added to the list.

The Add-UserDataToList function will query the UPA for accounts and write the data we need to the list.

 

Hit the Reindex site in the site collections Search and Offline Availability section for the site columns to be picked up by search.

Map the crawled properties to a couple of RefinableDates. I prefer to make this mapping on the tenant level as I usually don't know where I might need them in the future. 

Find the Content Type ID of the Content type created by the script.

Add a Results web part, name it "Todays work anniversaries"

Set the Query template to: 

Contenttypeid:0x01009290F0FA40E7CB42B55D6D96F897262B* RefinableDate12=Today

(replace 0x01009290F0FA40E7CB42B55D6D96F897262B with the value for the your content type) 

Add a Results web part, name it "Upcoming anniversaries (7 days)"

Set the Query template to: 

Contenttypeid:0x01009290F0FA40E7CB42B55D6D96F897262B* RefinableDate12<{Today+7} AND RefinableDate12>{Today}

(replace 0x01009290F0FA40E7CB42B55D6D96F897262B with the value for your content type) 

Add the managed properties birthdayhiredateAccountOWSUSER, nextWorkAnniversaryInYearsOWSTNMBR, RefinableDate10, RefinableDate12 to the Selected Properties in both web parts.

Enter Layout slots

Change UserEmail to use the  birthdayhiredateAccountOWSUSER property

Change Date from Created to RefinableDate12

For both web parts you can select the People layout on "page 2" in the web part configuration.

Set Secondary text to: 

{{{Title}}} has been with us {{{nextWorkAnniversaryInYearsOWSTNMBR}}} years

(remember to click Use Handlebars expression)

Set Tertiary text to:

{{getDate (slot item @root.slots.Date) "MMMM-D"}}

(remember to click Use Handlebars expression)

Set the sorting on the Upcoming Anniversaries to RefinableDate12 asc

Run the Add-UserDataToList  function in a runbook or similar once a month and you should be done😊

Work with your Handlebar based layouts in an interactive way

 

Just a small tips and tricks blog post about working with custom layouts in PnP Modern Search Results web parts.

The Results web part comes with a build in result template editor:

However, my preferred way to work with display template is to use the External Template URL:

1. Select the out of the box template you intend to customize.
2. Grap the base template config from the "Edit result template"
3. Paste it into VS Code/Notepad and save it as a HTML file on your PC.
4. Upload the file to a document library on your tenant
5. Copy the URL of the HTML file (get it from Version history or similar)
6. Paste it into the "Use an external template URL" field 
7. Sync the document library to your PC
8. Open the HTML file from the sync'ed document library on your PC using VS Code or similar.
9. Update the code and save, and all it takes to see the change is a F5 on the page with the web part.

 Easy, right?  👌😊

(And you get all of the modern editor stuff and GitHub Copilot as well)

Disabled or inactive User Accounts in M365 - a prerequisite for Governance

 

Among the many "triggers" in SharePoint Online these are the ones with the greatest impact:

Content created

Content updated

User Account disabled

Today we are taking a look at the latter and how to detect that a User Account no longer is active, as this is the primary trigger for a lot of governance actions.

You might expect that a key property of a User Account such as if it is active should be pretty obvious, but it is NOT. (unless HR remembers to inform you) 

The fellowing approaches to detecting disabled or inactive user account is available as a PnP Script Samples PowerShell script 😊

First of all, what do we mean by "User Account"? 

Are we defining this as a User Profile in the SharePoint User Profile Application (UPA)?

Are we talking about the Users endpoint in Microsoft Graph?

The answer is YES, as both can be used to decide if the User Account is active.

HideFromAddressLists

In the UPA we have the SPS-HideFromAddressLists property which, as the name implies, is used to hide the account from a address list. This property is synchronized into the UPA and originates in Exchange and is used to exclude the account from the Global Address List (GAL).

Once the SPS-HideFromAddressLists property changes to true, it is very often a good indicator that the User Account has been disabled. 

In Microsoft Search this property is the only way to exclude accounts from being shown.

Customer AD Properties

Another option is that the organization have added a customer User Profile Property like "EmployeeStatus"/"UserDisabled" and this property is sync'ed from AD/AAD to SharePoint via a customer sync job. 

I have even seen an organization where the UserAccountName was prefixed with ZZ[YearOfLeaving] as a way to tag the user as disabled 😮

Please consult your friendly Entra ID admin for details if the organization is using this approach.

Graph 😃

Using Graph we can query the Users endpoint to get the accountEnabled property

However, you will have to check with the Entra ID team in your organization to verify when Accounts gets disabled and if it is a good indicator that the employee no longer is with the Organization. 

Perhaps you will have to check if another criterium is fulfilled, like no licenses assigned, in order to determine that the user has been off-boarded.

No Activity

Yet another way to query if the user is inactive is to use the Auditlogs in Graph to check if the user has been active within the last X days.

https://graph.microsoft.com/v1.0/auditLogs/signIns?$top=1&$filter=userPrincipalName eq 'LeeG@tcwlv.onmicrosoft.com'

 The absence of logins is not a bullet proof way to detect that the user no longer is with the Organization, but it is a pretty good indicator.

Summary

The above should make it clear that we most likely will have to test a number of criteria in order to determine if we should start the governance actions, like rerouting the workflows assigned to the user in question, reassign ownerships on assets and so on.

The list of criteria will probably vary from organization to organization, but using the right mix should give you a good chance to detect those users.

#SharingIsCaring

New video on YouTube - Options for integrating 3rd party data with SP/MS Search as of Q4 2023

 

5 October I gave a presentation at the Danish SharePoint User Group's meeting, covering the options that we have as of Q4 2023 when we want to integrate 3rd party data into Microsoft or SharePoint Search.

This is a shortened version and in English rather than Danish.  ;-) 

YouTube

View from MAN Energy (the host for the meeting, and thanks for that) is magnificent on a beautiful evening. 

The joys of hiding content from Microsoft Search

 

It was just a question of time, sooner than later we would get a request to hide content in Microsoft Search.

"But why not just use permissions to ensure that only the right group of people can see the content", I hear you say?

Well, in this case the content is visible for everybody, but the business rule is that the content MUST be reviewed at a set interval, and if the content has not been reviewed within this deadline, the end user should not see the content in search anymore.

One option could be to have a workflow that breaks the permissions on the item level once the deadline has been reached, but that option comes with a certain smell of substandard performance and complexity.

The Microsoft Search approach looked less intrusive and gave me some options:

Option A)

I could add a Result Type in the Search and Intelligence Admin center that would "intercept" all items of the specific type (using Content type id or similar property) and only display those items that have been reviewed using the $when clause.

Option B) 

I could change the query for the verticals in order to exclude specific content using a subset of KQL (Manage search verticals) 

Initially I selected Option A, and use the Search Layout Designer to define the layout, but when the layout was used in the Result Type it had a hard time deciding if the date of the deadline is in the past or the future.

Both blocks below were displayed...but only in the Result Type, in the Search Layout Designer it worked as expected. 😕

Due to this issue, I investigated Option B.

Step one was to get the query right, and using Graph Explorer this was pretty easy:

When I tried to negate the query I found that using the - sign as a shorthand for NOT seems to be a no-go going forward.

Once that query works I just updated the Query for the relevant verticals, in this case only the All vertical.

Add "cacheClear=true" to your URL when testing the update as per Manage search verticals

Can this approach scale?

For now, this solution does solve the current problem, but I doubt very much that it can scale as additional content needs to be excluded. 

It will be interesting to see the solution patterns that will be developed in the coming months as Microsoft Search becomes more top of mind in the MS365 world.