How SharePoint handles DateTime stamps - and why it causes PnP Search Filter URLs to be offset

 

TimeZones and how SharePoint handles timestamps

We recently had an issue logged in the PnP Modern Search repository regarding why the date filter values were "wrong".

Actually, the date filter values were correct as they were offset to match the current users Time Zone, which can be pretty hard to understand, hence this summery:

Disclamer: I am in Denmark, currently in UTC+2 (summertime)

Each time a timestamp is saved in SharePoint it is saved in UTC0, so when I upload a document at 13.45 local time the timestamp saved in the backend will read 11.45.

But in the Document library it clearly shows 13.45, what gives?

Each time the SharePoint Front End displays a timestamp it is offset to the local timezone, so my colleague in Los Angeles looking at the same document will see the timestamp as 04.45 (9 hours behind my timezone).

However, when you get the timestamp from an API you get the time in UTC0 !!!!

So, if I am looking for documents that was created between 4 April and 10 April I must answer the question: from which timezone do I define when 4 April begins?

If I lived in New Zealand(UTC+12) 4 April starts a lot earlier than it does here in Denmark(UTC+2).

So, when somebody asks you to set up a PowerShell script to pull all Contracts (content type) created on 10 April, you have to ask is the offset should be based on the Time Zone of the requestor or something else.

In PnP Modern Search any time-based query is automatically offset to match the Time Zone of the current user. 

When I query for something created between 4 April and 10 April the query is changed to:

creased between 3 April 22h00m and 9 April 22h00m.

The reason is that when 4 April started in Denmark the time was 3 April 22h00m UTC0 which is the values in the backend.

You can find your current TimeZone info by navigating to a classic SP Page, hitting F12, go to the Console tab, enter _spPageContextInfo and hit Enter, look for webTimeZoneData:

Time Zones might be hard to deal with, but I guess it is there to stay :-)

PnP Modern Search Office Hours - Online tips and advise

 

For some time, I have assisted the PnP Modern Search project by answering questions and handling bug reports.

However written communication is often not optimal to describe the issue you are facing which has been a blocking bug for several weeks now. And if you are new to SharePoint/Microsoft Search there are a substantial number of pitfalls that can prevent your progress.

Therefore, I am now offering:

PnP Modern Search Office Hours - Online tips and advise 

Every 2nd week I will offer 4 slots of 15 minutes each (for free of cause) where I will set up a Teams Meeting with each participant. The sessions will be 1:1 and will not be recorded, and using screen sharing we should be able to pinpoint the issue and get you going again. 

So, if you are stuck on a Search related issue or need some advice regarding Search feel free to sign up  in this MS Form

The first session is August 22 @16.00 UTC+1

PnP Modern search: Set refiners in the URL

 

The PnP Modern Search Filter web part supports deep URL linking, so if you provide the correct URL both the Search text and selected filters can be activated once the page loads:

However, getting the correct URL can be tricky. The recommended approach is to set the search text and the relevant Filters and then grab the browser URL. It will look like this:

https://[Tenant].sharepoint.com/sites/PnPModernSearch/SitePages/Filters-read-from-url-are-not-set-in-the-filter-web-part--3157.aspx?q=cmo&f=%5B%7B%22filterName%22%3A%22RefinableString09%22%2C%22values%22%3A%5B%7B%22name%22%3A%22CMO%22%2C%22value%22%3A%22%C7%82%C7%82434d4f%22%2C%22operator%22%3A0%2C%22disabled%22%3Afalse%7D%5D%2C%22operator%22%3A%22or%22%7D%5D#

The components are: the URL to the page + the q section + the f section.

The q section is just the text to be applied in the Search Box. 

The f section is the Filter settings. It looks pretty messy, but this PowerShell snippet will clean it up:

$url = Read-Host "Enter URL"

$pageURL = $url.Substring(0, $url.IndexOf("?"))

$filterURL = $url.Substring($url.IndexOf("?")+1)

$filterURL = $filterURL.Replace("%5B", "[")

$filterURL = $filterURL.Replace("%7B", "{")

$filterURL = $filterURL.Replace("%22", '"')

$filterURL = $filterURL.Replace("%3A", ":")

$filterURL = $filterURL.Replace("%2C", ",")

$filterURL = $filterURL.Replace("%5D", "]")

$filterURL = $filterURL.Replace("%7D", "}")

$filterURL = $filterURL.Replace("%C7%82%C7%82", "ǂǂ")

$newUrl = $pageURL + "?" + $filterURL

$newUrl

And once cleaned up it looks like this:

f=[{"filterName":"RefinableString09","values":[{"name":"CMO","value":"ǂǂ434d4f","operator":0,"disabled":false}],"operator":"or"}]

The value tag above is the Filter Token, and you will be able to find those using the SP Search Query Tool.

Searching for phone numbers in the Employee Directory

 

Phone numbers search - where the answer is "It depends"

A question I get very often when assisting customers with People Search is why they can't search for phone numbers.

The answer is that they in most cases CAN search for phone numbers. However, it might not be as straightforward as they would like.

The primary reason searching for phone numbers is difficult is that we as humans prefer that phone numbers are displayed in a form that is easy to read and remember.

For instance, a US phone number will often be displayed as +1 980 555 0101, and those spaces really can mess up the search. More on this later.

In some cases, the KQL has been twisted in such a way that you can't search for phone numbers:

I searched for Joni, and copied her phone number.

But I get no results?

In this case the query is set up to search for FirstName only: FirstName:{subjectTerms}


I then update the query to: FirstName:{subjectTerms}  OR MobilePhone:{subjectTerms}

But still no results, what gives?

Checking the UPA reveals that Joni doesn't have a Mobile nor Home phone number.

OK, so the query should be like this perhaps: 

FirstName:{subjectTerms}  OR WorkPhone:{subjectTerms} 

Using the query text +1 980 555 0101 it returns no hits.

Looking at the query sent to the API reveals that it is transformed into 

FirstName:+1 980 555 0101  OR WorkPhone:+1 980 555 0101

 

Ouch, not a well-formed query.

The final query will in this case look like this:

(FirstName:({subjectTerms}) OR WorkPhone:({subjectTerms})*  )

and the resulting query in the API call like this:

(FirstName:(+1 980 555 0101) OR WorkPhone:(+1 980 555 0101)*  )

Another option is to abandon the specific search and use a more generic query: {subjectTerms}*  

(However, this might find matches in unexpected properties)

Query text                            resulting query                Hits

joni                                        joni*                                1

+1 980 555 0101                +1 980 555 0101*            1

+1 980                                +1 980*                            1

+1 9                                    +1 9*                                6

0101                                    0101*                               0

Sorry, SharePoint search can only handle "starts with" wildcards, not "ends with"

Yet another approach is to use the phone number properties for display purposes only, and create your own User Profile properties, like "WorkPhoneAsText" which contains the WorkPhone value stripped for spaces and prefixes. 

I have primarily used it with customers that used the last 4 digits like a local extension, and would therefore have a "LocalExtension" property in the UPA and a matching managed Property.

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

SharePoint People search - How to clean up your results by exclude accounts

People search in SharePoint is based on the accounts you can see in the User Profiles blade in the SharePoint Admin center

And as you properly know the accounts in the User Profiles will very often be a mess of expired accounts, Meeting Room, External users, Test account and a lot more.

These are my notes for cleaning up the People/Employee search in SharePoint 

Exclude former employees

“SPS-HideFromAddressLists” originates in Exchange and is primarily used to decide which accounts that should not be shown in the Global Address list. In many companies/orgs this property is set to true/1 when somebody leaves the org.

This makes it a very useful property as it is the best indicator we have on whether an account is active or not.

"SPS-HideFromAddressLists"<>1  (show only accounts that should not be hidden in the GAL)

Exclude test, admin or similar accounts

-preferredname:admin*

-preferredname:test*

Exclude accounts which does not use a specific email domain

This filter is an excellent way to exclude external users and consultants with a full account within the org. Please note that we often exclude accounts by specifying that the field should contain a specific value.

WorkEmail:@contoso.com 

Exclude members of a specific department

-Department:External

-Department:Management

Exclude accounts which does not have a value in a given property

It is possible to define a query that will exclude the accounts that do not have any value in a specific field.

Since KQL (the Language we are using in the search queries) can't search for "Fields which does not contain any value" we have to use a little trick:

If we want to exclude the accounts that does not have a cell phone number, we simply must reverse that requirement, and hence it will be "include those accounts that have a value in the cell phone field":

(MobilPhone:0* OR MobilPhone:1* OR MobilPhone:2* OR MobilPhone:3* OR MobilPhone:4* OR MobilPhone:5* OR MobilPhone:6* OR MobilPhone:7* OR MobilPhone:8* OR MobilPhone:9*)

User Profile properties such as Department and JobTitle are based on Term Store Term sets and we can use the Term Guid values in our query.

In this example we are using the auto generated  property owstaxIdSPShDepartment and the guid specified is the root node of the Department term set: (look in the Term Store for those guids)

owstaxIdSPShDepartment:"#8ed8c9ea-7052-4c1d-a4d7-b9c10bffea6f" 

So using that in our query will insure that only account with a value in the Department field in included in our result. Neat, right?

Exclude accounts having a specific AccountName

-AccountName:Prod\B

This will enable you to exclude specific Accounts or groups of accounts like meeting rooms, cars and similar object as the AccountName often indicates which kind of account it is.

 

Search Scope

In this case search scope is defined as the list of fields/properties in the SharePoint User Profile used in the matching. By default we are matching against every field, however this will sometimes cause some rather strange results, espcially if you are not using Ranking.

Example: In this Org we have a department called QA, and would expect the members of that department to show up when doing a search for the query "QA", however Bob from Accounting shows up as number 4 result, ahead of several collegues from the QA department. Why?

Well, after going through Bobs account we can see he is a member of a security group named "Financial QA".......and that is sufficient to mess up the search results.

Of the UPA fields Description, Interests, Memberships and PeopleKeywords can contains values that causes unexpected results.

 

One option that will solve this issue could be to limit your search to only match on a set of specific fields. This will to some extend reduce the general usebility of people search as the end users might not be awere of this limitation.

Often these fields are used in such at limited search: 

FirstName

LastName

PreferredName

Department

PeopleKeywords

WorkEmail

MobilePhone

Location

Responsibilities

PastProjects

MobilePhone

Interests

Description

JobTitle

MobilePhone

Skills

Ranking and sorting

Ranking and sorting decides in which order the search results will be shown. 

Sorting by Name

Sorting can be set in the search query, in the Search Result Source or in the PnP Modern Search result web part.

Common field to sort by:

FirstName

LastName

PreferredName

 

Sorting using XRANK

XRANK specifies a numeric value for each result, usually calculated by Microsoft using the selected Ranking Model. But we are able to tweak this ranking calculation by boosting results that matches a keyword. 

Example:

XRANK(cb=1.5) FirstName:{searchTerms} OR XRANK(cb=0.5) Department:{searchTerms}

In this case we are boosting an account by factor 1.5 (150%) if the query matches FirstName and by 50% if the query matches Department.

Ranking model

In most cases we are using the ”People Search social distance model” ranking model as it is a good allround model for people search.

 

Additional info: Tech and me: People Search ranking for dummies (techmikael.com)

 

Tooling

When working with User profile data and search you should be familiar with these tools:

SharePoint Administation, specificly User Profiles (raw data) and the Search Schema on the tenant level ( mapning fields from UPA to Managed Properties)

SP Query Tool, PnP-Tools/Solutions/SharePoint.Search.QueryTool at master · pnp/PnP-Tools · GitHub, a desktop app that allows you to test both your quiries and your data.

 

SP Editor, a Chrome/Edge extension , GitHub - tavikukko/Chrome-SP-Editor: Extension for creating and updating files (js, css) in SharePoint Online from Developer Tools

Similar to the SP Quiry Tool as far as Search goes, and contains a lot of other SP related features.