Thursday, February 25, 2021

Fetch Sharepoint Online sites which are not associated with O365 groups via Sharepoint Search KQL

In the previous article I showed how we can use Search API in order to fetch sites which are associated with O365 groups:

(contentclass:STS_Site OR contentclass:STS_Web) GroupId<>""

But what if we need to get opposite result: fetch only those sites which are not associated with O365 groups (sites without groups). Our knowledge about GroupId indexed property bag property will help also here since we need to fetch those sites which don't have any value in GroupId property.

First (naive) attempt to do that would be trying something like that:

(contentclass:STS_Site OR contentclass:STS_Web) GroupId:""

However it won't work because KQL supports not equal operation with empty string but doesn't support equal to empty string operation. My colleague Juha Alhojoki pointed me to the original trick which allows to do that: Check Not Null condition in Keyword Query Language for SharePoint Search. So idea is that if we know possible start letters for managed property (remember that KQL supports prefix matching but doesn't support suffix matching, i.e. we may search by "StartsWith" operator but not with "EndsWith") we may enumerate them all and then apply NOT operator to the final condition.

In our example GroupId contains guid i.e. it may start with Latin alphabet symbols and digits. So we will have the following condition to fetching sites which are not associated with O365 groups:

(contentclass:STS_Site OR contentclass:STS_Web) NOT(GroupId:a* OR GroupId:b* OR GroupId:c* OR GroupId:d* OR GroupId:e* OR GroupId:f* OR GroupId:g* OR GroupId:h* OR GroupId:i* OR GroupId:j* OR GroupId:k* OR GroupId:l* OR GroupId:m* OR GroupId:n* OR GroupId:o* OR GroupId:p* OR GroupId:q* OR GroupId:r* OR GroupId:s* OR GroupId:t* OR GroupId:u* OR GroupId:v* OR GroupId:w* OR GroupId:x* OR GroupId:y* OR GroupId:z* OR GroupId:0* OR GroupId:1* OR GroupId:2* OR GroupId:3* OR GroupId:4* OR GroupId:5* OR GroupId:6* OR GroupId:7* OR GroupId:8* OR GroupId:9*)

What we did here is enumerated all Latin symbols and digits and applied NOT operator to exclude sites which match enumerated criterias.

No comments:

Post a Comment