What is the Microsoft Search KQL query field limits for a verticals? Is there limited number of characters or lines?
You know what is Microsoft 365 Search Vertical and what is KQL query in vertical configuration, right?
Under Microsoft 365 admin center Search and intelligence you can configure search verticals. There are some out-of-the-box verticals – like All, Files, Sites, People and you can configure custom one.
As a part of vertical configuration – you can specify KQL query – if you want e.g. limit search with some sites or content types etc.
The question is – how many sites I can specify in this query field? E.g. can I specify 1000 sites? 10k sites?
And the answer is: It does not matter, because the limit is not in number of characters or lines.
In my dev environment I was able to save 50,000 lines (~3M characters). But attempt to save 100K lines (6M symbols) has failed (due to timeout, I believe:
Again, as I said the problem is not here.
The problem is time required for search to apply query. I.e. when you ask search to bring you something – after it gets results from index and before display results to you it applies KQL query configured for the vertical. And this time is the bottleneck.
Here is what I got measuring search response time depending on query size:
Search
response time, seconds
KQL query # of lines
KQL query size, # of symbols
works
1
500
28,000
works
5
1000
59,000
works
9
2000
120,000
works
25
3000
180,000
works/fails
30
3500
208,000
fails
35
3600
214,000
fails
35
50,000
3,000,000
n/a
n/a
100,000 (can’t save KQL query
6,000,000 (can’t save KQL query)
Which means that after ~ 1000 lines (50,000 characters) KQL query size – query becomes too slow, and after ~3000 lines (180k chars) – can fail (due to timeout I’d say).
Every team in Microsoft Teams or a Microsoft 365 group or a SharePoint site must have an owner/owners. Otherwise to whom we communicate on any question – site/group permissions, membership, site/group/team retention policy, content classification etc. Who will be responsible for team/site/group content and configuration and who will provide access to this site for other users.
MS: A team in Microsoft Teams or a Microsoft 365 group and its related services can become ownerless if an owner’s account is deleted or disabled in Microsoft 365. Groups and teams require an owner to add or remove members and change group settings.
Recently Microsoft implemented a new feature: a policy that automatically asks the most active members of an ownerless group or team if they’ll accept ownership. Very important feature. TY Microsoft!
The configuration via wizard is straightforward and intuitive.
But still we have some questions regarding the policy.
Q: I support a large Microsoft 365 environment and we already have hundreds of ownerless groups. I’m concerned how users might react and whether our helpdesk support teams are ready for new type of tickets etc. Implementing the policy in test/stage environment does not make much sense, since there are no really active users etc. So, can I test this policy in production – on real users, but pilot it within a small amount of users or ownerless groups before applying to all groups in the environment.
A: Yes, you can do a test or pilot implementation in production limiting the impacted users or groups. – if you need to limit users who will be getting notifications a “pilot team” – during Step 1 “Notification Options” under “Specify who can receive ownership notifications” you can select “Allow only certain active members” and under “Specify security groups to allow members” you can select a security group – so only members from the specified security group will be sent ownership request.
another option – you can test the policy on a several selected m365 groups:
Q: I know the policy is applied to Microsoft 365 groups only. But I have many standalone sites with no owners (no site collection administrators). How do I deal with ownerless SharePoint sites?
A: You have at least two options: 1) Keep the site non-group based and Promote site “Full Control” users to site administrators 2) Convert standalone sites to Microsoft 365 group based sites (TBC)
Some findings:
If a public group does not have an owner – all requests to joint the team will be declined with “The team does not have an owner” message: (that means no new members, i.e. no new contributors, but read-only visitors access is sill available for everyone, as group is public):
Who can create Microsoft 365 Groups
It is possible to limit users – who can create Microsoft 365 Groups (please refer to Microsoft: Manage who can create Microsoft 365 Groups – there is a guide and PowerShell code sample). This might help to keep the environment under control – let say, “only managers can create groups”, or “contractor should not be able to create teams”.
It would be good if the configuration would be consistent in terms “if a user cannot create a group – user cannot be a group owner”. Unfortunately, with current configuration options (Aug 2022), this is not the case. Azure AD Directory Setting “GroupCreationAllowedGroupId” works only for creation. Later, when the group is create – it is possible to add to group as a group owner those who is not able to create group.
Issues
“Ownerless group policy configuration failed” error message. And “Failure in configuring ownerless groups policy” and “Please try again.” – seems like a permission issue. SharePoint admin, Teams admin or Group admin roles: cannot configure Ownerless Groups Policy. Global admin: yes, can configure Ownerless Microsoft 365 Groups Policy. What is the minimum role required? According to a recent update of the Microsoft’s article – “A Global administrator can create a policy…”
If I get token with (Graph, MSAL, PnP) and use this token for (Graph API, SharePoint CSOM API, SharePoint REST API) matrix.
An App used in this tests has Sites.FullControl.All MS Graph API and SharePoint API permissions, as well as FullControl ACS based permissions to SharePoint (AppInv.aspx).
What if you want specific users do not appear in Microsoft 365 SharePoint, Teams or Delve search results? Examples are:
a) secondary/admin accounts e.g. a person have several roles and several accounts under the same name, e.g. regular user: John Smith John.Smith@contoso.com administrative account: John Smith John.Smith.2@contoso.com
b) role or service accounts like SharePoint_Support@contoso.com
Getting multiple results for the same one person might confuse users and even lead to miscommunication and broken processes.
Set the profiles AD property msExchHideFromAddressLists to True or Yes,
Sync/wait, so finally SharePoint UPA service SPS-HideFromAddressLists property will be set (msExchHideFromAddressLists AD property is mapped to the UPA SPS-HideFromAddressLists)
Under SharePoint classic search – update query: {searchboxquery} to {searchboxquery} -“SPS-HideFromAddressLists”:1
It works perfect for classic search. The problem is it does not work as expected in modern Microsoft Search.
“People” vertical is not customizable so far. So we cannot change query to do the same trick. But… it seems like Microsoft is working on it so finally it should be done by ootb means.
Here is the current situation on how different services or search entry points respect SPS-HideFromAddressLists property:
So only “People” vertical in Microsoft search does not respect SPS-HideFromAddressLists (msExchHideFromAddressLists).
What about cloud-based accounts (not synchronized from local AD)?
There is a configuration setting “Show in global address list” that does the same job. It’s under Microsoft 365 admin center -> Active Users -> User – Edit -> Mail -> Show in global address list:
And another configuration settings “Hide from global address list (GAL)” under Exchange Admin Center:
(WIP)
User Account
1
2
3
4
5
Enabled
Yes
No
Yes
Yes
Yes
Licensed (E5)
No
Yes
Yes
Yes
Yes
m365 Admin Center: Show in Global Address List
n/a
No
No
Yes
No
Exchange Admin Center: Hide from global address list (GAL)
* – some users can see changes after hours, for some it takes days
Does it seem confused we have properties:
“Show in Global Address List” under m365 Admin Center
“Hide from global address list (GAL)” under Exchange Admin Center
“ShowInAddressList” Azure AD User object property
“SPS-HideFromAddressLists” SharePoint User Profile property
Are these properties related to each other?
(WIP) Let’s test it:
Action-Consequences (immediate reaction – minutes if not other mentioned)
“Show in Global Address List” under m365 Admin Center
“Hide from global address list (GAL)” under Exchange Admin Center
“ShowInAddressList” Azure AD User object property
“SPS-HideFromAddressLists” SharePoint User Profile property
New user created, license assigned
Yes
Off
null
False
Uncheck “Show in my organization address list” under Microsoft 365 admin center
No
On
after one minute: null after 24 hours: null
after one minute: False after 24 hours: False
Set “ShowInAddressList” Azure AD User object property to “True”
Yes
Off
True
False
Set “ShowInAddressList” Azure AD User object property to “False”
No
On
False
True
Some findings:
“Show in Global Address List” under m365 Admin Center and “Hide from global address list (GAL)” under Exchange Admin Center – same, i.e. if you change one – another will be updated momentarily But both does not affect “ShowInAddressList” Azure AD User object property and “SPS-HideFromAddressLists” SharePoint User Profile property and vise versa (???)
“SPS-HideFromAddressLists” SharePoint User Profile property is not changeable. If you try to change the property value you’ll get an error message: Set-PnPUserProfileProperty : Property Not Editable: This property can not be modified.
“ShowInAddressList” Azure AD User object property is editable and synchronized to “SPS-HideFromAddressLists” SharePoint User Profile property (takes minutes) but then search crawler must pick this change up (takes hours) to hide/show the user
Results are inconsistent 🙁
Note: Az module works fine too. I.e. Get-AzADUser instead of Get-AzureADUser and Set-AzADUser instead of Set-AzureADUser.
Sites.Selected MS Graph API permissions were introduced by Microsoft in March 2021. One year later, in 2022 they added SharePoint Sites.Selected API permissions.
Why is this so important? Because MS Graph API for SharePoint is still limited and cannot cover all possible needs. I’d estimate: 90% of applications use SharePoint CSOM, so developers have to use AppInv.aspx to provide permissions for their applications to SharePoint API.
But from this moment – having SharePoint API permissions in MS Graph – in theory – we can fully rely on permissions provided in Azure and – in theory – this should allow us disable SharePoint-Apps only principal:
Meantime I’ll test providing SharePoint Sites.Selected API permissions via Graph API call.
(wip) Test set #1: Certificate vs Secret
DisableCustomAppAuthentication: $false (SP-app-only spns are enabled). All applications have “write” access provided to a specific site only. Connecting with Connect-PnPOnline and then test access with Get-PnPSite
App / Get-PnPSite
Secret
Certificate
ACS based (Azure+AppInv)
OK
The remote server returned an error: (401) Unauthorized.
MS Graph API Sites.Selected
The remote server returned an error: (403) Forbidden.
The remote server returned an error: (401) Unauthorized.
SharePoint API Sites.Selected
OK
OK
MS Graph API + SharePoint API Sites.Selected
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
OK
App with no permissions
The remote server returned an error: (403) Forbidden
The remote server returned an error: (401) Unauthorized
(wip) Test set #2: Sites.Selected SharePoint vs MS Graph (secret)
DisableCustomAppAuthentication = $false (SP-app-only spns are enabled).
All applications have “write” access provided to a specific site only.
Using Client Secret (not a certificate)
Using PnP.PowerShell
Action/Via
SharePoint + MS Graph Sites.Selected “secret”
SharePoint Sites.Selected “secret”
MS Graph Sites.Selected “secret”
Connect-PnPOnline
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite
OK
OK
The remote server returned an error: (403) Forbidden.
Get-PnPList
OK
OK
Get-PnPListItem
OK
OK
Set-PnPSite
Attempted to perform an unauthorized operation.
Set-PnPList
Attempted to perform an unauthorized operation.
Set-PnPListItem
OK
OK
New-PnPList
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Add-PnPListItem
OK
(wip) Test set #3: Read vs Write vs FullControl
DisableCustomAppAuthentication = $false (SP-app-only spns are enabled). All applications have Sites.Selected SharePoint and MS Graph API permissions. Using Client Secret (not a certificate) Using PnP.PowerShell
Read
Write
FullControl
Connect-PnPOnline
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPList
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPListItem
Set-PnPSite
Set-PnPList
Set-PnPListItem
New-PnPList
Add-PnPListItem
(wip) Test set #5: Certificate vs Secret
C#, SharePoint CSOM, PnP.Framework
Findings
PnP.PowerShell Get-, Grant-, Set- and Revoke-PnPAzureADAppSitePermission cmdlets require Azure App with MS Graph Sites.FullControl.All app permissions (otherwise it says “Access denied”) and authentication via certificate (otherwise it says “This cmdlet does not work with a ACS based connection towards SharePoint.”)
The same actions – managing permissions for the client app to the specific site collections – could be done via Microsoft Graph Sites Permissions API using just secret-based authentication.
If an azure app does not have Sites.Selected API permissions configured – “Grant-PnPAzureADAppSitePermission” works as expected – no error messages – the output is normal – as if Sites.Selected API permissions were configured in the app. The same for Get-, -Set and Revoke-. Permissions provided for the app to the site are not effective though: Connect-PnPOnline works well, but all other commands – starting from Get-PnPSite – returns “The remote server returned an error: (403) Forbidden.”
If an app have no permissions to SharePoint – “Connect-PnPOnline” works ok, but “Get-PnPSite” return an error: “The remote server returned an error: (403) Forbidden.”
Set-PnPAzureADAppSitePermission gives an error message “code”:”generalException”,”message”:”General exception while processing” if the site is not specified.
AppInv is not working?
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
How to provide permissions for an Azure registered application with MS Graph SharePoint Sites.Selected API permissions to a specific site via calling Microsoft Graph API from PowerShell.
We need an “admin” application – Azure registered application with with Sites.FullControl.All MS Graph API permissions. This method can use secret, so we need Client Id and Client Secret for this “admin” app.
We also need a Client Id and Application Display Name for an Azure application with Sites.Selected MS Graph and/or SharePoint API permissions provided.
And we need our “target” site Url.
With PowerShell scripts you can:
Get Microsoft Graph Access Token with an “admin” app
Get client (target) site Id
Get current app permissions provided to client site
Add read or write permissions for the client app to the client site
Sites.Selected MS Graph API permissions were introduced by Microsoft in March 2021. It was a good move towards site-level access for non-interactive (daemon) applications, but still developers were limited with only what MS Graph API provides for SharePoint. SharePoint CSOM and REST API still provides much more than MS Graph API.
So developers had to use AppInv.aspx at site level to provide ACS-based permissions to their apps to be able to use SharePoint CSOM and REST APIs. The bad news is ACS-based permissions have some downsides so some SharePoint/m365/security engineers consider them legacy and deprecated. But if we decide to disable SharePoint App-only service principals – all apps with ACS-based permissions provided via AppInv.aspx will stop working.
2021: Microsoft Graph Sites.Selected API
Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So from now developers should be fully happy with API permissions provided in Azure (without SharePoint ACS-based permissions).
2022: SharePoint Sites.Selected API
Why is this so important? Because this should allow us to be able to switch from ACS based permissions provided in SharePoint via AppInv.aspx to Azure-provided permissions and as a consequence – disable SharePoint-Apps only principal (‘set-spotenant -DisableCustomAppAuthentication $true’).
Why we are eager to disable Custom App Authentication in SharePoint? Simply say, SharePoint App-only service principals are not trackable (they all appeared as a “spo_service@support.onmicrosoft.com” id in all logs) and hard to manage (there is no way to get list of existing/registered SP app-only service principals, sites and their owners) – see more in this article.
So, SharePoint Sites.Selected application API permissions provided in Azure is a significient step to make Microsoft 365 SharePoint environment more secure and manageble.
Update: Sites.Selected API MS Graph permissions was introduced by Microsoft in 2021. It was a good move towards site-level development, but still developers were limited with only what MS Graph API provides for SharePoint dev. So devs had to use AppInv.aspx at site level to provide ACS permissions to their apps to be able to use SharePoint CSOM and REST APIs. Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So now devs should be fully happy without ACS-based permissions.
Scenario
You have an application that needs access to Microsoft 365 SharePoint Online site/list/documents. Application is running without interaction with users – e.g. unattended, as daemon job.
There are two options you can authenticate to Microsoft 365 – with the secret or with the certificate. Authenticating with certificate is considered more secure.
Questions
What happens if SharePoint-Apps only principal is disabled (i.e. ‘set-spotenant -DisableCustomAppAuthentication $true’ )?
Why I’m getting 401 error when authenticating to SPO?
Why I’m getting 403 error when authenticating to SPO with secret?
What permissions to I need to work with SPO?
Findings
Note: we will use PowerShell 7.2 and PnP.PowerShell 1.9 to illustrate it.
Disabled SharePoint-Apps only principal
If SharePoint-Apps only principal is disabled in your tenant (i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $true ), then the only way you work with SPO from code is:
an App registered in Azure
API permissions provided via Azure (MS Graph, SharePoint)
Certificate is used
In all other cases (even your Connect-PnPOnline command complete successfully) – you will be getting error 401 (unauthorized) when trying Get-PnPTenant or Get-PnPTenantSite or Get-PnPSite
Enabled SharePoint-Apps only principal
If SharePoint-Apps only principals are enabled in your tenant (i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $false ), then you have two options to work with SPO from code:
Azure App with a secret (Client Id + Client Secret) and permissions to SharePoint provided via SharePoint ( AppInv.aspx )
Azure App with a certificate (Client Id + Certificate) and permissions provided via Azure (Microsoft Graph and/or SharePoint)
Error 401 while accessing SharePoint Online with PnP
Let say you administer Microsoft 365 SharePoint Online and you want to get a list of new SharePoint sites (e.g. sites created recently – during last day/week/month).
With GUI it’s done easily: SharePoint Admin Center -> Active Sites -> sort based on “Date Created” – done.
With PowerShell – not so simple. “Get-PnPTenantSite” cmdlet returns a site object but the object does not have “Created” field. It’s a web property. But to get a web object – you have to connect separately to each site and get root web object to check when the web was created. For small environments it is possible, for large environments it can take days… And still not nice. “Get-PnPTenantSite” with “-Filter” option would help, but “…Currently, you can filter by these properties: Owner, Template, LockState, Url.”
Get-SPOSite – similar experience.
Solution
Microsoft Graph API helps. It returns result in seconds and you can sort or filter results based on created date . Below are two methods: Option 1 is based on Search and filtering and Option 2 is based on Sites Search and sorting. So there are some pros and cons for each method.
Microsoft Graph Search API allows KQL in queries. So we can form a query with something like “created>=1/1/2021” and use entity type = ‘[“site”]’. Search should return only sites created after Jan 01, 2021.
This option is also based on Microsoft Graph API, but sites entry point, which allows search too and sort results by property “createdDateTime”. So we will just search for everything and select how many results we need based on createdDateTime property.
