Query Examples

This article comprehensively describes some real-world query examples.

Viewing or changing various settings of User Accounts is a common administrative task. The query language may help Iotellect Server administrators to perform bulk changes to several user accounts, or view certain settings for multiple user accounts in a single ordered table.

To access the basic User Account settings we'll use a variable called "childInfo" ("User Information") defined in the User context. The value of this variable has a single record with several fields containing user's first/last name, country etc.

Here is a typical view of the User Info variable:

Now let's use one of the simplest possible queries to get the value of this variable from just one context.

Query Text:

SELECT
 *
FROM
 users.admin:childInfo

Result of this query is a table showing the value of the childInfo variable defined in the users.admin context.

When viewing or editing the properties of some system object or hardware device, its context name is usually shown in the window header:

This query contains two clauses: SELECT and FROM. The SELECT clauses says that we should select all fields ("*"). The FROM clause contains a single Context Reference ("users.admin:childInfo") used to build a table on which the query will be executed. The contents and format of this table will match the value of the childInfo variable exactly.

All fields in the result of this query will be editable except for the fields that are defined as read-only in the format of childInfo variable.

When viewing or editing the properties of some context (such as the User context), you usually see variable descriptions rather than variable names. But we must use variable names in query text: Variable names are usually shown in the tooltips that appear when the mouse hovers over a variable description

Query Result:

Now let's select the properties of all users in the system:

Query Text:

SELECT 
  * 
FROM 
  users.*:childInfo

The Context Reference (users.*:childInfo) contained in the text of this query is actually a mask of contexts. During query execution this mask will resolve to the list of all user contexts accessible by the user executing the query. See Context References chapter for more info.

This query results in a table with the values of the childInfo variable of every user account accessible by the user who is executing the query. It will probably contain multiple records, one per every user account. The result of this query will also be editable.

This query is built-in to the Iotellect Server distribution. It is called All Users.

Query Result:

For example, you can now change the first name and last name of some user and save the changes:

Changes will be immediately visible in other parts of the system:

Now let's sort the table.

Query Text:

SELECT 
  * 
FROM 
  users.*:childInfo order by childInfo$name desc

This query outputs almost the same table as the previous one, but the rows in this table are sorted by Username (name field in the format of the childInfo variable) in descending order. The query contains a two-component Field Reference (childInfo$name), used to refer to a field within the table built when resolving the Context Reference users.*:childInfo.

When we examine value of "childInfo" variable in Iotellect Client or other Iotellect Server User Interface, field descriptions are shown in the table header instead of field names. But we must use field name in order to refer to it in query text. Field names are usually shown in tooltips that appear when mouse hovers over the field header:

We can edit the query and assign an alias, info, to our Context Reference, and then use a three-component Field Reference that includes this alias:

SELECT 
  * 
FROM 
  users.*:childInfo as info 
ORDER BY 
  info.childInfo$name desc

The result of this query is exactly like the previous one.

Now let's select just a limited number of fields by further modifying the same query.

Query Text:

SELECT 
 childInfo$name, childInfo$firstname, childInfo$lastname 
FROM 
 users.*:childInfo 
ORDER BY
 childInfo$name desc

This query contains a number of Field References in the SELECT clause. Since the fields are specified explicitly, the query result will be not editable, unless we add the write-back fields. We'll add them to this query in one of the next examples.

The result of this query will contain just three columns.

Query Result:

Let's now use a Query Language expression to show the first and last name of users in a single column, to make browsing more convenient. We'll also add a new column to the query result, user's country.

Query Text:

SELECT 
  childInfo$name, 
  CASEWHEN((childInfo$firstname IS NULL), '', childInfo$firstname)  || ' ' || CASEWHEN((childInfo$lastname IS NULL), '', childInfo$lastname),
  childInfo$country
FROM
  users.*:childInfo
ORDER BY 
  childInfo$name desc

This query selects three fields from the source table. The first and third fields contain the user's username and country respectively. The value of the second field is calculated using an expression. This expression gets a user's first name and appends a space and the user's last name to it. The double pipe marks (||) used are just a standard SQL operator, used for concatenating the strings. The first and last names are transformed using the CASEWHEN built-in function to make sure that an empty string is shown if value of the first or last name is NULL ("<Not set>"). Note that the name for the second column is generated automatically.

Query Result:

Now let's assign a custom name ("label") to the second column to make the result more readable.

Query Text:

SELECT 
 childInfo$name, 
 CASEWHEN((childInfo$firstname IS NULL), '', childInfo$firstname) || ' ' || CASEWHEN((childInfo$lastname IS NULL), '', childInfo$lastname) as name,
 childInfo$country
FROM 
 users.*:childInfo
ORDER BY 
 childInfo$name desc

Second column is now called "name" in query result.

Query Result:

Now let's make it possible to change the users' countries -- add write-back fields to make the result editable. To add them we should assign an alias to the Context Reference ("table alias"), because write-back fields (CONTEXT_ID, PARENT_ID and RECORD_INDEX) have to be specified for a given context reference. To specify which reference should be written back, you have to use an alias. Carefully read the example below to figure it all out.

Query Text:

SELECT 
 info.childInfo$name, 
 CASEWHEN((info.childInfo$firstname IS NULL), '', info.childInfo$firstname) || ' ' || CASEWHEN((info.childInfo$lastname IS NULL), '',    info.childInfo$lastname) as name,
 info.childInfo$country,
 info.CONTEXT_ID,
 info.PARENT_ID,
 info.RECORD_INDEX
FROM 
 users.*:childInfo as info
ORDER BY 
 info.childInfo$name desc

In the result of this query, Country column will be editable. Username column is still read-only because it is read-only in the source variable ("childInfo"). Name column is read-only because it is calculated using an expression.

Note that additional users “test1”, “test2”, “test3”, and so on, have been added.

Query Result:

Now let's add some filtering rules to the query. As an example, let's select only users whose usernames doesn't contain word "test".

Query Text:

SELECT 
 info.childInfo$name, 
 CASEWHEN((info.childInfo$firstname IS NULL), '', info.childInfo$firstname) || ' ' || CASEWHEN((info.childInfo$lastname IS NULL), '',    info.childInfo$lastname) as name,
 info.childInfo$country,
 info.CONTEXT_ID,
 info.PARENT_ID,
 info.RECORD_INDEX
FROM 
 users.*:childInfo as info
WHERE
 info.childInfo$name NOT LIKE '%test%'
ORDER BY 
 info.childInfo$name desc

This query contains an additional WHERE clause, specifying which records should be included in the result.

Query Result:

The last line of the next example shows the LIMIT clause, used to restrict query result to a specified range of rows.

Query Text:

SELECT 
 info.childInfo$name, 
 CASEWHEN((info.childInfo$firstname IS NULL), '', info.childInfo$firstname) || ' ' || CASEWHEN((info.childInfo$lastname IS NULL), '',    info.childInfo$lastname) as name,
 info.childInfo$country,
 info.CONTEXT_ID,
 info.PARENT_ID,
 info.RECORD_INDEX
FROM 
 users.*:childInfo as info
WHERE
 info.childInfo$name NOT LIKE '%test%'
ORDER BY 
 info.childInfo$name desc
LIMIT 1 OFFSET 1

Result of this query includes just one (LIMIT 1) record (the 2nd in the result of previous query). We use OFFSET 1 here.  This means we're going to get 1 record (LIMIT 1) starting from the second record (OFFSET 1). The OFFSET clause tells Iotellect  to start taking record from the second record onwards (the first record's offset is 0).

Query Result:

This example is similar to selecting the basic properties of all users as described in the previous example, but here we use the deviceServerInfo variable ("Device Server Information") defined in the Device Server context.

Query Text:

SELECT
 * 
FROM
 users.*.deviceservers.*:deviceServerInfo

The text of this query includes a single Context Reference (users.*.deviceservers.*:deviceServerInfo) with a context mask that expands to all Device Server contexts which are accessible with the permissions of the user executing the query. The results of this query are editable, except for the fields that are read-only in the definition of the deviceServerInfo variable.

This query is built-in to the Iotellect Server distribution. It is called "All Device Servers".

This example shows the usage of Context References that include multiple variables/fields.

Query Text:

SELECT 
 info.deviceServerInfo$owner, 
 info.deviceServerInfo$name, 
 info.status$servertods, 
 info.status$dstoserver 
FROM 
 users.*.deviceservers.*:status:deviceServerInfo as info 
ORDER BY 
 info.deviceServerInfo$owner, 
 info.deviceServerInfo$name

Context Reference in this query ("users.*.deviceservers.*:status:deviceServerInfo") refers two variables defined in Device Server context: "deviceServerInfo" mentioned in previous example and "status" ("Device Server Status") that contains some real-time values related to Device Server functioning. The values of both variables contain just one record, so the table that will be built after resolving this context reference will contain all fields that appear in the values of both variables and one record per every Device Server accessible by the user executing query.

From this table we select four columns: two that appear in the value of deviceServerInfo (Owner and name of Device Server) and two from status (Incoming and outgoing traffic). The table is sorted by two fields: first by Device Server owner, then by Device Server name (because some Device Servers may have the same owner).

This query is built-in to the Iotellect Server distribution. It is called "Device Server Traffic Statistics".

This example shows how context functions may be used in the query.

Query Text:

SELECT 
  * 
FROM 
  external_device_servers.*:buzz()

This query is very interesting because it doesn't produce any output, but causes a side-effect on the server. It includes one Context Reference ("external_device_servers.*:buzz()") that involves the buzz() function ("Buzz Device Server") defined in the External Device Server context. This function is called without parameters, as specified by the empty parenthesis. The buzz function causes Device Server to blink its LEDs, helping to locate visually. Thus, this query helps to locate all Device Server that are visible by the Iotellect Server in the local network segment. It it very important to run the Discover Device Server action before executing this query. This action forces Iotellect Server to find all local Device Servers by sending broadcast network commands. See External Device Servers for more info.

If discovery was not performed, the External Device Servers context will not have any children, the external_device_servers.*context mask will not resolve to any context and no action will be performed on query execution.

The output of buzz() function contains no fields of records, so this query doesn't return any data. But its execution causes all External Device Server to buzz helping to locate them.

This query is built-in to the Iotellect Server distribution. It is called "Buzz All External Device Servers".

This example shows how aggregation functions can be used.

Query Text:

SELECT 
  SUM(status$servertods) as server_to_ds, 
  SUM(status$dstoserver) as ds_to_server 
FROM 
  users.*.deviceservers.*:status

In one of the previous examples we've mentioned the status variable ("Device Server Status") that is defined in the Device Server context and contains statistics of traffic between Iotellect Server and the Device Server. This query calculates how many bytes were sent to all currently visible Device Server and how many bytes were received from them. The query result contain one record, because the SUM aggregation function is used.

Since the query result contains just one row, it is shown using a "vertical" two-column layout, where field names are shown in the first column and field values in the second.

This example could be easily modified to use other aggregation functions: AVG will help to calculate an average traffic of Device Server MIN and MAX may be used to calculate minimum and maximum traffic respectively COUNT may be used to find the number of Device Server (although this example is not the best one for doing that)

This example shows how to execute queries on multiple tables (built from multiple Context References).

Query Text:

SELECT 
  d.deviceServerInfo$owner || '.' || d.deviceServerInfo$name as device_server,
  d.deviceServerInfo$blocked,
  u.childInfo$city,
  u.childInfo$country
FROM
  users.*:childInfo as u, users.*.deviceservers.*:deviceServerInfo as d
WHERE
  u.childInfo$name = d.deviceServerInfo$owner

This query has two Context References in the FROM clause. The first one is used to build a table containing all basic settings of all users, and the second one results in a table of all basic settings of all Device Server. These tables are then joined together based on the formula contained in WHERE clause ("u.childInfo$name = d.deviceServerInfo$owner"). The query result has four fields: first contains full Device Server name in the form "Owner Name"."Device Server Name". Second shows "Blocked" status of the Device Server. Two other columns show city and country of the user who owns the Device Server (which are probably the city and country where Device Server is located).

This example shows how to use SQL JOIN and hidden fields to combine data from two tables generated from two different context references.

When Iotellect is used for network management, Iotellect Server operates with devices using SNMP protocol. Every SNMP device has a so-called Interface Table (ifTable variable of device context) containing statistics of device's network interfaces. The below query finds all network interface of all SNMP devices those status is down (i.e. ifOperStatus field equals to 2).

The query joins description of device context (i.e. device name) to the description of network interface to show them in one table.

SELECT 
  info.info$description, ift.ifTable$ifDescr, ift.ifTable$ifOperStatus
FROM
  users.*.devices.*:ifTable as ift LEFT OUTER JOIN users.*.devices.*:info as info ON ift$CONTEXT_ID = info$CONTEXT_ID 
WHERE
  ift.ifTable$ifOperStatus = 2

This query calls get() function from events context and filters resulting table by username.

SELECT
 *
FROM
 events:get("users", "login") as logins
WHERE
 logins.get$username = 'operator'

Note, that same results may be obtained by passing filter expression into the get() function call:

SELECT
 *
FROM
 events:get("users", "login", "{username} == 'operator'") as logins

This query finds all network devices those response time is over 500 ms and shows their names along with current response time.

SELECT 
  info.info$description AS device, 
  ping.ping$averageTime AS average_round_trip_time
FROM
  users.*.devices.*:info AS info
RIGHT OUTER JOIN  
  users.*.devices.*:ping AS ping
ON
  ping$CONTEXT_ID = info$CONTEXT_ID
WHERE
  ping.ping$averageTime > 500
ORDER BY 
  ping.ping$averageTime DESC

This query finds all network (SNMP-enabled) devices that have an average load of all their processors higher than 90%. It works perfectly for multi-processor devices.

SELECT 
  info.info$description AS device,
  avg(processors.hrProcessorTable$hrProcessorLoad) AS processor_utilization_percentage
FROM
  users.*.devices.*:hrProcessorTable AS processors
LEFT OUTER JOIN 
  users.*.devices.*:info as info 
ON 
  processors$CONTEXT_ID = info$CONTEXT_ID
GROUP BY 
  device
HAVING
  avg(processors.hrProcessorTable$hrProcessorLoad) > 90
ORDER BY
  processor_utilization_percentage DESC

This query calculates number of SNMP devices of every type (type is specified by sysObjectID variable provided by device itself) and shows summary table. Note, that descriptions of every device type are taken from common table called deviceTypes.

SELECT 
  coalesce(types.value$description, snmp.sysObjectID$sysObjectID) AS device_type,
  COUNT(*) AS device_count
FROM
  users.*.devices.*:status:sysObjectID AS snmp
LEFT JOIN 
  common.deviceTypes:value AS types
ON
  snmp.sysObjectID$sysObjectID = types.value$type
WHERE
  snmp.status$driver = 'com.tibbo.linkserver.plugin.device.snmp'
GROUP BY 
  device_type
ORDER BY
  device_count DESC

This query uses statistics function defined in Utilities context to extract last values of a statistical channel. The channel is configured to count network device's interface errors that occurred every hour/day/month.

The query groups all device's interface tables into one and use it as a base (FROM users.*.devices.*:ifTable AS interfaces). Then it LEFT OUTER JOIN's last hour's data from two statistical channels named "ifInOctets" and "ifOutOctets" (LEFT OUTER JOIN utilities:statistics("users.*.devices.*", "ifInErrors", null, "hour") AS in_errors). And finally it LEFT OUTER JOIN's generic device information (LEFT OUTER JOIN  users.*.devices.*:info AS info). The query also features filtering, sorting and results row limit.

SELECT
 info.info$description AS device,
 interfaces.ifTable$ifDescr AS interface,
 in_errors.statistics$average * 3600 AS incoming_errors,
 out_errors.statistics$average * 3600 AS outgoing_errors
FROM
 users.*.devices.*:ifTable AS interfaces
LEFT OUTER JOIN
 utilities:statistics("users.*.devices.*", "ifInErrors", null, "hour") AS in_errors
ON
 interfaces.ifTable$ifIndex = in_errors.statistics$key
 AND
 interfaces$CONTEXT_ID = in_errors.statistics$context
LEFT OUTER JOIN
 utilities:statistics("users.*.devices.*", "ifOutErrors", null, "hour") AS out_errors
ON
 interfaces.ifTable$ifIndex = out_errors.statistics$key
 AND
 interfaces$CONTEXT_ID = out_errors.statistics$context
LEFT OUTER JOIN
 users.*.devices.*:info AS info
ON 
 info$CONTEXT_ID = interfaces$CONTEXT_ID
WHERE
  length(interfaces.ifTable$ifDescr) > 1
ORDER BY
  incoming_errors + outgoing_errors DESC
LIMIT
  10

In the Iotellect Time and Attendance, the system processes attendance data for cardholders forming a complex hierarchy that includes organizations, divisions, and possibly departments. Since context path of every cardholder starts with context path of the division he belongs to, we can build a query that lists cardholders by divisions:

SELECT
 divisions.childInfo$name,
 cardholders.childInfo$name
FROM
 organizations.Organization1.divisions.*.cardholders.*:childInfo as cardholders,
 organizations.Organization1.divisions.*:childInfo as divisions
WHERE
 substring(cardholders.CONTEXT_ID, 1, length(divisions.CONTEXT_ID)) = divisions.CONTEXT_ID

In the below example, each device has custom_childContext tabular custom property that lists all devices dependent on it. This property has contextPath field that contains paths of dependent devices.

The query checks triggers listed in activeInstances tables of all alerts. Only triggers those source field equals to context path of selected device (i.CONTEXT_ID) or listed in custom_childContext table of selected device will be selected.

Thus, the query will return alert triggers that are active either for current device (dev1) or for its dependent devices.

SELECT 
 *
FROM 
 users.*.alerts.*:activeInstances AS at 
WHERE 
 at.activeInstances$source IN 
 ( 
 SELECT 
  i.CONTEXT_ID 
 FROM users.admin.devices.dev1:info AS i 
 UNION SELECT 
  c.custom_childContexts$contextPath 
 FROM users.admin.devices.dev1:custom_childContexts AS c
 )

The variableHistory function can load historical values of any context variable. This is suitable to show those values in a report, on a dashboard, etc. But what if you want to show values of multiple variables in a single table? For example, you're measuring temperature and history, and want the measurements to be presented as a single table. Here is a solution.

The below query loads 10-days history of two separate variables in two virtual tables and JOINs those tables by putting together values those timestamps are equal with one second precision.

SELECT
  sineTimeString, sineTime,  sineValue, triangleTime, triangleValue
FROM
  (
  SELECT
    TO_CHAR(sine.variableHistory$vUpdateTime, 'DD.MM.YYYY H:MI:SS') as sineTimeString, sine.variableHistory$vUpdateTime as sineTime, sine.variableHistory$value as sineValue
  FROM
    utilities:variableHistory("users.admin.devices.demoVirtualDevice", "sine", 'dateAdd(now(), -10, "m")') as sine
  )
JOIN
  (
  SELECT
    TO_CHAR(triangle.variableHistory$vUpdateTime, 'DD.MM.YYYY H:MI:SS') as triangleTimeString, triangle.variableHistory$vUpdateTime as triangleTime, triangle.variableHistory$value as triangleValue
  FROM
    utilities:variableHistory("users.admin.devices.demoVirtualDevice", "triangle", 'dateAdd(now(), -10, "m")') as triangle
  )
ON
sineTimeString = triangleTimeString

The below query refers three other queries by listing their data variables in the FROM clauses of primary and nested SELECT statements. The three query result sets are glued together using UNION operators.

SELECT
  hpux.data$icon AS icon,
  hpux.data$device AS device,
  hpux.data$context AS context,
  hpux.data$cpu AS cpu,
  hpux.data$max_threshold as max_threshold,
  hpux.data$min_threshold as min_threshold,
  hpux.data$max_threshold - hpux.data$cpu as margin,
  CASEWHEN(hpux.data$max_threshold - hpux.data$cpu < 0, 
    2, 
    CASEWHEN(hpux.data$max_threshold - hpux.data$cpu < 10, 1, 0) ) as condition
FROM
  users.admin.queries.cpu_HPUX:data as hpux
UNION ALL
(SELECT
  standard.data$icon AS icon,
  standard.data$device AS device,
  standard.data$context AS context,
  standard.data$cpu AS cpu,
  standard.data$max_threshold as max_threshold,
  standard.data$min_threshold as min_threshold,
  standard.data$max_threshold - standard.data$cpu as margin,
  CASEWHEN(standard.data$max_threshold - standard.data$cpu < 0, 
    2, 
    CASEWHEN(standard.data$max_threshold - standard.data$cpu < 10, 1, 0) ) as condition
FROM
  users.admin.queries.cpu_standard:data as standard
UNION ALL
(SELECT
  sun.data$icon AS icon,
  sun.data$device AS device,
  sun.data$context AS context,
  sun.data$cpu AS cpu,
  sun.data$max_threshold as max_threshold,
  sun.data$min_threshold as min_threshold,
  sun.data$max_threshold - sun.data$cpu as margin,
  CASEWHEN(sun.data$max_threshold - sun.data$cpu < 0, 
    2, 
    CASEWHEN(sun.data$max_threshold - sun.data$cpu < 10, 1, 0) ) as condition
FROM
  users.admin.queries.cpu_Sun:data as sun))
ORDER BY 
  margin

This example illustrates how to evaluate Iotellect expressions from queries. The query lists device setting variables (technically, variables that belong to remote group and its subgroups) of a device along with their values. Values are retrieved by calling EVALUATE_TO_STRING function that performs a call to getVariable() expression language function and encodes obtained tables into strings using encode() function. The getVariable() call refers to default context (pointed by dc() function), so this default context is passed to EVALUATE_TO_STRING function by using variables$CONTEXT_ID field reference.

Since the query will return variable values as strings, it's necessary to use query's Output Format parameter to specify that value column should be converted to Data Table type.

SELECT 
 variables.variables$name as name,
 EVALUATE_TO_STRING('encode(getVariable(dc(), "'+variables.variables$name+'"))', variables$CONTEXT_ID, null) as value
FROM 
 users.admin.devices.device1:variables as variables
WHERE
 SUBSTRING(variables.variables$group, 1, 7) = 'remote|'

Was this page helpful?

Tell us more

Send

Thank you!