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

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

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

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

Examples

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

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

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

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

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

Template

logtype="edrevents" and class_name="ClassName"and"numericField" [>,<,=,<=,>=] "value"groupby"fieldname"timeslicenumber[h,d,m]

Examples

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

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

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

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

4. Aggregation Query

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

4a. Metric Aggregation

max - To find the maximum value of a field
min - To find the minimum value of a field
sum - To find the sum of all values of a field
avg - To find the average value of a field
sd - To find the standard deviation of a field
stats - To find the minimum, maximum, average, sum, sum of squares, variance, standard deviation of the given field.
percentile - To find the percentile range of field
count_distinct - To find count of unique values of a field
count - To get the matching documents count for a given query criteria
top - To get sample values of a field.

4b. Bucket Aggregation

groupby / distinct - To find unique values of a field
histo - To group data into different or fixed-size intervals based on value of field
timeslice - To group data based on time intervals

Bucket Aggregation Options

limit
sort
having (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

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

Multiple groupby query format

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

logtype="edrevents" and class_name="Process Activity"and activity_id=1groupby 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 the keyword _field

logtype="edrevents" and class_name="File Activity"groupby device.hostname sort_fielddesc

To sort result by count use the keyword _count

logtype="edrevents" and class_name="File Activity"groupby device.hostname sort_countasc

limit

by default limit is 10
To get more than 10 results we can specify "limit no_of_results"
we can get a maximum of 1000 results

logtype="edrevents" and class_name="Process Activity"groupby actor.process.name limit1000

having

It is a post result operator.
having operator is used to apply condition and filter the results of bucket aggregation queries.
The condition can also be applied on _count field.
having operator can be used after groupby or timeslice operators.

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 a maximum of 100 characters. The strings which have more than 100 characters will be truncated.
Inaccurate Results
- Zoho Logs internally uses the Elastic Search (ES) to index and query the log records. ES is distributed in nature i.e. the data will be partitioned into multiple shards to scale the huge amount of records.
- When search is performed, the query is executed in all the shards seperately. The query response from each shard is taken and merged to get the final response.
- This behaviour of ES results in two problems,
  - Inaccurate top values
  - Inaccurate document count
- For example, consider searching with query logtype="edrevents" and class_name="File Activity" groupby file.ext limit 2. This query will be executed in all the shards and each shard will return the top 2 file extension values for that shard.
- The total document count of /get is 10 but it is not present in the response. This is because the final response is derived from the response received from each and every shard. By merging the results from each shard we get /post - 9 and /put - 8 as response. Therefore an eligible candidate (field value) may not come in the top of the result.
- The document count is inaccurate in some cases. The actual document count of the /put is 10 but it is mentioned as 8 in the response. But when we further click the document count to get the logs with request_uri as /put, we will get all the 10 documents that contain /put.

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

Note: More than 1000 values can be retrieved only by using "sort _field" and, they cannot be retrieved when "sort _count" is used.

First query

logtype="edrevents" and class_name="File Activity"groupby device.hostname sort_fieldasclimit1000

Subsequent queries

logtype="edrevents" and class_name="File Activity"and device.hostname>[LAST_HOSTNAME_IN_PREVIOUS_RESULT] groupby device.hostname sort_fieldasclimit1000

Example

logtype="edrevents" and class_name="File Activity"groupby device.hostname sort_fieldasclimit1000

The above query gets the first 1000 results from which you can get the device hostname of the 1000th record (say WORKSTATION-1000). To get the next set of result, use the following query

logtype="edrevents" and class_name="File Activity"and device.hostname>"WORKSTATION-1000"groupby device.hostname sort_fieldasclimit1000

and so on...

This will work even if the field is a string.

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

5d. Matched count vs groupby document count

In a groupby query, the total messages match count may not match the sum of document count of each unique remote_service_name (e.g. 10.09k != 27 + 17 + 8 + 8).
The total event match count indicates the number of events that matched the given criteria (logtype="edrevents" and class_name="Process Activity" and activity_id=1). This includes events that do not have the field actor.process.name populated.
The exact total messages match count can be retrieved by adding a remote_service_name not empty criteria to the query so that it only matches documents that have the remote_service_name field in it. This way, you get the expected total message matched count (60 = 27 + 17 + 8 + 8).

6. histo Operator

histo operator is used to group data into different or fixed-size intervals based on value of field.

Examples

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

The above query provides distribution of traffic.bytes with an interval of 1000000 bytes sorted by respective counts in descending order

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

The above query provides distribution of traffic.bytes less than 10000000 bytes with an interval of 1000000 bytes

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

The above query provides distribution of severity_id

logtype="edrevents" and class_name="Process Activity"histo severity_id 1sort_fielddesc

The above query provides distribution of status sorted in descending order

6a. Range queries with histo operator

To find out the distribution of Network Activity events by traffic bytes: less than 1MB, 1MB to 10MB, 10MB to 100MB, and greater than 100MB, use the following query

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

To find out the number of request that took less than 1MB, 1MB to 10MB and greater than 10MB

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

To find out the number of request that took 1MB to 10MB and 10MB to 100MB

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

7. timeslice Operator

timeslice operator is used to group logs into specified slices of time. Possible timeslice units are d (day), h (hour) and m (minute)

Example

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

The above query provides distribution of logs in the specified slices of time.

7a. Sort queries with timeslice operator

To sort the timeslice or count, use the following queries

logtype="edrevents" and class_name="File Activity"timeslice 5m sort_count

logtype="edrevents" and class_name="File Activity"timeslice 5m sort_fielddesc

8. Combining Queries

We can group together multiple conditions as in the examples below.

Simple query

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

Returns a value that matches either of the conditions provided.

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

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

Using these operators, you can find the sum, minimum, maximum and average of the number fields in your logs.

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

distinct

By using distinct in the query, you can fetch a table that contains only distinct values.

logtype="access"DISTINCT zuid

To find the standard deviation of a field, use the following query.

logtype="access"sd(time_taken)

stats

To find the minimum, maximum, average, sum, sum of squares, variance, standard deviation of a field, use the following query.

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

percentile

Percentiles are a way of ranking data points from smallest to largest and dividing them into 100 equal parts. The 25th percentile is the value below which 25% of the data points fall, the 50th percentile is the "median", and the 75th percentile is the value below which 75% of the data points fall.

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

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

By default, percents range is (1, 5, 25, 50, 75, 95, 99, 100).

Old percentile format

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

New percentile format

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

Note: From search version 2, only the new percentile format will be supported.

count_distinct

Count distinct in a query fetches only the count of the distinct values.

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

Note: The results of count_distinct operator is approximate as getting exact value is an expensive process.

count

Using Count at the end of a query will give you the total number of log messages that match the constraints in the query.

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

top

Consider a usecase where you need average of traffic.bytes and a sample dst_endpoint.ip for each network status_id.

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

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

Note:

To perform top aggregation, the field should have groupby support.
Max limit that can be specified for top operator is 10.
When the limit option is used, the value of the field may repeat since multiple logs can have the same value.

Caution: Before using this operator, Have a discussion with Logs Team.

Combining groupby and aggregation together

The search query below generates a groupby table, with additional columns for aggregation values.

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

Combining timeslice and aggregation together

The search query below generates a timeslice table, with additional columns for aggregation values.

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

Combining groupby and timeslice together

The search query below generates a timeslice table, with an additional column for groupby values.

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

Multiple groupby

The search query below generates a table with multiple groupby options.

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

Combining groupby and having together

To filter the URLs with some condition, use the having operator.

Note: having operator won't work with less than operator.

logtype="edrevents" and class_name="Process Activity"groupby actor.process.name having_count>100

logtype="edrevents" and class_name="Process Activity"groupby actor.process.name having_count>100000sort_count

logtype="edrevents" and class_name="Process Activity"groupby actor.process.name having_count>100000sort_fielddesc

9. Alias for Metric Aggregation

For better readability, alias names can be provided for the metric aggregation values.

Template

MetricAggregationName(FieldName (options)) as aliasName

Examples

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

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

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

The alias name can also be used for sorting in Bucket Aggregation

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

Note: Enclose alias names in double quotes if they begin with an uppercase letter or a number, contain spaces, or match a reserved keyword.

10. Limitations to Query Constraints

count operator can only be used at the end of the query. After count operator, no other criteria or aggregate operators can be allowed.
count and count_distinct operators cannot be used at the same time.

11. Sorting with aggregation values

To sort dst_endpoint.ip with average traffic.bytes, the following query can be used.

logtype="edrevents" and class_name="Network Activity"groupby dst_endpoint.ip sortavg(traffic.bytes) desc

The above query can also be paired with having operator to get more filtered results.

logtype="edrevents" and class_name="Network Activity"groupby dst_endpoint.ip having_count>1000sortavg(traffic.bytes) desc

Sorting can also be applied with multiple groupby

logtype="edrevents" and class_name="Network Activity"groupby status_id sortavg(traffic.bytes), dst_endpoint.ip sortavg(traffic.bytes)

12. More Search Query Usecases

To retrieve Process Activity events

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

To get the access details of a particular app server

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

To get the access details of a particular app server group

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

To get the access details of a particular user

logtype ="access"and zuid="5873965"

To find the total bytes transferred in Network Activity

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

To extract the list of distinct processes on each device

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

To get the sum of traffic bytes per destination IP

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

To retrieve the maximum and minimum traffic bytes for each device

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

To extract distinct file paths per process UID

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

To extract the most frequently accessed file extensions

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

To extract distinct destination hostnames per device

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

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

logtype="edrevents" and class_name="Network Activity"and severity_id=2 (or) logtype="edrevents" and class_name="Network Activity"and severity_id=3 (or) logtype="edrevents" and class_name="Network Activity"and severity_id=4 (or) logtype="edrevents" and class_name="Network Activity"and severity_id=5 (or) logtype="edrevents" and class_name="Network Activity"and severity_id=6

To get unique device count for files accessed in system32

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

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 (traffic.bytes > 10MB)

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

To get unique destination IPs for high traffic connections (traffic.bytes > 10MB)

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

To get field stats (traffic.bytes) for network activity from powershell

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

To get the Process Activity distribution over time. Example: 1d, 5h, 3m

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

Advanced Aggregation Queries

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 criteria is to be applied.
sort_over_criteria - whether sorting needs to be done on "value" or "value count". use _field to apply sort over value or _count to apply sort over value count.
limit_value is the maximum limit of aggregate results.

13. Recent and Saved Search Queries

You can reuse your recent search queries which you can find on left side of the search box.

You can also save your search queries for future use.

Trusted by