Filter on managed properties in search with or without values

Back in 2014 I wrote the post How To: Search up items which don’t have a value set, which covers how to write keyword query syntax (KQL) filters to return or restrict items depending on if a specific managed property has a value or not. Recently Microsoft added support to more easily query if managed properties of type Text contain or does not contain a value.

Here’s a link to the updated documentation:

https://learn.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference#filter-on-items-where-a-text-property-is-empty-or-contains-a-value

Note: The new supported syntax only works for Microsoft 365 / Online

 

 

Items missing or having a text value

The syntax is as follows:

KQL Syntax	Description
NOT <Property Name>:*	Items where a property does not have a value
<Property Name>:*	Items where a property does has a value

The documentation uses the following example to list SharePoint sites associated to a hub site.

(DepartmentId:* OR RelatedHubSites:*) AND contentclass:sts_site NOT IsHubSite:true

Deciphering the query:

KQL	Description
(DepartmentId:* OR RelatedHubSites:*)	return items which has a value in the original DepartmentId managed property or in the successor RelatedHubSites property
contentclass:sts_site	return only site items
NOT IsHubSite:true	exclude hub site results

Note that hubs connected to another hub will not be included in the above query. If you want those, then remove the NOT IsHubSite:true part and post-process the results as needed.

For completeness let’s cover how to accomplish the same for other types of managed properties.

Items missing or having a YesNo value

To find items missing a value in a date property the syntax shown in my 2014 seems to no longer work and should be replaced with the following where the date is some low non-used date.

KQL	Description
NOT (RefinableYesNo00:true OR RefinableYesNo00:false)	return items not having a value in a YesNo property
(RefinableYesNo00:true OR RefinableYesNo00:false)	return items having a value in a YesNo property

 

Items missing or having a date value

To find items missing a value in a date property the syntax shown in my 2014 seems to no longer work and should be replaced with the following where the date is some low non-used date.

KQL	Description
NOT RefinableDate00>1900-01-01	return items not having a value in a date property
RefinableDate00>1900-01-01	return items having a value in a date property

 

Items missing or having a number value

For number type managed properties it’s easier as you typically know the range of values.

KQL	Description
NOT Size>=0	if the managed property only contain positive values, then this will return all items with no value set
NOT RefinableDecimal00>=0 NOT RefinableDecimal00<0	return items where the property RefinableDecimal00 has no value
Size>=0	return all items having a value which is greater than your smallest value

There are still new things to learn from the SharePoint Search API I won't share. I will NOT!

…I will, just a tad bit late :)

This was a tweet I made Friday October 21st once I understood the root cause of an API issue which has popped up in the last months. The issue affected PnP Modern Search web parts when query rules were enabled, and also the Search Query Tool with default settings.

I thought it was a weird API issue which had been introduced as part of an ongoing service upgrade for Microsoft Search lately, but turns out everything was working as expected – except the expected part hasn’t been expected for the past many years. And a big thank you to engineers at Microsoft helping me understand what the root cause is.

On the API side it manifests itself as follows with the following simple API REST query executed in the SharePoint Search Query Tool where you only get 2 main results where you would expect 10.

https://tenant.sharepoint.com/_api/search/query?querytext='mikael'&rowlimit=10

Where are the rest of my 10 results? Well, they happen to be located in the Secondary Results, a place I never looked.

I’ll explain the behavior, and it is actually correct (sort of), and I will explain why this happens now in 2022.

A trip down memory lane

When SharePoint 2013 was released Microsoft released the feature of query rules, which allowed to bring in result blocks into the search results as seen below.

The below screenshot triggers the rule “People Name in SharePoint” which bring in two result blocks.

And the query rule definition

The definition above says to always place people on top, and possible show documents authored by the person as a block within the results, or interleaved.

The thing is that the logic/setting to have it ranked has changed.

Todays logic – changed in October 2021

The old logic

The old logic introduced with SharePoint 2013 would start the block high up, and if results in the block was not clicked it would move down the page, and eventually off the page, which is what has happened for most customers over the years.

The new logic now introduced will ALWAYS interleave the block on the page 1 results, never moving it off the page.

So how does this affect the API?

By default an API query will invoke query rules unless explicitly turned off, e.g. the above query https://tenant.sharepoint.com/_api/search/query?querytext='mikael'&rowlimit=10

As Modern Search was introduced quite some time back this has greatly reducing the use of the classic search center. This means that people haven’t clicked results in result blocks from quite some time, no clicks are recorded and the block moved off the page – never appearing in API queries.

Now as the logic has changed, the blocks come back, which is not a bug, but maybe not expected.

Together with query rules there is another API setting available, one I have never thought about, but it’s been there all along. “Enable Interleaving” which by default is set to true documented at https://learn.microsoft.com/en-us/previous-versions/office/sharepoint-csom/jj262234(v=office.15).

Of course, if you don’t need query rules for your scenario you should always disable them on the API call. Problem solved!

Then again, using the PnP modern search web parts a common scenario is to use promoted results, and thus you need query rules enabled. Which leads to queries on people names triggering the original “People names rules” causing interleaving to happen and the results split into primary and secondary result tables in the response.

The solution then is to set EnableInterleaving=false.

Changing the query to https://contoso.sharepoint.com/_api/search/query?querytext='mikael'&enableinterleaving=false&rowlimit=10 ensure 10 results as expected in the primary result set.

I have released a fix to the Query Tool which by default will disable interleaving, or you can set it yourself.

https://github.com/pnp/PnP-Tools/releases 

And I have made the same fix to the PnP Modern Search Web Parts v4.8, and any interleaving should be done manually at the template level if strictly needed.

Query variable trick in Microsoft Search verticals (and classic)

Microsoft has been working on both classic and modern scenarios for Microsoft Search, and evaluating existing solutions to determine the best way to support query variables. This post is not exclusive to Microsoft Search, and the same technique can be used with any SharePoint classic search experience. The only difference is the type of query variables that are supported for each experience.

For supported query variables in Microsoft Search modern experiences see https://learn.microsoft.com/en-us/microsoftsearch/manage-verticals#profile-query-variables.

For supported query variables in classic search see https://www.techmikael.com/2014/05/s15e03-query-variables-constant-trouble.html.

Solution

The sample case solution provide an option to filter search results down a city. All items are tagged with a managed property City to allow for the filtering. On the SharePoint page of the solution the user can pick their own or a specific city. When picking their own, no query parameter is passed with the city. When picking a specific city the user is sent to a vertical in Microsoft Search passing the city value as a query string parameter:

_/layouts/15/search.aspx/verticalname?City=Helsinki

Which brings us to the query template to use:

{searchTerms} {?City:{Request.City} NOT UNIQUESTRING}{?City:{Profile.positions.detail.company.address.city}}

To see what properties you can use for a Profile query variable, view the output of https://graph.microsoft.com/beta/me/profile in e.g. Graph Explorer. For our test case the City location for a person is available via the query variable {Profile.positions.detail.company.address.city}.

What the above query template achieve is: If a query string parameter City is present, this will be used as part of the query. If not present, the City value of the logged in users profile will be used instead. The {?} notation means that if a query variable is missing, the part enclosed within the braces will be removed all together from the template on evaluation.

I’m using a trick with UNIQUESTRING (which could be any random unique string not present in the search index) to invalidate the last part of the query if we have a query string parameter in the URL. It adds invalid KQL sort of for a property which does not exist, and is thus ignored.

Let’s add some examples to illustrate the evaluation where the ignored part of the query is highlighted in yellow, and the inclusion part in green. The user’s profile value for city is Oslo.

Scenario 1 – Click on Oslo

• ?City=Oslo
• Users City=Oslo

Ending query: City:Oslo NOT UNIQUESTRINGCity:Oslo    

Scenario 2 – Click on Helsinki

• ?City=Helsinki
• Users City=Oslo

Ending query: City:Helsinki NOT UNIQUESTRINGCity:Oslo

Scenario 3 – Click on My City

• ?City=<missing>
• Users City=Oslo

Ending query: City:Oslo

Scenario 4 – Click on Helsinki (and missing a city in the profile)

• ?City=Helsinki
• Users City=<missing>

Ending query: City:Helsinki NOT UNIQUESTRING

Scenario 5 – Click on My City and missing a city in the profile

• ?City=<missing>
• Users City=<missing>

Ending query: <empty>

And that’s all there is to it. By matching values on a users profile with values on other data you can create quick navigation and filtering scenarios. By adding SPFx into the mix even more control and logic can be built around the search results pages and passing in values.

Retirement of custom default result sources in Microsoft Search for modern search experiences

In November 2021 I posted about bookmarks being the successor feature of promoted results for organizational scoped searches in Microsoft Search, which was the first step to modernize the Microsoft Search stack and remove dependencies on classic SharePoint search features and API’s.

The next step is now under way, as announced by MC526131 - Retirement of custom default result sources in Microsoft Search for modern search experiences.

 

For most customers the change, which will start April 10th 2023, should have no impact. The way KQL rewrite works for a default result source was never intended for modern search experiences as any change was applied to all verticals showing SharePoint and OneDrive content. The ability for an admin to edit and add KQL per vertical in the modern experience is a better and more accurate feature – succeeding the result source feature which doesn’t really work well in modern search experiences in SharePoint. See https://learn.microsoft.com/en-us/microsoftsearch/manage-verticals#keyword-query-language-kql for more information on vertical management.

I want to be crystal clear that nothing happens to classic search experiences nor to experiences which are powered by the SharePoint Search API. Everything keeps on working as before – it’s just Microsoft Search in out-of-the-box modern experiences which retires reading the setup.

I also want to point out that this does not effect query rule triggered promoted results on SharePoint sites or SharePoint hub-sites, as the modern experience will show a promoted result for these scopes regardless of the result source they may have been targeted towards.

And as a last note, this only applies to environment where search vertical administration is rolled out.

That’s it and you can likely ignore this post as it should not affect you

Surface Dynamics 365 Data in Microsoft Search

If you’re a user of Dynamics 365 did you know that it’s now possible to surface search results from Dynamics 365 in Microsoft Search?

Head over to https://docs.microsoft.com/en-us/microsoftsearch/manage-dynamics365, or direct your admin to follow the directions on how to enable the experience.

Quick steps:

• Navigate to https://admin.microsoft.com/Adminportal/Home?#/MicrosoftSearch/connectors
• Click “add a new app or service”
  
• Pick D365 and follow the setup wizard
  

Sensible defaults – avoiding error on first navigation to the Files tab in a Teams channel

I won’t take credit for the solution myself, as this was shared to me by the awesome Remi Blom-Ohlsen, who is too timid to share great tips with the world himself :)

We’ve all been there, and if you create solutions for customers involving Teams you are bound to have encountered the issue. After the team is created and you immediately navigate to the Files tab in a channel, you get an error message.

Navigate to the Posts tab and back, and all is working.

When creating Teams using out of the box user interfaces you still have to live with this. If you however use a custom ordering and provisioning solution you can via code fix this nuance for your users :)

If you are patient and wait a tiny bit, the folder will be created for you. For those who are impatient and want to ensure everything is the best way possible for end user, they can continue to read.

If your provisioning solution is using C# you can ensure the channel folder is creating using code like this. And the crux here is the FilesFolder property on the channel object.

public static async Task<DriveItem> InitTeamDrive(string GroupId, GraphServiceClient GraphClient = null)
{
	var graphClient = <get your client here>;
	var channels = await graphClient.Teams[GroupId].Channels.Request().GetAsync();

	foreach (var channel in channels)
	{
		if (channel.DisplayName == "General")
		{
			var drive = await graphClient.Teams[GroupId].Channels[channel.Id].FilesFolder.Request().GetAsync();
			return drive;
		}
	}
	return null;
}

The similar direct graph call would be.

GET https://graph.microsoft.com/v1.0/teams/<group id>/channels/<channel id>/filesFolder

Bookmarks to take over for promoted results in Microsoft Search

As announced in the M365 message center post MC293777 Microsoft Search will over the course of November to December of this year execute phase one to deprecate the support of classic query rule based promoted results / best bets, and replace it fully with Bookmarks and Q&A answers.

Message center announcement: In order to improve the Microsoft Search experience, promoted results will no longer be supported in Microsoft Search for Organization level scoped searches and will be replaced by the Bookmarks and Q&A features.

In 2019 Microsoft Search released Bookmarks and Q&A as the successor to promoted results for organization scoped queries. Since the launch Microsoft Search has seen a tremendous uptake in usage of Bookmarks and it currently outnumbers the classic feature by more than 3:1, and users have more success when using them.

Org scoped queries means queries performed from SharePoint Home office.com, the Work vertical in Bing, or if a user upscope on the search result page in SharePoint using the breadcrumb, and also sites in SharePoint configured to show org scoped results by default.

Up until now the search result page has returned both Bookmarks and promoted results, meaning admins has two ways to configure the same user experience. With this change promoted results will stop being shown in Microsoft Search, but will continue to work in classic search experiences (for those still clinging to it), or experiences built on SharePoint Search API’s.

Microsoft made the change internally over a year ago and the time has now come for the rest of the world!

Why bother?

I’ve worked with query rules ever since they were introduced in SharePoint 2013 and they are a pain to work with. Which is why I way back in 2014 came up with an easier approach, “Better best bets with lists”, and some premier field engineers in Microsoft iterated it to “Even better best bets with lists”.

Well, those times are over my friend, and here’s my list of benefits using Bookmarks/Q&A over promoted results:

• Management – if you ever tried to manage more than 10 query rules, you must like to be challenged for patience
• Targeting – you can times scope a bookmark (also possible with query rules), you can geo-target, you can AAD target and you can device target Bookmark answers. Something very hard to accomplish with query rules.
• Client support – Bookmark answers will show in SharePoint, Office.com and Microsoft Search in Bing, with more to come. As Microsoft Search is not only SharePoint you can expect Bookmark and other answers to show in the full M365 eco system of client applications when applicable! Currently rolling out to Teams.

What you need to do to prepare

Microsoft Search provides an alternative to promoted results using either Bookmark or Q&A answers.

If your organization set up Promoted Results in SharePoint, you can import the Promoted Results into Microsoft Search and make the imported content available to your users. This is an easy way to quickly populate search results as soon as you set up Microsoft Search and make it more effective for your users. We recommend using promoted results from SharePoint as a reference to understand how to name and create relevant search results.

Microsoft Search allows a search administrator to import promoted results as Bookmarks. To import promoted results, in the Microsoft 365 admin center, open the Search & intelligence admin center and then select Answers. In the Answers dialog, select Bookmarks and choose Import SharePoint results to import promoted results from SharePoint. Also see https://docs.microsoft.com/en-us/microsoftsearch/manage-bookmarks#import-sharepoint-results.

Updated script to re-index user profiles for search

This has been on my mind for years and I finally got the time to create a new version of my PowerShell script to re-index user profiles for the scenario where you map a new UPA property to be used in search.

You can pick up reindex-users-v2.ps1 from https://github.com/wobba/SPO-Trigger-Reindex. The script allows for quicker updates especially for tenants with a lot of profiles as it uses the UPA bulk import API instead of iterating the UPA.

See https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/bulk-user-profile-update-api-for-sharepoint-online and https://docs.microsoft.com/en-us/previous-versions/office/sharepoint-csom/mt642955(v=office.15) for documentation.

Cover image by Markus Winkler @ Unsplash

Site descriptions are now searchable!! (and more)

“What????!” you might say, hasn’t it always been like that? And the answer is that ever since SharePoint 2013 it hasn’t. Because if it had, I would not be writing this – just saying.

As a bonus if you create a column in a library named Description, the content of this property is now also searchable. See my old post https://www.techmikael.com/2014/12/solution-to-cannot-search-content-in.html about this issue.

That’s correct people, you get a 2 for 1 special as Microsoft Search has rectified a long time nuance. And I’ll take the credit for getting this fixed (with some awesome technical help from Himanshu on the Stream team) - I can be humble another time

Note: The change applies to updated content, not content already in the index. For active sites it should fix itself, so no action needed on your part.

Technical explanation

For the site description (or the description of an Office 365 group or a Team) the crawled property storing the description value, ows_SiteDescription, was mapped to two managed properties:

• Description
• SiteDescription

Neither which were marked as searchable, effectively removing the ability to get a match on normal user queries from a search box. We have now made a search schema change to map ows_SiteDescription to the managed property Contents in addition to the two existing ones, ensuring full-text search.

Same goes for a SharePoint column/field named Description. Except this is a weird one as the crawled property for this column is Office:6, not ows_Description. To be on the safe side we have mapped both Office:6 and ows_Description to Contents as well.

Need a refresh on what makes content free-text searchable in SharePoint? Head over to https://www.techmikael.com/2014/07/what-makes-sharepoint-column-searchable.html.

Cover image by Alexander Krivitskiy@unsplash

Microsoft Graph: Encoding and decoding the drive id

If you have worked with the Microsoft Graph API and SharePoint items you might have encountered URL’s which include a drive id. Where you in SharePoint typically work with URL’s for a site and the document library, a drive id is an encoded representation of a document library location.

The following Graph REST request will list all items in a specific document library:

GET https://graph.microsoft.com/v1.0/drives/b!VvpCx03990mC5Lb5YxH0SUA9TgZHvEZImra6PMjrvbx85KUwT1BMTbhen6I6ffXL/root/children

Fetching the item for a file you use this signature to retrieve the file itself where item id is part of the listing: GET /drives/{drive-id}/items/{item-id}

With some reverse engineering you easily can figure out the drive-id is a prefixed base64 encoded string composed of the site id, web id and list id for a particular library.

Using PowerShell here’s a few lines converting the drive id string to the correct guid’s and back:

$driveId = "b!VvpCx03990mC5Lb5YxH0SUA9TgZHvEZImra6PMjrvbx85KUwT1BMTbhen6I6ffXL"   
$encodedDriveId = $driveId.Substring(2).Replace('_','/').Replace('-','+')    
$bytes = [Convert]::FromBase64String($encodedDriveId)
$siteIdBytes = [byte[]]$bytes[0..15]
$siteIdGuid = New-Object Guid @(,$siteIdBytes) #site id

$webIdBytes = [byte[]]$bytes[16..31]
$webIdGuid = New-Object Guid @(,$webIdBytes) #web id

$listIdBytes = [byte[]]$bytes[32..47]
$listIdGuid = New-Object Guid @(,$listIdBytes) #list/library id

$bytes = $siteIdGuid.ToByteArray() + $webIdGuid.ToByteArray() + $listIdGuid.ToByteArray()
$driveId = "b!" + [Convert]::ToBase64String($bytes)

The item-id part is a bit more tricky and looks to be some sort of base32 encoding of a SharePoint item’s unique id. As I haven’t figured out the mechanics you can still access the item by route of the list item unique id:

GET https://graph.microsoft.com/v1.0/drives/{drive-id}/list/items/{unique-id}/driveItem

Example: https://graph.microsoft.com/v1.0/drives/b!VvpCx03990mC5Lb5YxH0SUA9TgZHvEZImra6PMjrvbx85KUwT1BMTbhen6I6ffXL/list/items/7D0E814C-0798-4966-A20C-6DE4F27F2027/driveItem

Of course, if you already have the site id, web id, list id and unique item id, you can access a drive item using the following syntax as mentioned in my file preview post.

GET https://graph.microsoft.com/v1.0/sites/{site-id},{web-id}/lists/{list-id}/items/{item- id}/driveItem

Photo by https://unsplash.com/@maurosbicego