Table of Contents
Helping People Build Better KQL Queries, One Search at a Time
In May 2021, Microsoft introduced a new user interface for content searches (including core eDiscovery) in the Microsoft 365 compliance center. At the time, I was critical about the change because the new interface is slower and (still) buggier than the old. Nevertheless, change is an ongoing influence in cloud services, and you’ve got to imagine that the developers introduce nee features or upgrades to existing capabilities for good reason. At least, that’s the theory.
Which brings me to a change which happened some time ago that I completely overlooked. Message center notification MC288050 (October 4) announced the arrival of a Keyword Query Language (KQL) editor to help compliance administrators build queries for content searches, including the searches used in core eDiscovery and advanced eDiscovery. Microsoft 365 roadmap item 88582 reports that the KQL editor is still in preview as of November 2021.
Condition Card Builder
Put simply, the value of the KQL editor is that it stops people making mistakes when they compose queries for content searches. Up to now, building a query involved handcrafting the set of keywords and conditions necessary to find information. The GUI offered some help, such as date pickers to set a date range for a query, but not much. The roll-your-own query facility is now referred to as the condition card builder and it’s still available for those who like composing KQL queries. Figure 1 shows a query using two keywords (search terms) and a condition.

The KQL Editor
You can switch between the condition card builder and KQL editor as often as you like. In this instance, switching to the KQL editor reveals the query created using the entered keywords and conditions. In effect, we have a starting point to refine the query (Figure 2).

Let’s say that we’re only interested in Word documents which contain the keywords. Type AND to add a new clause and then at least the first two characters of a property to search against. The KQL editor then proposes the matching conditions and operators (like :, for equals) which can be used. Microsoft refers to these hints as “property restrictions.” The hint used in Figure 3 is “file” because we want to search for a specific file type. The KQL editor suggests autocompletion of matching properties, and we can select Filetype: and then docx to complete the condition.

If the KQL editor notices a syntax error or some missing element, it flags the issue and tells you what the problem is (Figure 4). The experience is somewhat like that of editing code using an ISE and makes it easy to ensure that a query is valid and will run when submitted for processing.

Another interesting facility is the autocompletion for user principal names when searching based on email recipients (Figure 5). This works for the From, To, Recipients, and Participants properties. The names come from the tenant’s Azure AD and include guest accounts.

You can also copy and paste queries from other searches to use as the basis for a new search. This isn’t a huge advantage for simple searches, but it saves time when you deal with complex, multi-condition search queries. After pasting a query into the KQL editor, the editor checks the query to make sure that it doesn’t contain errors.
A Step Forward
The KQL editor is a nice addition to content searches. Even with the error checking done by the KQL editor, it’s still possible to create queries that just don’t work. For instance, I was able to compose this query in the KQL editor, which accepted the syntax without any problem:
“Azure B2B Connect*” AND “Teams” AND (filetype:”doc OR filetype:pdf”) AND LastModifiedTime=”this year” AND (from:James.Abrahams@office365itpros.com OR from:Ben.James@Office365itpros.com)
The filetype condition has quotation marks in the wrong place and the query mixes email and document conditions together (the Author property is more appropriate to search for documents).
In any case, you can’t expect to disengage your brain entirely when composing search queries. The KQL editor helps, but humans need to create and check the queries and validate that the result results are those expected.
So much change, all the time. It’s a challenge to stay abreast of all the updates Microsoft makes across Office 365. Subscribe to the Office 365 for IT Pros eBook to receive monthly insights into what happens, why it happens, and what new features and capabilities mean for your tenant.
Hi. I’ve been trying to get more info on KQL since i am using the PnP Search webparts in SharePoint.
I am trying to get all pages FileType:equals(“aspx”) but want to exclude those that have Main in the name but cannot finde a Not contain or similar operator. Also, between clauses, do i use a comma? thank you
I don’t know because I haven’t tried this with SharePoint. You could file a support incident with Microsoft and ask them to clarify their documentation. Or (this might be quicker) submit feedback at the bottom of https://docs.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference