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.
Figure 1: Using the condition card builder to compose a KQL query
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).
Figure 2: Composing a search query in the KQL editor
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.
Figure 3: The KQL editor autocompletes properties and operators
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.
Figure 4: The KQL editor detects a syntax error
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.
Figure 5: Suggesting user principal names for a search condition
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.
2 Replies to “KQL Editor Makes Content Search Queries Easier to Compose”
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
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
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