Advanced API Queries in Digi Remote Manager UI 3.x

You may not know that you can take advantage of a complex query language in the search box of the new Digi Remote Manager 3.x. 

Underlying the simple search box is an advanced yet simple query language we call the v1 API query language.

We have a great article about the Remote Manager platform v1 Stream Queries that hinted at the depths of the v1 API query language.

Let's talk about how you can use those advanced query features of the API in the Remote Manager web UI.

 

V1 API Query Language Introduction

First, let's talk a little about the v1 API query language. The v1 query language is a little bit like SQL. We've simplified it a bit to let us use it on data stores that are not necessarily SQL. Today we use it on Microsoft SQL, Solr and Plain Java object data stores and we believe we can keep extending it to other types of data quite easily.

Most v1 APIs (for example: ws/v1/devices/inventory, ws/v1/alerts/inventory, ws/v1/users/inventory, ws/v1/subaccounts) accept an HTTP parameter named 'query'. This parameter can be used to construct the queries that we're talking about in this document.

For example, if you run a v1 query like this (this uses the httpIe tool from https://httpie.org/ to run HTTP queries from the command line):

https://remotemanager.digi.com/ws/v1/devices/inventory query=="group = '/Marvel' and location contains 'Rochester'"

It might return something like this:
v1 API Query output (Expand source)

General Syntax Rules

  • The query language itself is case insensitive, so startsWith is the same as startswith.
  • Data representation and sorting are in general case insensitive. So "name contains 'Broadway'" and "name contains 'broadway'" return the same rows. 
  • Use single quotes on text, enumerated types and absolute timestamps. So, registration_date >= '2019-02-04T15:02:00.000Z' is an example of an absolute timestamp condition.
  • The v1 API query supports and/or logical operators and use parenthesis to group conditions. So, an example condition is: name contains 'Bus' and (carrier = 'Daffy Duck''s Digital Services' or carrier = 'Bugs Bunny Bulk Bandwidth, LLC')
  • Escape quotes with an additional quote. So, carrier = 'Daffy Duck''s Digital Services' would represent a query with a single quote.
  • Like IS/IS NOT NULL in SQL, you can use the empty keyword with the = and <> conditions to look for the presence or absence of values. Empty fields are null or empty string.

Data Types and Conditions

This table describes the available data types and shows example condition that could be used with the v1/devices/inventory API to find matching devices. Other v1 inventory APIs support different fields.

Note that the reserved value empty can be used to represent a null value or an empty field.

 
Data Type Description and Example Conditions Condition Types

Text

(Includes Tags or Group fields)

Any string or text field no matter the length. Use quotes.

location contains 'Broadway'

description <> 'Bus123'

name = empty

tags = 'UnderTest'

group endswith '/Northwest'

=, <, >, <=, >=, <>,

startsWith, contains, endsWith

Numeric

Any numeric value, int, long, decimal, etc.

Supports hex values for integer and long fields.

signal_percent <= 25

vendor_id = 0xFE00000A

=, <, >, <=, >=, <>
Enum

An enumerated type takes on a distinct set of values.

For example, on/off or high/medium/low.

connection_status = 'connected'

maintenance_mode = 'on'

=, <, >, <=, >=, <>
Timestamp

A timestamp is usually returned as a UTC value from the API. 

It is often converted to local time in the UI. You can build a condition using an absolute or relative time specification.

An absolute time is always the fully specified ISO8601 value with UTC offset information. For example, currently offset '-05:00' is central time.

A relative time specification is an expression, something like -1w (representing 1 week ago). Relative time intervals always starts with a minus '-' and can be any number of seconds(s), minutes(m), hours(h), days(d), weeks(w). No leap year, or other adjustments happen, i.e. a week is always exactly 7 days or 604800 seconds.

registration_date >= '2019-06-01T00:00:00.000Z'

last_update <= '2020-01-01T01:15:00.000-05:00'

last_connect < -24h

last_update >= -5m

=, <, >, <=, >=, <>
Geoposition

Geoposition fields support conditions within or outside of a bounding box.

The bounding box specifies the coordinates of the form: [ SW corner longitude, SW corner latitude, NE corner longitude, NE corner latitude ].

See https://gist.github.com/graydon/11198540 for some great examples

For example, looking to see if the device is approximately in the united states:

geoposition within [-171.791110603, 18.91619, -66.96466, 71.3577635769]

within, outside

 

  

Remote Manager 3.x UI queries

In the Remote Manager 3.x UI, table listings have a search box that does simple searches.

Simple searches are sufficient for a lot of searching. 

After typing a term and hitting enter or selecting the search button, the new rows are displayed with a brief indicator of your search criteria displayed.

Note that, by default, the "Advanced search" widget is deselected. 



Simple Search in the UI

 

We can understand in a bit more detail what a simple search is actually doing. After typing your simple search and seeing the displayed rows. Select the Advanced search widget to show the current search in its advanced form.

It should be clear from this search box that there's more going on here than meets the eye, and that this search box can be used for more advanced features than is immediately indicated.

The simple search for "Deadpool" actually searches all of the fields "Name", "Mac", "Device ID", "Tag", "Description", "Notes" and "Type" for the search term. Note: If we look back at the v1 query language conditions, it happens to be doing a "contains" condition for each of those fields (though we can't tell this directly from the UI).


 

Advanced Search in the UI

 

So, how can we use and take the most advantage of the advanced search? First some details:

In the advanced search mode, there are a number of individual fields that can be used for searches, there are condition type (contains, equals, less than, startsWith, etc) and the values.

So basically, we need to enter the three parts of the search in the search box

  1. What field?
  2. How to search?
  3. Repeat for each field

What field

Start with the target field (table column). In advanced search, when you select the search box, you'll be prompted for a field name. As you type, the UI autocompletes the field. Finish typing the field name and hit enter or select it with the mouse.


 

How to search

Once we have selected an available field, we're free to type a value for that field. If we type a value, the UI builds a 'contains' condition if appropriate (like for a text value) or an equals condition otherwise (for enumerated types or numeric values for example).

But we want to do more than simple contains. We want to take control of the condition.

NOTE: So the big hidden feature here is that we can type any condition operator (shown in the Data types table above) before the value. Separate the condition operator (for example = or <= or startsWith) from the value with a space.

So in this example, I type "Last", then auto complete the field selection to "Last Connect" and then type in ">= -1w" and hit enter.  We can see after hitting enter, that the UI displays the advanced condition. The results show devices that have connected within the last week.


 



Repeat for each field

 

We can now continue this process, adding additional fields to our search. You can click on the "AND" and "OR" widgets in the UI to toggle them between the logical AND and OR operators.

Differences to v1 API

Unlike the v1 API, the UI may not support all operations.

For example:

  • Conditions for geoposition as described in the data type table above are not yet supported
  • Complex grouping with parenthesis is not yet supported

Conclusion

Let's conclude with a more advanced trouble shooting user scenario.
 

Troubleshooting Example

By using the techniques we covered here, we can build a complex use case like the following:

  • I'm currently diagnosing connectivity problems
  • I want to find devices that are having trouble connecting or are currently in maintenance mode.
  • By "trouble connecting" I mean devices that have never connected or haven't connected recently
  • By "recently" I mean within 2 days

NOTE: Don't forget to click on the "AND" widgets in the search box to turn the AND conditions to OR conditions.



Happy Searching!

 

 

Last updated: Aug 05, 2021

Filed Under

Digi Remote Manager

Recently Viewed

No recently viewed articles