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.

Fetch Sharepoint Online sites associated with O365 groups via Sharepoint Search KQL

As you probably know O365 groups have associated Sharepoint Online site behind. It may be needed to fetch only those sites which have associated O365 group via Sharepoint Search and KQL. In this post I will show how to do that and in the future post I will describe how to fetch only those sites which are not associated with O365 groups.

Modern Team sites which are associated with O365 group are created using Groups template. I.e. in order to get all sites associated with groups we may use the following KQL query:

(contentclass:STS_Site OR contentclass:STS_Web) WebTemplate:GROUP

It will work however there may be problem with old sites which were "groupified". I.e. regular site could be connected to O365 site - this procedure is called groupifying (see Connect to a Microsoft 365 group). Above query won't return such sites.

Another approach is based on the fact that groupified sites contain special property bag properties which contain information about connected group:

• GroupAlias
• GroupDocumentsListId
• GroupDocumentsUrl
• GroupId
• GroupType
  

By default custom property bag properties are not searchable but they can be made searchable by adding them to Indexed Property Bag which can be done via PowerShell:

Connect-PnPOnline -Url "http://{tenant}.sharepoint.com"
Set-PnPPropertyBagValue -Key "customProperty" -Value "foo" -Indexed  

Here is good article which explains this topic: What Are Indexed Property Bags And How To Use Them For SharePoint Site.

The good thing is that at least GroupId looks like indexed by default. This gives us possibility to use another KQL for getting sites connected with O365 groups:

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

This query will work also with groupified sites.

Update 2021-02-25: see also article which shows how to fetch sites which are not associated with O365 groups: Fetch Sharepoint Online sites which are not associated with O365 groups via Sharepoint Search KQL.

Get list items from Sharepoint using lists.asmx web service

In one of my previous articles I showed how to get authentication cookies (FedAuth) from Sharepoint FBA using authentication.asmx web service (see Authenticate in Sharepoint on-prem FBA site via OTB /_vti_bin/Authentication.asmx web service). In this post I will show how to use these cookies and get list items from Sharepoint list using standard lists.asmx web service.

It can be done using the following code:

Cookie authCookies = ...;
string listTitle = ...;
string soapBody =
	"<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" +
	"  <soap:Body>" +
	"    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" +
	"      <listName>" + listTitle + "</listName>" +
	"    </GetListItems>" +
	"  </soap:Body>" +
	"</soap:Envelope>";
var cookies = new CookieContainer();
cookies.Add(authCookies);
var handler = new HttpClientHandler();
handler.CookieContainer = cookies;
using (var httpClient = new HttpClient(handler))
{
	var req = new HttpRequestMessage(HttpMethod.Post, "http://example.com/_vti_bin/lists.asmx")
	{
		Content = new StringContent(soapBody, Encoding.UTF8, "text/xml")
	};
	var res = httpClient.SendAsync(req).GetAwaiter().GetResult();
	string result
	if (res.StatusCode == HttpStatusCode.OK)
	{
		result = res.Content.ReadAsStringAsync().Result;
	}
}

Here we use auth cookies from previous article. We again create special SOAP body where pass list title. In this example we will get all list items but it is also possible to fetch list items which match CAML query - for that you will need to use quert, viewFieldsm rowLimit and queryOptions tags in SOAP body. For more information about these properties refer to documentation of lists.asmx web service. Id everything was done correctly it will return you list items from specified lists in SOAP format.

Use GraphServiceClient with HttpClient in muti-thread apps

If you use GraphServiceClient from Graph SDK for .Net in multi-thread app consider reusing it as static instance instead of creating own instances inside each thread. GraphServiceClient itself is thread safe (see here) and can be reused within multiple threads as is (without locks, synchronizations, etc).

Otherwise it will internally create multiple HttpClient instances and you may face with reaching sockets limit. Alternatively you may create static HttpClient and then pass it to the GraphServiceClient instances created per thread as it has special constructor for that:

public GraphServiceClient(HttpClient httpClient)
  : base("https://graph.microsoft.com/v1.0", httpClient)
{
}

If you create threads intensively there may be too many instances of HttpClient created and you will reach sockets limit. E.g. this is how it looked in Azure function app with queue-triggered Azure function when GraphServiceClient instances were created inside function call (own instance per thread):

I.e. there were 2K connection peaks and the following errors in the logs:

An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full

When I changed the code and reused static instance of GraphServiceClient connections count got stabilized: