[Tutorial] How to build filter query in: Get Exchange Mail Messages to get emails with attachment flag category size etc

Hi,
I have prepared a comprehensive compendium of knowledge on how to filter e-mails using KQL queries used in filtering e-mail messages in Get Exchange Mail Messages activity.

From the following set of information you will learn how to correctly construct a query syntax.
I have tested all the query properties below for correct operation on MS Office 2016 for corporate use. I have marked the ones that didn’t work in my company.

The KQL language was used in the queries used, which means: Keyword Query Language.

You can read more about KQL here:

Attribute operators:

Operator The value of the attribute
: … contains the specified value (takes both numeric and text values).
= … is equal to the given value (takes both numeric and text values).
> … is greater than the specified value.
< … is less than the specified value.
>= … is greater than or equal to the specified value.
<= … is less than or equal to the specified value.
<> … is not equal to the specified value.
.. … is within the range of the specified values (does not accept date ranges).

Boolean operators:

Operator Description
AND Returns search results that contain any text expressions or property constraints specified with the AND operator. You must specify any valid string expression and / or a valid property constraint both before and after the AND operator. This is the same as using the plus sign ("+").
NOT Returns search results that do not contain the specified free text expressions or property constraints. You must specify any valid string expression and / or a valid property constraint after the NOT operator. This is the same as using a minus sign ("-").
OR Returns search results that contain one or more of any specified text expressions or property constraints. You must specify any valid string expression and / or a valid property constraint both before and after the OR operator .

Operators to include and exclude content in the results:

Name Operator Behavior
Inclusion + Contains content with values matching the inclusion.This is the default if no character is specified. This is the same as using the AND operator.
Exclusion - Excludes content with values that match the exclusion. The same as using the NOT operator.

Wildcard operator:

To enable prefix matching, use the wildcard operator asterisk ("*"). You can specify part of a word starting at the beginning of a word followed by the wildcard operator in the query as follows. This query will match results that contain terms that start with “serv” followed by zero or more characters, such as “server”, “server”, “service”, etc. : serv*

Date and time:

KQL provides the datetime data type for date and time. The following ISO 8601 date and time formats are supported in queries:

YYYY-MM-DD
YYYY-MM-DDThh:mm:ss
YYYY-MM-DDThh:mm:ssZ
YYYY-MM-DDThh:mm:ssfrZ

*YYYY* specifies a four-digit year.
Note: Only four-digit years are supported.
*MM* specifies a two-digit month. For example, 01 = January.
*DD* specifies a two-digit day of the month (01 through 31).
*T* specifies the letter “T”.
*hh* specifies a two-digits hour (00 through 23); AM / PM indication is not allowed.
*mm* specifies a two-digit minute (00 through 59).
*ss* specifies a two-digit second (00 through 59).
*fr* specifies an optional fraction of seconds, ss; between 1 to 7 digits that follows the . after the seconds.

For example, 2012-09-27T11:57:34.1234567.

All date / time values must be specified according to the UTC (Coordinated Universal Time), also known as GMT (Greenwich Mean Time) time zone. The UTC time zone identifier (a trailing “Z” character) is optional.

Date Intervals:

Name Description
today It represents the time from the beginning of the current day to the end of the current day.
yesterday It represents the time from the beginning of the day to the end of the day before the current day.
this week (thisweek) It represents the time from the beginning of the current week to the end of the current week. When determining the first day of the week, the culture in which the text of the query was formulated is taken into account.
this month (thismonth) It represents the time from the beginning of the current month to the end of the current month.
last month (lastmonth) Represents the entire month prior to the current month.
this year (thisyear) It represents the time from the beginning of the current year to the end of the current year.
last year (lastyear) It represents the entire year preceding the current year.

Date ranges (today, yesterday, this week, this month, last month, this year, last year) are interpreted as numeric values, but cannot be used with the operator: ..

Use Cases:

Attribute The type of the variable Description Example
subject String Exact phrases or keywords in the subjects of your subjects. "subject:invoice for" ; "subject:welcome" ; "subject:welcome AND from:""john.smith@example.com"""
body String Any item property that contains the specified string value. "body:microsoft" ; "body:""uipath""" ; "body:please review"
attachmentnames ; attachment (not working) String Exact phrases or keywords in attachment file names. "attachmentnames:invoice.zip"
to String SMTP address, display name or user alias in the TO field. "to:""Name Surname""" ; "to:""john.smith@example.com"""
from String As above for the FROM field. "from:""john.smith@example.com"""
cc String As above for the CC field. "cc:""john.smith@example.com""" ; "cc:""smith"" +""example.com"""
bcc String As above for the Bcc field. "bcc:""john.smith@example.com""" ; "bcc:""smith"" +""example.com"""
participants String All the people fields in the email. The fields are: FROM, TO, CC, and BCC. "participants: administrator" ; "participants:""@example.com""" ; "participants:""john.smith@example.com"""
recipients (doesn't work) String All recipient fields in the email. The fields are TO, CC, and BCC. "recipients:""john.smith@example.com""" ; "recipients:""@example.com"""
category String Names or parts of the default Outlook category names. "category:""red""" ; IMPORTANT: write the color in the system language of Outlook / Exchange
importance String Available values: normal (default), high, low "importance:high OR low" ; "importance:high AND attachmentnames:.pdf"
kind Item type Available values: contacts ; docs ; email ; faxes ; im ; journals ; meetings ; notes ; posts ; rssfeeds ; tasks ; voicemail "kind:email OR contacts" ; "kind:meetings"
sent Date The specific date or time range during which the item was shipped. Format: yyyy-MM-DD or date range (today, yesterday, this week, this month, last month, this year, last year) "sent>=2022-05-01 AND sent<2022-05-02 AND received:today" ; "sent:last month"
received Date As above, when the item has been received. "received:today" ; "received> =2022-01-01" ; "received>=2022-05-01 AND received<2022-05-01" ; IMPORTANT: when writing the date: =2022-05-01, the value of date and time is assumed: =2022-05-01 00:00 ; searching for a specific whole day, the record >=2022-05-01 AND received<2022-05-02 is treated as >=2022-05-01 00:00 AND received<2022-05-02 00:00
hasattachment Boolean True if the item has at least 1 attachment. (Exchange 2016 and Online only) "hasattachment:true"
isflagged Boolean True if the item is flagged. (Exchange 2016 and Online only) "isflagged:true"
isread Boolean True if the item is read. (Exchange 2016 and Online only) "isread:false"
size Number The size of the item (including attachments) in bytes. "size>1000000" ; "size:1..100000 AND received:last week"

KQL Source examples:

7 Likes