Intro
KQL (Keyword Query Language) is a powerful syntax used primarily within Microsoft products like SharePoint and Microsoft Search to formulate search queries. Understanding its basic rules and operators such as AND
, OR
, and other principles is crucial for constructing effective search queries. Here’s a breakdown of these rules:
Boolean Operators:
-
AND: The
AND
operator is used to narrow down search results by requiring that all specified conditions must be met. For example,ContentType:Document AND Author:"John Doe"
retrieves documents authored by John Doe. -
OR: The
OR
operator broadens search results by retrieving items that match either of the specified conditions. For example,ContentType:Document OR ContentType:Folder
retrieves both documents and folders. -
NOT: The
NOT
operator excludes items that match the specified condition. For example,ContentType:Document NOT Author:"Jane Smith"
excludes documents authored by Jane Smith.
Grouping:
- Parentheses
()
can be used to group clauses together to control the logical order of operations. For example,(ContentType:Document OR ContentType:Folder) AND Author:"John Doe"
retrieves documents or folders authored by John Doe.
Wildcards and Fuzziness:
-
Wildcards: KQL supports
*
(asterisk) as a wildcard character to match any sequence of characters. For example,Title:proj*
matches documents with titles starting with “proj”. -
Fuzziness: KQL allows for fuzzy matching using the
~
(tilde) followed by a number to indicate how many edit distance operations (insertions, deletions, substitutions) are allowed. For example,Title:proj~2
matches documents with titles similar to “proj” within an edit distance of 2.
Field Operators:
- Field Specifiers: Fields such as
Title:
,Author:
,ContentType:
, etc., specify which metadata or content property to search within. For example,ContentType:Document
limits the search to documents only.
Range Queries:
- Range Queries: Using
..
(two dots) allows specifying a range for numerical or date fields. For example,Modified:{Today-7}..{Today}
retrieves items modified within the last 7 days.
Example Queries:
- Simple Query:
ContentType:Document AND Author:"John Doe"
- Complex Query:
(ContentType:Document OR ContentType:Folder) AND (Author:"John Doe" OR Author:"Jane Smith")
- Wildcard Query:
Title:proj*
- Fuzzy Query:
Title:proj~2
- Range Query:
Modified:{Today-30}..{Today}
Usage Notes:
- Syntax Sensitivity: KQL syntax is case-insensitive but syntax structure (e.g.,
AND
,OR
) is crucial for correct query interpretation. - Managed Properties: Ensure fields used in queries (
ContentType
,Author
, etc.) are properly configured and indexed in your search system. - Testing: Always test complex queries in a controlled environment to ensure they retrieve expected results.
Examples
* Search for Documents Modified in the Last 30 Days
{searchTerms} AND (FileExtension:docx OR FileExtension:pdf) AND Modified:{Today-30}..{Today}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.FileExtension:docx OR FileExtension:pdf
: Filters to show only Word documents and PDFs.Modified:{Today-30}..{Today}
: Filters to show documents modified in the last 30 days.
* Search for Items Created by Current User
This shows items (SharePoint files) which the user has created.
{searchTerms} AND Author:{User.Name}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Author:{User.Name}
: Filters to show items where the author is the current user.
* Search for Items Modified by Current User
This shows items on which the user has worked.
{searchTerms} AND Editor:{User.Name}
Explanation:
{searchTerms}
: This placeholder is replaced by the user’s search query.Editor:{User.Name}
: This part of the query filters results to show only items where the Editor (the person who last modified the item) is the current user.
* Search for Tasks Due Today
ContentType:Task AND DueDate:{Today}
Explanation:
ContentType:Task
: Filters to show only items of content type Task.DueDate:{Today}
: Filters to show tasks that are due today.
* Search for Tasks Due This Month in Specific Sites
ContentType:Task AND Path:https://yourtenant.sharepoint.com/sites/TEAMGR-* AND DueDate:{Today}..{EndOfMonth}
Explanation:
ContentType:Task
: Filters to show only items of content type Task.Path:https://yourtenant.sharepoint.com/sites/TEAMGR-*
: Limits the search to items within sites starting with TEAMGR…..DueDate:{Today}..{EndOfMonth}
: Filters to show tasks that are due this month.
* Search for Overdue Tasks
ContentType:Task AND DueDate<={Today}
Explanation:
ContentType:Task
: Filters to show only items of content type Task.DueDate<={Today}
: Filters to show tasks where the due date is on or before today (i.e., overdue tasks).
* Search for Tasks Assigned to Users From a Specific Department
ContentType:Task AND Department:Dept2
Explanation:
ContentType:Task
: Filters to show only items of content type Task.Department:Dept2
: Filters to show tasks assigned to users whose department property is set to “Dept2”. This requires the user profile properties to be indexed and searchable.
* Search for Tasks in Progress
ContentType:Task AND Status:"In Progress"
Explanation:
ContentType:Task
: Filters to show only items of content type Task.Status:"In Progress"
: Filters to show tasks that are currently in progress.
* Search Within a Specific SharePoint Site Collection
{searchTerms} AND Path:https://yoursitecollection.sharepoint.com/sites/specificsite
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Path:https://yoursitecollection.sharepoint.com/sites/specificsite
: Limits the search to items within a specific site collection.
* Search Within Multiple SharePoint Site Collections
This query template ensures that the search is limited to items within any of the three specified SharePoint site collections.
{searchTerms} AND (Path:https://yoursitecollection.sharepoint.com/sites/site1 OR Path:https://yoursitecollection.sharepoint.com/sites/site2 OR Path:https://yoursitecollection.sharepoint.com/sites/site3)
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Path:https://yoursitecollection.sharepoint.com/sites/site1
: Limits the search to items within the first site collection.Path:https://yoursitecollection.sharepoint.com/sites/site2
: Limits the search to items within the second site collection.Path:https://yoursitecollection.sharepoint.com/sites/site3
: Limits the search to items within the third site collection.
* Search Within Teams
This query allows you to search for documents inside your Teams. Microsoft Teams stores its data in SharePoint (for files) and Exchange (for messages), so you can target the SharePoint sites associated with Teams to search for files and documents.
{searchTerms} AND (Path:https://yourtenant.sharepoint.com/sites/Team1 OR Path:https://yourtenant.sharepoint.com/sites/Team2 OR Path:https://yourtenant.sharepoint.com/sites/Team3)
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.- Path:https://yourtenant.sharepoint.com/sites/Team1: Limits the search to items within the SharePoint site associated with Team1.
- Path:https://yourtenant.sharepoint.com/sites/Team2: Limits the search to items within the SharePoint site associated with Team2.
- Path:https://yourtenant.sharepoint.com/sites/Team3: Limits the search to items within the SharePoint site associated with Team3.
* Search Within All Teams
If all your team sites follow a naming convention such as TEAMGR-0001, TEAMGR-0002, etc., you can create a query in the PnP Search Web part that targets all sites following this pattern using a wildcard in the KQL query.
{searchTerms} AND Path:https://yourtenant.sharepoint.com/sites/TEAMGR-*
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.- Path:https://yourtenant.sharepoint.com/sites/TEAMGR-*: Limits the search to items within any site that starts with TEAMGR- in your SharePoint tenant.
* Search with Query String Parameters for Category and Date Range
{searchTerms} {?{QueryString.Category}Category:{QueryString.Category}} {?{QueryString.StartDate}Modified:{QueryString.StartDate}..{QueryString.EndDate}}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Category:{QueryString.Category}
: Filters by a category passed in the query string.Modified:{QueryString.StartDate}..{QueryString.EndDate}
: Filters by a date range passed in the query string.
* Search for Content with Specific Metadata
{searchTerms} AND Department:Finance AND ContentType:Document
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Department:Finance
: Filters to show only items where the Department metadata is set to Finance.ContentType:Document
: Filters to show only items of content type Document.
* Search for Items Tagged with Specific Terms
{searchTerms} AND (Tag:ProjectX OR Tag:Important)
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Tag:ProjectX OR Tag:Important
: Filters to show items tagged with either ProjectX or Important.
* Search for Recently Viewed Items by the User
{searchTerms} AND ViewedBy:{User.Name} AND LastViewedTime:{Today-7}..{Today}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.ViewedBy:{User.Name}
: Filters to show items viewed by the current user.LastViewedTime:{Today-7}..{Today}
: Filters to show items viewed in the last 7 days.
* Search for Items Excluding a Specific Folder
{searchTerms} -Path:https://yoursitecollection.sharepoint.com/sites/specificsite/Shared%20Documents/ExcludeFolder/*
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.-Path:https://yoursitecollection.sharepoint.com/sites/specificsite/Shared%20Documents/ExcludeFolder/*
: Excludes items from a specific folder.
* Search for Items Created by the Current User in the Last Year
{searchTerms} AND CreatedBy:{User.Name} AND Created:{Today-365}..{Today}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.CreatedBy:{User.Name}
: Filters to show items created by the current user.Created:{Today-365}..{Today}
: Filters to show items created in the last year.
* Search for Images in Team Sites
{searchTerms} AND Path:https://yourtenant.sharepoint.com/sites/TEAMGR-* AND (FileExtension:jpg OR FileExtension:png OR FileExtension:gif)
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Path:https://yourtenant.sharepoint.com/sites/TEAMGR-*
: Limits the search to items within any team site.FileExtension:jpg OR FileExtension:png OR FileExtension:gif
: Filters to show only image files.
* Search for Videos in Team Sites
{searchTerms} AND Path:https://yourtenant.sharepoint.com/sites/TEAMGR-* AND (FileExtension:mp4 OR FileExtension:avi OR FileExtension:wmv)
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Path:https://yourtenant.sharepoint.com/sites/TEAMGR-*
: Limits the search to items within any team site.FileExtension:mp4 OR FileExtension:avi OR FileExtension:wmv
: Filters to show only video files.
* Search for Videos Longer Than 5 Minutes
ContentType:Video AND DurationInSeconds>300
Explanation:
ContentType:Video
: Filters to show only items of content type Video.DurationInSeconds>300
: Filters to show videos with a duration longer than 300 seconds (5 minutes).
* Search for Large Files
This query can help manage the site collection storage.
{searchTerms} AND Size>1073741824
Explanation:
{searchTerms}
: Placeholder for the user’s search terms. This can be omitted if you want to search for all large files without specific keywords.Size>1073741824
: Filters to show items where the size is greater than 1GB (1GB = 1,073,741,824 bytes).
* Search for Suspiciously Small Files
This query can help find broken or empty files.
{searchTerms} AND Size<1000
Explanation:
{searchTerms}
: Placeholder for the user’s search terms. This can be omitted if you want to search for all large files without specific keywords.Size<1000
: Filters to show items where the size is less than 1000 bytes.
* Search for Items with Comments
{searchTerms} AND IsDocument:true AND IsContainer:false AND Comments:{searchTerms}
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.IsDocument:true AND IsContainer:false
: Filters to show only document items and exclude folders.Comments:{searchTerms}
: Searches within the custom managed property Comments for the search terms.
* Search for Items with Comments
IsDocument:true AND IsContainer:false AND Comments:*
Explanation:
IsDocument:true AND IsContainer:false
: Filters to show only SharePoint document items and exclude folders.Comments:*
: Filters to show documents where the Comments property is not empty.
* Search for Comments with a Specific Phrase
IsDocument:true AND IsContainer:false AND Comments:{searchTerms}
Explanation:
IsDocument:true AND IsContainer:false
: Filters to show only SharePoint document items and exclude folders.Comments:{searchTerms}
: Searches within the custom managed property Comments for the search terms. Ensure that the Comments field is properly configured and indexed in SharePoint so that it can be searched effectively using KQL.
* Search for Folders and Document Sets
{searchTerms} AND IsContainer:true
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.IsContainer:true
: This query filters the search results to include only items that SharePoint identifies as containers. In SharePoint’s context, containers typically refer to folders or document sets.
* Search for Items with High Importance
{searchTerms} AND Importance:High
Explanation:
{searchTerms}
: Placeholder for the user’s search terms.Importance:High
: Filters to show items marked with high importance.
* Search for Calendar Events
ContentType:Event AND EventDate:{Today}..{EndOfYear}
Explanation:
ContentType:Event
: Filters to show only calendar events.EventDate:{Today}..{EndOfYear}
: Filters to show events occurring from today to the end of the year.
* Be More Specific
Do not hesitate to combine the queries above.
(Path:https://yourtenant.sharepoint.com/sites/department1 OR Path:https://yourtenant.sharepoint.com/sites/department2)
AND ContentType:Document
AND Author:"Arleta Wanat"
AND (FileType:docx OR FileType:xlsx OR FileType:pptx)
AND Created>{Today-365}
AND (Modified:{Today-7}..{Today})
AND Size>{1024}
AND (Keywords:"project update" OR Keywords:"report")
AND IsDocument:true
AND IsContainer:false
Final Notes:
- Ensure that the tokens you use match the actual parameters or variables available in your context.
- Test your query templates thoroughly to confirm they behave as expected with various input conditions.