A quick guide to building queries using ONQL

This OpManager Nexus Query Language (ONQL) quick guide helps you get started with writing queries to retrieve and analyze monitoring data. It provides a quick reference cheat sheet of commonly used clauses and syntax, a step-by-step walkthrough for building a query, and a comparison of ONQL with SQL to help you understand familiar query concepts. It also helps you avoid common errors and build valid queries.

6 steps to a query

Follow the steps below to build a structured query using ONQL:

1. Pick a category and the columns you want

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time))

The reporttype clause specifies the monitor category for which data should be retrieved; it is followed by a single pipe |. The include() clause defines the columns to be returned in the query results. Attributes such as monitor names can be specified directly. However, while using an aggregation function, such as avg(), the performance metrics can be enclosed within the aggregation function.

2. State the time window

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time)) timeperiod last_24_hours

When using time-dependent performance or availability attributes, you must specify a time period. Otherwise, the query will return an error. For other attributes that do not depend on a time period, the query fetches the recent data by default if no time period is specified.

3. Keep only certain monitors with filterby

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time)) filterby(monitoring_inventory.monitor_name contains "prod") timeperiod last_24_hours

The filterby() clause filters resources based on their inventory attributes, such as the monitor name, type, tag, or group. It uses operators such as is and contains to define conditions. These conditions will have values enclosed in double quotes. For example:

filterby(monitoring_inventory.monitor_name is ("Zylker", "Zylker_user"))

4. Keep only rows exceeding a certain threshold with having

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time)) filterby(monitoring_inventory.monitor_name contains "prod") having(avg(performance.response_time) > 500) timeperiod last_24_hours

The having() clause filters query results based on current status and availability. Specify the aggregation function and attribute, followed by a comparison operator such as >, << /code>, or = and the value to compare against. You can use is and is_not operators when filtering by status.

5. Order the rows with sort

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time)) filterby(monitoring_inventory.monitor_name contains "prod") having(avg(performance.response_time) > 500) sort avg(performance.response_time) desc timeperiod last_24_hours

The sort clause orders query results based on a selected column. It does not require parentheses, but you must specify the sorting direction using asc for ascending order or desc for descending order. Use desc to display the highest values first, helping you identify the top-performing or worst-performing monitors, depending on the metric.

6. Turn one number into a trend with timeslice

reporttype="web_experience" | include(monitoring_inventory.monitor_name, monitoring_inventory.monitor_type, avg(performance.response_time)) filterby(monitoring_inventory.monitor_name contains "prod") having(avg(performance.response_time) > 500) sort avg(performance.response_time) desc timeperiod last_24_hours timeslice hour

With the timeslice clause, the selected time period is divided into hourly intervals, allowing you to view data for each hour and analyze trends over time. You can also split the query across multiple lines for better readability without affecting its execution.

Fixed rules for writing ONQL queries

Always follow these three essential rules when writing ONQL queries.

1. Follow the predefined clause order: ONQL clauses must appear in a specific sequence: reporttype, include(), filterby(), having(), sort, timeperiod, and timeslice. While some clauses are optional, their order cannot be changed.
2. Use only one pipe (|) after reporttype: A single pipe | must separate reporttype from the remaining clauses. All subsequent clauses must be separated by white space without additional pipes. For example, include(...) | filterby(...) is invalid.
3. Use lowercase syntax and enclose text values in double quotes: ONQL keywords, clauses, and operators are case-sensitive and must be written in lowercase. For example, INCLUDE(...) is invalid. Enclose text values in double quotes. Unquoted values are permitted only when they contain letters, numbers, or underscores (_).

Cheat sheet

The following cheat sheet provides a quick reference to commonly used ONQL clauses, their syntax, and their purpose, helping you build queries with the appropriate structure.

reporttype="" |
include( [agg[|agg]]() [in ""] [as ""], … )
filterby(    [and|or …] )
having( () 

What are the differences between ONQL and SQL?

If you are familiar with SQL, understanding how its concepts relate to ONQL can help you get started with writing queries. The following comparison highlights the key differences in their syntax, clauses, and functionality, enabling you to identify the ONQL equivalents of familiar SQL operations.

The comparison of how ONQL and SQL achieve the same result using different query syntax is given below:

Key differences to remember when moving from SQL to ONQL

• Follow the predefined clause order: ONQL clauses must appear in a fixed sequence: reporttype, include(), filterby(), having(), sort, timeperiod, and timeslice, where applicable.
• Use the appropriate operators: The filterby() clause uses word-based operators such as is and contains, whereas having() uses comparison operators such as >, << /code>, and =. You can use is and is_not operators when filtering by status.
• Aggregate performance metrics: Performance metrics can be specified with an aggregation function, such as avg(), min(), or max(). If no aggregation function is specified, by default, avg() is applied while fetching the data. Raw metric values can be retrieved only in supported contexts.
• Avoid unsupported SQL operations: ONQL does not support joins, subqueries, arithmetic expressions, or aggregation functions such as sum() and count().
• Enclose text values in double quotes: Use double quotes (" ") to specify text values. Unlike SQL, ONQL does not support single quotes as string delimiters.
• Use sorting to prioritize results: Specify sort ... desc to display the highest values first. Unlike SQL's LIMIT, this changes the order of results but does not restrict the number of rows returned.

For example, imagine that you want to retrieve the average CPU usage of production servers over the last 24 hours, with the servers having the highest usage displayed first. The corresponding queries built in both languages are:

SQL:

SELECT name, AVG(cpu) AS avg_cpu
FROM metrics
WHERE name LIKE '%prod%' AND ts >= NOW() - INTERVAL '24 hours'
GROUP BY name
ORDER BY avg_cpu DESC

ONQL:

reporttype="server" | include(monitoring_inventory.monitor_name, avg(performance.cpu_usage)) filterby(monitoring_inventory.monitor_name contains "prod") sort avg(performance.cpu_usage) desc timeperiod last_24_hours

FAQs

1. Why cannot a performance metric be used without an aggregation function?

Performance metrics can be specified with an aggregation function, such as avg(), min(), or max(), to calculate values over the selected reporting period. If no aggregation function is specified, by default, avg() is applied while fetching the data. For example, use avg(performance.cpu_usage) to retrieve the average CPU usage. Inventory and availability attributes do not require aggregation functions.

2. Is it possible to limit the query results to the top 10 rows?

ONQL does not support a LIMIT clause to restrict the number of returned rows. However, you can use sort desc to arrange results in descending order and identify the first 10 rows. Note that sorting does not reduce the total number of rows returned.

3. Why cannot a pipe (|) be used between clauses?

ONQL allows a single pipe | after the reporttype clause to separate it from the remaining clauses. All subsequent clauses must be separated by white space. However, the pipe is also used within include() to apply multiple aggregation functions to the same attribute.

4. What is the difference between filterby() and having()?

The filterby() clause selects monitors based on their inventory attributes, such as the monitor name, type, or group. It uses word-based operators such as is and contains.

The having() clause filters query results based on aggregated performance or availability values. It uses comparison operators such as >, < , and = to evaluate measured values against specified conditions. You can use is and is_not operators when filtering by status.

5. Can the OR operator be used to combine conditions within the having() clause?

No. The having() clause only supports the and operator for combining multiple conditions. All specified conditions must match for a result to be returned.

6. What happens if the time period is not specified?

If the timeperiod clause is omitted, ONQL uses last_month as the default reporting period, which represents the previous calendar month. Specify a time period explicitly to ensure that the query retrieves data for your intended duration. However, if the query includes a time-dependent attribute, you must explicitly specify the timeperiod clause.

7. Why is timeslice rejected sometimes?

The supported timeslice values depend on the selected reporting period and the feature in which the query is executed. For example, in custom reports, last_30_days supports day and week, whereas last_quarter does not support days.

Refer to the supported time period and timeslice combinations in the ONQL Syntax documentation.

8. Why is hours not accepted as a unit?

ONQL supports predefined unit abbreviations rather than full unit names. For example, use hrs instead of hours.

Supported units include:

• Time: ms, secs, mins, hrs, days
• Data size: bytes, kb, mb, gb, tb, KiB, MiB, GiB, TiB
• Data transfer rate: bps, kbps, mbps, gbps, tbps, bytes/s, KiB/s, MiB/s, GiB/s, TiB/s

Unit conversions are supported only within the same unit family. Attributes representing percentages or counts cannot be converted.

9. What is the difference between -1M and -1m in custom_expression?

Time units in custom_expression are case-sensitive. The lowercase m represents minutes, while the uppercase M represents months, with each month calculated as 30 days.

For example:

• -1m represents the last one minute.
• -1M represents the last 30 days.

10. How to find the supported attributes available for a category?

Press Ctrl + Space (Windows), Option + Space (macOS), or Shift + Space (Linux) in the query editor to view context-aware suggestions. The suggested attributes are generated based on the monitor categories and attributes available in your account.

If an attribute is not listed, it indicates that the attribute is not supported for the selected monitor category.

11. How to generate an ONQL query using natural language?

Click the Zia icon in the Query Editor and describe the monitoring data you want to retrieve in natural language. Zia generates an ONQL query based on your description and may provide alternative suggestions.

Review the generated query and make any necessary changes before executing it.

12. What is the difference between a clause and a function in ONQL?

A clause defines a specific part of the query, such as the data to retrieve, the conditions to apply, or how the results should be organized. For example, include() specifies the columns to return, filterby() defines which monitors to include, whereas sort determines the order of the results.

A function performs a specific operation within a clause, such as calculating an aggregated metric value. For example, avg() calculates the average of a performance metric, while min() and max() return its lowest and highest values.

In short, clauses define what the query should do, while functions define specific operations performed within those clauses.

Related articles

• Understanding ONQL and how it works
• ONQL error codes and troubleshooting