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.

Thursday, February 18, 2021

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}"
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.

Wednesday, February 17, 2021

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='' xmlns:xsd='' xmlns:soap=''>" +
	"  <soap:Body>" +
	"    <GetListItems xmlns=''>" +
	"      <listName>" + listTitle + "</listName>" +
	"    </GetListItems>" +
	"  </soap:Body>" +
var cookies = new CookieContainer();
var handler = new HttpClientHandler();
handler.CookieContainer = cookies;
using (var httpClient = new HttpClient(handler))
	var req = new HttpRequestMessage(HttpMethod.Post, "")
		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.

Friday, February 5, 2021

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("", 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:

Friday, January 29, 2021

Calculate Sql Server database size having only db_reader permissions on target database

There are several possible ways to calculate database size (the same size which is shown when you right click on the database in Sql Server Management Studio > Properties > Files):

In this example we have 23 Mb of database file and 11 Mb of transaction log, total 34 Mb.

One way we can try is to run the following query:

select db_name(database_id) as database_name, 
    size/128.0 as CurrentSizeMB
from sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabase'

If you have permissions to run this command result will look like this:

As you can see it shows the same numbers as properties window shown above. The problem is that according to documentation you should have quite high server-level permissions to access sys.master_files:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

If you don’t have one of these permissions result will be empty.

Is it possible to calculate database size having less permissions? E.g. having only db_reader permissions on target database. The answer is yes it is possible. In order to do that we need to use system stored procedure sp_spaceused:

use MyDatabaseName
exec sp_spaceused

It works also with db_reader permissions on the target database. Result will look like this:

It returns 2 result sets and in 1st result set it returns overall database size which is sum of db file name and transaction log (34 Mb in our example).

Tuesday, January 26, 2021

Authenticate in Sharepoint on-prem FBA site via OTB /_vti_bin/Authentication.asmx web service

Sharepoint contains number of OTB web services which are located inside _vti_bin virtual directory. In this article we will check one of them: authentication.asmx. This is quite interesting web service which allows to authenticate your app in Sharepoint FBA site. I.e. allows to send username and password and get FedAuth authentication cookies if provided credentials are valid (the same cookies which are used by Sharepoint FBA site when you successfully logged in via it’s login page).

Note that you may use this web service in server-side apps or mobile apps but not in client side JavaScript-based solution because FedAuth cookies have HttpOnly flag and thus can’t be used in JavaScript.

In order to use this web service we need to send special SOAP body in HTTP POST request. In order to get example of this SOAP open in browser (where instead of you should use url of your site). It will show list of available web methods:

  • Login
  • Mode

Click on Login and you will get needed SOAP examples (in this article we will use SOAP 1.1):

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="" xmlns:xsd="" xmlns:soap="">
    <Login xmlns="">

In this SOAP body we need to set actual username and password in appropriate xml tags.

Here is example which shows how to send this SOAP body to authentication.asmx web service and get authentication cookies:

var cookies = new CookieContainer();
var handler = new HttpClientHandler();
handler.CookieContainer = cookies;
using (var httpClient = new HttpClient(handler))
    string soapBody =
    "<soap:Envelope xmlns:xsi='' xmlns:xsd='' xmlns:soap=''>" +
    "  <soap:Body>" +
    "    <Login xmlns=''>" +
    "      <username>" + username + "</username>" +
    "      <password>" + password + "</password>" +
    "    </Login>" +
    "  </soap:Body>" +
    var req = new HttpRequestMessage(HttpMethod.Post, "");
    req.Content = new StringContent(soapBody, Encoding.UTF8, "text/xml")
    var res = httpClient.SendAsync(req).GetAwaiter().GetResult();
    if (res.StatusCode == HttpStatusCode.OK)
        var response = res.Content.ReadAsStringAsync().Result;
        var cookie = cookies.GetCookies(new Uri("")).Cast<Cookie>().FirstOrDefault(c => c.Name == "FedAuth");

In this example we first create HTTP POST request to authentication.asmx using SOAP body retrieved above and then if authentication was successful read FedAuth cookies returned from server. After that we may use these cookies for making authenticated calls to other web services. In one of the future articles I will show how to do that.

Monday, January 25, 2021

Get access token for calling AAD secured Azure functions via PowerShell

Some time ago I posted PowerShell example which lists all Azure AD groups via Rest API: List Azure AD groups via Rest Graph API in Powershell. However if you have custom Azure functions app which is secured via AAD (see Call Azure AD secured Azure functions from C#) you may use similar approach to get access token based on client id/secret (i.e. which will use app permissions) for calling your Azure functions from PowerShell or from 3rd party clients like Postman.

In order to get access token for calling AAD secured Azure functions you may use the following PowerShell script:


$currentDir = $PSScriptRoot
$dllCommonDir = resolve-path($currentDir + "\Common\")

[System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllCommonDir, "Microsoft.Identity.Client.dll"))

function GetAccessToken($tenant, $clientId, $ClientSecret)
	$appCredentials = New-Object Microsoft.Identity.Client.ClientCredential -ArgumentList $ClientSecret
	$aadLoginUri = New-Object System.Uri -ArgumentList ""
	$authorityUri = New-Object System.Uri -ArgumentList $aadLoginUri, $Tenant
	$authority = $authorityUri.AbsoluteUri
	$redirectUri = "urn:ietf:wg:oauth:2.0:oob"
	$clientApplication = New-Object Microsoft.Identity.Client.ConfidentialClientApplication($ClientId, $authority, $redirectUri, $appCredentials, $null, $null)
	[string[]]$defaultScope = @("")
	$authenticationResult = $clientApplication.AcquireTokenForClientAsync($defaultScope).Result
	return $authenticationResult.AccessToken

Write-Host "Acquiring access token..." -foregroundcolor green
$accessToken = GetAccessToken $Tenant $ClientId $ClientSecret
if ([System.String]::IsNullOrEmpty($accessToken))
	Write-Host "Can't acquire access token" -foregroundcolor red
Write-Host "Access token successfully acquired" -foregroundcolor green
$filePath = [System.IO.Path]::Combine($currentDir, [System.String]::Format("_accesstoken_{0}.txt", [System.DateTime]::Now.ToString("yyyyMMddHHmmss")))
($accessToken) | Out-File $filePath -Append

It will fetch access token and will log it to text file. For calling this script you will need Microsoft.Identity.Client.dll assembly located in Common subfolder.

Saturday, January 9, 2021

One way to fix Failed to run web job error on Azure App Service

If you have just updated web job (see the following related articles: Upload WebJob to Azure App service with predefined schedule and How to remove Azure web job via Azure RM PowerShell) you may face with the following issue: when you will try to run updated web job you will get the error “Failed to run web job”. The problem is that error notification won’t contain any description and it will be hard to find the actual reason of this problem. In this article I will describe one of the reasons.

When web job is started it creates special lock file triggeredJob.lock in /data/jobs/triggered/{webJobName} folder of SCM site. You may check it if will connect to the site via FTP:

In the same folder there will be sub folders with timestamps which correspond to webjob runs. In these sub folders you will find output logs of each web job run – the same logs which are shown in Azure portal > App service > Web jobs > Logs. The purpose of this triggeredJob.lock file is to prevent launch of second instance of web job when previous instance didn’t finish yet. And the problem is that if during update web job was running this lock file may not be successfully deleted. As result when you will try to run updated version of web job you will get “Failed to run web job” error.

Solution will be to delete this file manually. However it is also not that straightforward. If you will try to remove it from FTP client you will get error

“The process cannot access the file because it is being used by another process”:

In order to delete it we need to stop both App Service and SCM site. Note that it is mandatory to stop both sites – if you will only stop App Service from Azure portal triggeredJob.lock will be still used by the process. Stopping of SCM site is more tricky than App Service. The process is described here: Full stopping a Web App. You need to go to Resource Explorer ( and select your App service. After that in JSON view on the right side click Edit and do 2 things:

  1. Change “state” from “Running” to “Stopped” – this is the same as if you would stop App service form Azure portal
  2. Find “scmSiteAlsoStopped” property and set it from “false” to “true” – it will stop SCM file

After that click PUT button on the top. It will stop both sites and you will be able to delete triggeredJob.lock now. Then go to Azure portal and start App service – it will start both sites. After all these steps you should be able to run web job again.