# SQL Functions

# Overview

These functions return the results of a user-defined SQL query.

# Reference

• executeSqlQuery
• queryToMap

# executeSqlQuery

executeSqlQuery(string query [, int limit]) collection[collection[string]]

Returns the result of SQL query. The first row consists of column labels. The function results are typically passed to addTable function for rendering.

The response is limited to 1,000 rows by default. An exception is thrown if row count exceeds the limit.

Examples:

executeSqlQuery('SELECT datetime, value FROM http.sessions WHERE datetime > current_hour LIMIT 2')

[
  [datetime, value],
  [2018-01-25T19:00:12.346Z, 1],
  [2018-01-25T19:00:27.347Z, 1]
]

executeSqlQuery("SELECT entity, avg(value) AS \"Average Value\" " +
                "FROM jvm_memory_used WHERE datetime > current_hour GROUP BY entity")

[
  [entity, Average Value],
  [atsd, 467675162.105]
]

query = 'SELECT datetime, value FROM http.sessions WHERE datetime > current_hour LIMIT 2'
//
addTable(executeSqlQuery(query), 'ascii', true)

+--------------------------+-------+
| datetime                 | value |
+--------------------------+-------+
| 2018-01-26T13:00:14.098Z | 23    |
| 2018-01-26T13:00:29.110Z | 22    |
+--------------------------+-------+

# queryToMap

queryToMap(string query, string keyColumn [, int limit]) map

Returns the result of SQL query as a map where key is keyColumn value and value is a map consisting of other column values. Value datatypes are based on SQL data types.

The response is limited to 1,000 rows by default. An exception is thrown if row count exceeds the limit.

Examples:

Map qr = queryToMap('SELECT entity, AVG(value) AS av, MAX(value) AS mv FROM cpu_busy WHERE datetime > current_hour GROUP BY entity');

// The map can be used in Initialization or Event Script
if (qr.size() > 0 && toNumber(qr['nurswgvml007']['av']) > 10) {
 //
}

{
  'nurswgvml007': { 'av': 10.2, 'mv': 16.0 },
  'nurswgvml010': { 'av': 24.2, 'mv': 32.4 },
  'nurswgvml012': { 'av': 15.0, 'mv': 20.0 }
}