# Query Logs | ManageEngine Endpoint Central

## 1. Syntax

### Supported operators

| Operator | Description |
|---|---|
| `class_name` | Filter events to a specific event class (e.g. File Activity, Process Activity). Use this as the starting condition for every query. |
| `and` | Add a query constraint that includes the previous query. |
| `or` | Add a query constraint that excludes the previous query. |
| `groupby` | Find the count of repetitive values in the specified field. |
| `avg` | Returns the average of the values in the field. |
| `sd` | Returns the standard deviation of the given field. |
| `stats` | Returns the minimum, maximum, average, sum, sum of squares, variance, standard deviation of the given field. |
| `distinct` | Returns only the distinct (different) values. |
| `count_distinct` | Returns the count of the distinct values. |
| `count` | Returns the number of log messages that match the query. |
| `percentile` | Returns the percentile ranges of the given field. |
| `having` | Returns the log messages that having the given condition. |

### Numeric operators

| Operator | Description |
|---|---|
| `>` | Is greater than. |
| `<` | Is less than. |
| `=` | Is equal to. |
| `>=` | Is greater than and equal to. |
| `<=` | Is less than and equal to. |

### String operators

| Operator | Description |
|---|---|
| `=` | Is equal to. |
| `!=` | Is not equal to. |
| `contains` | Has a substring. |

## 2. Query Language

To search using query language, follow this format:

```sql
logtype="edrevents" and class_name="ClassName" and other_conditions
```

Where:

- `class_name` should be one of the supported EDR event classes: File Activity, Process Activity, Network Activity, DNS Activity, Registry Key Activity, Registry Value Activity, or Authentication.
- `other_conditions` could be any field condition specific to that event class.

**Example**

```sql
logtype="edrevents" and class_name="File Activity" and file.path contains "\\AppData\\Temp"
```

In this example, `File Activity` is the EDR event class and `file.path contains "\\AppData\\Temp"` is a condition.

## 3. Query Types

### 3a. Log Fetch Queries - Used to get the logs for a given criteria

**Template**

```sql
logtype="edrevents" and class_name="ClassName" and numericField [>,<,=,<=,>=] "value" or stringField [contains,!=,=] "substring"
```

**Examples**

```sql
logtype="edrevents" and class_name="Network Activity" and traffic.bytes>1000000
```

```sql
logtype="edrevents" and class_name="DNS Activity" and query.hostname endswith ".xyz" or query.hostname endswith ".top"
```

```sql
logtype="edrevents" and class_name="Authentication" and status_id=2
```

```sql
logtype="edrevents" and class_name="Authentication" and status_id=2 and actor.user.name!="svc_backup"
```

### 3b. Aggregation Queries - Used to group and categorize logs

**Template**

```sql
logtype="edrevents" and class_name="ClassName" and "numericField" [>,<,=,<=,>=] "value" groupby "fieldname" timeslice number[h,d,m]
```

**Examples**

```sql
logtype="edrevents" and class_name="Process Activity" and severity_id>=3 groupby actor.process.name
```

```sql
logtype="edrevents" and class_name="Network Activity" and traffic.bytes>1000000 timeslice 1h
```

```sql
logtype="edrevents" and class_name="Network Activity" histo traffic.bytes 1000000
```

```sql
logtype="edrevents" and class_name="DNS Activity" and rcode_id=3 groupby device.hostname timeslice 1h
```

## 4. Aggregation Query

Aggregation queries help you to categorise and group data to get meaningful insights. There are two types of aggregation: metric aggregation and bucket aggregation.

### 4a. Metric Aggregation

- [`max`](#combiningQueriesSumMinMaxAvg) - To find the maximum value of a field  
- [`min`](#combiningQueriesSumMinMaxAvg) - To find the minimum value of a field  
- [`sum`](#combiningQueriesSumMinMaxAvg) - To find the sum of all values of a field  
- [`avg`](#combiningQueriesSumMinMaxAvg) - To find the average value of a field  
- [`sd`](#combiningQueriesSD) - To find the standard deviation of a field  
- [`stats`](#combiningQueriesStats) - To find the minimum, maximum, average, sum, sum of squares, variance, standard deviation of the given field  
- [`percentile`](#combiningQueriesPercentile) - To find the percentile range of field  
- [`count_distinct`](#combiningQueriesCountDistinct) - To find count of unique values of a field  
- [`count`](#combiningQueriesCount) - To get the matching documents count for a given query criteria  
- [`top`](#combiningQueriesTop) - To get sample values of a field  

### 4b. Bucket Aggregation

- [`groupby`](#groupByOperator) / [`distinct`](#combiningQueriesDistinct) - To find unique values of a field  
- [`histo`](#histoOperator) - To group data into fixed-size intervals based on value of field  
- [`timeslice`](#timesliceOperator) - To group data based on time intervals  

#### Bucket Aggregation Options

- [`limit`](#groupByLimit)  
- [`sort`](#groupBySort)  
- [`having`](#groupByHaving) (Not applicable for `histo` operator)

## 5. `groupby` Operator

- `groupby` operator is used to get unique values of a field.
- `groupby` operator can be used on a field only when groupby option is enabled for that field.
- For a logtype, groupby enabled fields can be found under **Manage › LogTypes › Edit › filter "GroupBy" and select "Enabled"**

**`groupby` query format**

```sql
logtype="edrevents" and class_name="ClassName" groupby fieldname1 sort [_field,_count] [asc,desc] limit [1-1000]
```

**Multiple `groupby` query format**

```sql
logtype="edrevents" and class_name="ClassName" groupby fieldname1 sort [_field,_count] [asc,desc] limit [1-1000], fieldname2, fieldname3 sort [_field,_count] [asc,desc] limit [1-1000]
```

**Example**

```sql
logtype="edrevents" and class_name="Process Activity" and activity_id=1 groupby actor.process.name
```

### 5a. `groupby` Options

#### `sort`

- `sort` can be applied on both field values (`_field`) and count (`_count`).
- By default the results are sorted by count descending.
- To sort result by values use `_field`.

```sql
logtype="edrevents" and class_name="File Activity" groupby device.hostname sort _field desc
```

- To sort result by count use `_count`.

```sql
logtype="edrevents" and class_name="File Activity" groupby device.hostname sort _count asc
```

#### `limit`

- By default `limit` is 10.
- To get more than 10 results specify `limit no_of_results`.
- Maximum limit is 1000.

```sql
logtype="edrevents" and class_name="Process Activity" groupby actor.process.name limit 1000
```

#### `having`

- It is a post result operator.
- Used to apply condition and filter results of bucket aggregation queries.
- The condition can also be applied on `_count`.
- Can be used after `groupby` or `timeslice`.

```sql
logtype="edrevents" and class_name="File Activity" groupby file.path having _count>1000
```

### 5b. `groupby` Limitations

- `groupby` results sorted by document count (`_count`) cannot be paginated.
- `groupby` over string fields is limited to 100 characters. Longer strings will be truncated.

**Inaccurate Results**

Zoho Logs uses Elastic Search (ES) to index and query records. ES is distributed and partitions data into shards.

- Queries execute separately on each shard.
- Responses from shards are merged to get the final response.

This may cause:

- **Inaccurate top values**
- **Inaccurate document count**

Example:

```sql
logtype="edrevents" and class_name="File Activity" groupby file.ext limit 2
```

Each shard returns top 2 values. Merging may exclude eligible candidates from the overall top results.

Document count may also appear inaccurate. For example, `/put` may show `8` in results but actually have `10` documents when drilled down.

### 5c. How to get more than 1000 aggregate results?

> **Note:** More than 1000 values can be retrieved only using `sort _field`. They cannot be retrieved when `sort _count` is used.

**First query**

```sql
logtype="edrevents" and class_name="File Activity" groupby device.hostname sort _field asc limit 1000
```

**Subsequent queries**

```sql
logtype="edrevents" and class_name="File Activity" and device.hostname>"WORKSTATION-1000" groupby device.hostname sort _field asc limit 1000
```

This works even if the field is a string:

```sql
logtype="edrevents" and class_name="File Activity" and file.path > "C:\\Windows\\Temp\\z"
```

### 5d. Matched count vs `groupby` document count

- Total matched count may not equal sum of document counts in `groupby`.
- Total event match count includes events without the grouped field populated.
- Add a `remote_service_name` not empty criteria to get exact matching totals.

## 6. `histo` Operator

`histo` operator groups data into fixed-size intervals based on field value.

**Examples**

```sql
logtype="edrevents" and class_name="Network Activity" histo traffic.bytes 1000000 sort _count desc
```

```sql
logtype="edrevents" and class_name="Network Activity" and traffic.bytes<10000000 histo traffic.bytes 1000000
```

```sql
logtype="edrevents" and class_name="Process Activity" histo severity_id 1
```

```sql
logtype="edrevents" and class_name="Process Activity" histo severity_id 1 sort _field desc
```

### 6a. Range queries with `histo` operator

```sql
logtype="edrevents" and class_name="Network Activity" histo traffic.bytes range(1000000,1000000to10000000,10000000to100000000,100000000)
```

```sql
logtype="edrevents" and class_name="Network Activity" histo traffic.bytes range(1000000,10000000)
```

```sql
logtype="edrevents" and class_name="Network Activity" histo traffic.bytes range(1000000to10000000,10000000to100000000)
```

## 7. `timeslice` Operator

`timeslice` groups logs into specified time slices.

Possible units:

- `d` (day)
- `h` (hour)
- `m` (minute)

**Example**

```sql
logtype="edrevents" and class_name="File Activity" timeslice 5m
```

### 7a. Sort queries with `timeslice`

```sql
logtype="edrevents" and class_name="File Activity" timeslice 5m sort _count
```

```sql
logtype="edrevents" and class_name="File Activity" timeslice 5m sort _field desc
```

## 8. Combining Queries

### Simple query

```sql
logtype="edrevents" and class_name="File Activity" and file.path contains "\\AppData\\Temp" and actor.process.user.type_id=3
```

### Or

Returns a value that matches either condition:

```sql
logtype="edrevents" and class_name="Process Activity" and (activity_id=1 or activity_id=2 or activity_id=4)
```

### SUM(), MIN(), MAX(), AVG()

```sql
logtype="edrevents" and class_name="Network Activity" MAX(traffic.bytes) MIN(traffic.bytes) SUM(traffic.bytes) AVG(traffic.bytes)
```

### distinct

```sql
logtype="access" DISTINCT zuid
```

### sd

```sql
logtype="access" sd(time_taken)
```

### stats

```sql
logtype="edrevents" and class_name="Network Activity" stats(traffic.bytes)
```

### percentile

```sql
logtype="edrevents" and class_name="Network Activity" percentile(traffic.bytes)
```

```sql
logtype="edrevents" and class_name="Network Activity" percentile(traffic.bytes percents(5,10,20,25,50,99,100))
```

Default percents: `(1, 5, 25, 50, 75, 95, 99, 100)`

**Old format**

```sql
logtype="edrevents" and class_name="Network Activity" percentile(traffic.bytes) percents(5,25,50)
```

**New format**

```sql
logtype="edrevents" and class_name="Network Activity" percentile(traffic.bytes percents(5,25,50))
```

> **Note:** From search version 2, only the new percentile format is supported.

### count_distinct

```sql
logtype="edrevents" and class_name="File Activity" COUNT_DISTINCT(device.hostname)
```

> **Note:** Results are approximate.

### count

```sql
logtype="edrevents" and class_name="DNS Activity" and query.hostname contains "malware" count
```

### top

```sql
logtype="edrevents" and class_name="Network Activity" avg(traffic.bytes) top(dst_endpoint.ip) groupby status_id
```

```sql
logtype="edrevents" and class_name="Network Activity" avg(traffic.bytes) top(dst_endpoint.ip,src_endpoint.ip sort dst_endpoint.ip desc limit 2) groupby status_id
```

> **Notes:**
> - Field must support `groupby`.
> - Maximum `top` limit is 10.
> - Values may repeat when limit is used.

### Combining groupby and aggregation

```sql
logtype="edrevents" and class_name="Network Activity" MAX(traffic.bytes) MIN(traffic.bytes) GROUPBY device.hostname
```

### Combining timeslice and aggregation

```sql
logtype="edrevents" and class_name="Network Activity" AVG(traffic.bytes) MIN(traffic.bytes) MAX(traffic.bytes) timeslice 1h
```

### Combining groupby and timeslice

```sql
logtype="edrevents" and class_name="File Activity" groupby file.ext timeslice 1m
```

### Multiple groupby

```sql
logtype="edrevents" and class_name="File Activity" GROUPBY device.hostname,class_name,activity_name
```

### Combining groupby and having

```sql
logtype="edrevents" and class_name="Process Activity" groupby actor.process.name having _count>100
```

## 9. Alias for Metric Aggregation

For better readability, alias names can be provided.

**Template**

```sql
MetricAggregationName(FieldName (options)) as aliasName
```

**Examples**

```sql
logtype="edrevents" and class_name="Network Activity" avg(traffic.bytes) as average max(traffic.bytes) as "Maximum Bytes" min(traffic.bytes) as "Minimum Bytes"
```

```sql
logtype="edrevents" and class_name="Network Activity" percentile(traffic.bytes percents(100)) as "max" max(traffic.bytes)
```

```sql
logtype="edrevents" and class_name="File Activity" avg(severity_id) as "Average Severity"
```

Alias can be used in sorting:

```sql
logtype="edrevents" and class_name="Network Activity" avg(traffic.bytes) as "Average Bytes Transferred" groupby dst_endpoint.ip sort "Average Bytes Transferred"
```

> **Note:** Use double quotes if alias begins with uppercase, number, contains spaces, or matches reserved keywords.

## 10. Limitations to Query Constraints

- `count` can only be used at the end of the query.
- After `count`, no other criteria or aggregates are allowed.
- `count` and `count_distinct` cannot be used together.

## 11. Sorting with aggregation values

```sql
logtype="edrevents" and class_name="Network Activity" groupby dst_endpoint.ip sort avg(traffic.bytes) desc
```

With `having`:

```sql
logtype="edrevents" and class_name="Network Activity" groupby dst_endpoint.ip having _count>1000 sort avg(traffic.bytes) desc
```

Multiple `groupby` sorting:

```sql
logtype="edrevents" and class_name="Network Activity" groupby status_id sort avg(traffic.bytes), dst_endpoint.ip sort avg(traffic.bytes)
```

## 12. More Search Query Usecases

### To retrieve Process Activity events

```sql
logtype="edrevents" and class_name="Process Activity"
```

### To get access details of a particular app server

```sql
logtype="access" and _zl_host="127.0.0.1" and account="sas"
```

### To get access details of a particular app server group

```sql
logtype="access" and (group_name="Pre-Mail" or group_name="IMAP" or group_name="SMTPIN")
```

### To get access details of a particular user

```sql
logtype="access" and zuid="5873965"
```

### To find total bytes transferred in Network Activity

```sql
logtype="edrevents" and class_name="Network Activity" sum(traffic.bytes)
```

### To extract distinct processes on each device

```sql
logtype="edrevents" and class_name="Process Activity" DISTINCT(actor.process.name) GROUPBY device.hostname
```

### To get sum of traffic bytes per destination IP

```sql
logtype="edrevents" and class_name="Network Activity" sum(traffic.bytes) GROUPBY dst_endpoint.ip
```

### To retrieve maximum and minimum traffic bytes per device

```sql
logtype="edrevents" and class_name="Network Activity" MAX(traffic.bytes) MIN(traffic.bytes) GROUPBY device.hostname
```

### To extract distinct file paths per process UID

```sql
logtype="edrevents" and class_name="File Activity" DISTINCT(file.path) GROUPBY actor.process.uid
```

### To extract most frequently accessed file extensions

```sql
logtype="edrevents" and class_name="File Activity" COUNT(file.ext) GROUPBY file.ext
```

### To extract distinct destination hostnames per device

```sql
logtype="edrevents" and class_name="Network Activity" DISTINCT(dst_endpoint.hostname) GROUPBY device.hostname
```

### To extract Network Activity events by severity levels (2–6)

```sql
logtype="edrevents" and class_name="Network Activity" and severity_id=2 (or) ...
```

### To get unique device count for files accessed in system32

```sql
logtype="edrevents" and class_name="File Activity" AND file.path contains "\\system32" COUNTDISTINCT(device.uid)
```

### To get unique device hostnames for files accessed in system32

```sql
logtype="edrevents" and class_name="File Activity" AND file.path contains "\\system32" groupby device.hostname
```

### To get unique destination IP count for high traffic connections

```sql
logtype="edrevents" and class_name="Network Activity" AND traffic.bytes>10000000 COUNTDISTINCT(dst_endpoint.ip)
```

### To get unique destination IPs for high traffic connections

```sql
logtype="edrevents" and class_name="Network Activity" AND traffic.bytes>10000000 groupby dst_endpoint.ip
```

### To get field stats for network activity from powershell

```sql
logtype="edrevents" and class_name="Network Activity" AND actor.process.name="powershell.exe" MAX(traffic.bytes) MIN(traffic.bytes)
```

### To get Process Activity distribution over time

```sql
logtype="edrevents" and class_name="Process Activity" AND actor.process.name="powershell.exe" timeslice 1d
```

### Advanced Aggregation Queries

```sql
query_string groupby groupby_field sort sort_over_criteria sorting_order limit limit_value
```

Where:

- **query_string** is a normal query.
- **groupby_field** is the field over which groupby is applied.
- **sort_over_criteria**: `_field` for value, `_count` for value count.
- **limit_value** is maximum aggregate result limit.

## 13. Recent and Saved Search Queries

- You can reuse recent search queries from the left side of the search box.
- You can also save search queries for future use.