> For the complete documentation index, see [llms.txt](https://baric6.gitbook.io/barics-knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://baric6.gitbook.io/barics-knowledge-base/development/microsoft-kql/kql-hunting-email-queries.md).

# KQL hunting email Queries

#### Look for a certain email

```kusto
EmailEvents
| where SenderFromAddress contains "Boltinc8@gmail.com"
| project RecipientEmailAddress, Subject
```

#### Get email with lab in the name

```kusto
EmailEvents
| where RecipientEmailAddress matches regex "^lab\\d{1,2}@.+$"
| project SenderFromAddress, RecipientEmailAddress
```

Get all emails that have a attachment with said name

```kusto
EmailAttachmentInfo
| where FileName contains "INV9783.pdf"
| project RecipientEmailAddress, FileName, Timestamp, SenderDisplayName, SenderFromAddress
```

#### Get all emails from Gmail and count

```kusto
EmailEvents
| where SenderFromAddress  contains "@gmail.com"
| summarize Count = count() by SenderFromAddress
| project SenderFromAddress, Count
```

#### Get all emails with Gmail

```kusto
EmailEvents
| where SenderFromAddress contains "@gmail.com" 
| project SenderFromAddress, RecipientEmailAddress, Subject
```

#### Hunt emails except

```kusto
EmailEvents
| where not(SenderFromAddress has_any("@yahoo.com", "@gmail.com", "@outlook.com", "@aol.com", "icloud.com"))
| summarize Count = count() by SenderFromAddress
| project SenderFromAddress, Count
```

#### Regex pattern matches any character that is not an ASCII character

```kusto
EmailEvents
| where Subject matches regex @"[^\x00-\x7F]"
| project RecipientEmailAddress, Subject, EmailAction
```

#### Finding Chinese character 

```kusto
EmailEvents
| where Subject matches regex @"[\u4E00-\u9FFF]"
| project Timestamp, RecipientEmailAddress, Subject, SenderFromAddress
```

#### Finding Spanish character 

```kusto
EmailEvents
| where Subject matches regex @"[áéíóúüñÁÉÍÓÚÜÑ]"
| project Timestamp, RecipientEmailAddress, Subject, SenderFromAddress
```

All Languages that are not English 

```kusto
EmailEvents
| where isnull(EmailLanguage) or EmailLanguage != "en" // Include emails with no language specified or not in English
| order by EmailLanguage asc // Sort by language in ascending order
| project RecipientEmailAddress, Subject, SenderFromAddress, EmailLanguage
```

#### Finding attachments sent from Gmail

```kusto
EmailAttachmentInfo
| where SenderFromAddress contains "gmail"
| project RecipientEmailAddress, SenderFromAddress, FileName
```

#### Finding attachments that are PDF sent from Gmail with a certain size

```kusto
EmailAttachmentInfo
| where FileType has "pdf"
| where SenderFromAddress contains "gmail"
| where FileSize > 40000 and FileSize < 50000
| extend FileSizeKB = FileSize / 1024.0
| project RecipientEmailAddress, SenderFromAddress, FileName, FileSize, FileSizeKB 
```

Attachments type count

```kusto
EmailAttachmentInfo
| summarize Count = count() by FileType
| order by Count desc
```

#### Email contains in subject

```kusto
EmailEvents
| where Subject contains "Direct Deposit"
| project SenderFromAddress, RecipientEmailAddress, Subject
```

#### Count **Sender domain** (sent-from)

```kusto
EmailEvents
| where Timestamp > ago(24h)
| extend SenderDomain = tostring(split(SenderFromAddress, "@")[1])
| summarize count() by SenderDomain
| order by count_ desc
```

#### Count **delivered-to domains** (recipients)

```kusto
EmailEvents
| where Timestamp > ago(24h)
| extend RecipientDomain = tostring(split(RecipientEmailAddress, "@")[1])
| summarize count() by RecipientDomain
| order by count_ desc
```