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😊

Deleting File Versions to reduce the SharePoint Storage Consumption

 

This is a fellow-up post on the MS365thinking: Don't pay more for SharePoint Storage than you have to :-) post, where I went through the options you have when reducing the SharePoint Storage consumption.

The first action could be to reduce the default number of versions from 500 to a more reasonable number, like 50.

This will minimize any future storage increase but will not delete any existing versions. So, we will have to trim the existing libraries. 

In this post I will show how you can reduce the consumption using the PnP.PowerShell command Remove-PnPFileVersion

First of all, let's provide some evidens on how SharePoint storage is calculated in a way that will convince your management that you should investigate this.

This script will create a brand-new Modern SharePoint site collection (STS#3) or use the site collection specified by you.

DummyFileVersionGenerator

The script will then create a number of major and minor versions using a file provided by you:

#how many major versions the script should create

$majorVersionCount = 30

#how many minor versions that should create per major version

$minorVersionCount = 10

It will then calculate the current amount of SP storage used in this site collection.

If the file you provided is 5MB then we would expect the storage to be 5*10*30 = 1500 MB.

Wait...I have heard that Microsoft only saves the diffs when dealing with modern office files as these are XML files behind the covers. So the 300 versions of the file will only take up a smidge more than 5 MB, right?

Yes, you are correct, but that is not the way SharePoint storage is calculated :-) as Microsoft calculates this as the aggregation of File Size for each version of the file.

The re-calculation of Storage seems to be on a schedule so you should expect that the new storage numbers will take serveral hours before it shows up in the Admin center

Once we can see the Storage being consumed on our site collection, it is time to bring out the harvester:

File Version Trimmer | PnP Samples

Please be aware that the script is a sample and NOT production grade code. That will most likely be a number of questions you will need to address before you can start trimming the file versions.

Typical questions could be:

• Which Site Collections should not be trimmed.
• Which archived Site Collections should be unarchived and trimmed and then archived again.
• Are we going to use the same pruning parameters on all sites, or should we only trim the minor versions on some Site Collections.
• Are some of the files tagged as records and should receive special treatment
• and so on :-)

 

Have fun and remember Sharing is caring