.. _nsgql: NetSpyGlass Server Query Language ********************************* .. highlight:: sql :linenothreshold: 2 .. toctree:: :maxdepth: 1 NetSpyGlass query language is loosely based on SQL syntax and can be used to select monitoring variables, devices and components. The language is a "work in progress" at this time. Model ===== NsgQL can be used to build queries that access monitoring data, devices and components in NetSpyGlass. To make SQL-like query language possible, NetSpyGlass objects and data is mapped to SQL "tables" and "columns" as follows: - monitoring variable names, words "tags", "devices" and "maps" can be used in place of a "table" after `SELECT`. For example: .. code-block:: sql SELECT column FROM ifInRate SELECT column FROM ifOperStatus SELECT column FROM cpuUtil SELECT column FROM tags SELECT column FROM devices SELECT name FROM maps - any variable name, any tag facet name, as well as words "device", "component", "interface", "address", "BoxDescr", "time", "metric", "tag", "tagFacet", "ViewId" can be used in place of a "column" and in the matching expression after `WHERE` .. code-block:: sql SELECT device,component,time,metric FROM ifInRate WHERE device=dfw3-dr01-re0 AND ifDescription=MET Columns can have multiple values, for example `VlanId` and `VlanName` if the device has interfaces in multiple vlans. Other columns with multiple values are `Role`, `ViewId`, `tag`. NsgQL query that mentions these columns in the `SELECT` part returns multiple rows with one value per row. The query effectively works similar to SQL "JOIN" operator, as if columns `VlanId`, `VlanName` and others with multiple values were separate tables. Depending on the table mentioned in the `FROM` part, NsgQL chooses common index to build the "JOIN" on automatically. Here is an example of a query that selects specific interface of a device and includes column `ifRole` that has multiple values for each matching object. Note how the query returns multiple rows with one value of `ifRole` in each row: .. code-block:: sql SELECT device,component,ifRole FROM ifInRate WHERE device=dfw3-dr01-re0 AND component=xe-0/0/0 +--------------------+----------------------+--------------------------------+ | component | device | ifRole | +--------------------+----------------------+--------------------------------+ | component=xe-0/0/0 | device=dfw3-dr01-re0 | ifRole=Alertable | | component=xe-0/0/0 | device=dfw3-dr01-re0 | ifRole=BroadcastTypeInterface | | component=xe-0/0/0 | device=dfw3-dr01-re0 | ifRole=ParentOfAggregationPort | | component=xe-0/0/0 | device=dfw3-dr01-re0 | ifRole=PhysicalPort | +--------------------+----------------------+--------------------------------+ This query behaves as if tag facets and monitoring variables were stored in SQL tables linked together by foreign keys pointing to an ID that is a combination of `device` and `component`. You can use any tag facet or even different variable name after `SELECT` and the query will return data as if it did a `JOIN` between these tables. The same query returns just one row if its matching part includes value of `ifRole`: .. code-block:: sql SELECT device,component,ifRole FROM ifInRate WHERE device=dfw3-dr01-re0 AND component=xe-0/0/0 AND ifRole=PhysicalPort +--------------------+----------------------+--------------------------------+ | component | device | ifRole | +--------------------+----------------------+--------------------------------+ | component=xe-0/0/0 | device=dfw3-dr01-re0 | ifRole=PhysicalPort | +--------------------+----------------------+--------------------------------+ SELECT ====== General syntax of the SELECT query is as follows: .. code-block:: sql SELECT column FROM variableName WHERE matchingClause ORDER BY column [DESC] TBD Schema ====== `SELECT` accepts the following items in place of "table": - "tags" - "devices" - "interfaces" - "maps" - "alerts" - any existing monitoring variable name Table "tags" ------------ ================ ====================== column value ================ ====================== facet names of tag facets. ================ ====================== Table "maps" ------------ this table holds information and map views defined in the system. Queries against this table return information only about views accessible by the currently logged in user. These views are those created by Python scripts, those owned by the user and shared views owned by others. ================ =============================================== column value ================ =============================================== id map view ids name names of map views owner The name of the user who created this map updatedAt When this map view has been updated last time (timestamp in milliseconds) shared (boolean) is this view shared type View type: UNKNOWN, SYSTEM, SCRIPTED, INTERACTIVE ================ =============================================== Table "devices" --------------- this table holds information about network devices and device clusters ================ ======================================================================================== column value ================ ======================================================================================== id device or cluster Id name query returns device name device alias for "name" address device address BoxDescr device description tag device tags in the form `Facet.Word` tagFacet tag facets tags of the matching devices belong to ViewId view Ids this device is a member of (appears in maps) uptime device uptime in seconds at the time of the last polling cycle discoveryTime the time of the last successful discovery of this device (timestamp in milliseconds) VlanId vlan id interfaces of this device are members of; tagged interfaces are members of many vlans VlanName vlan name Vendor device vendor Model device model SerialNumber device serial number SoftwareRev software revision this device runs Role device role tag facet name words in the corresponding tag facet ================ ======================================================================================== Table "interfaces" ------------------ this table holds information about inetrfaces and other components of network devices ================ ======================================================================================== column value ================ ======================================================================================== id device or cluster Id name interface name component interface name interface interface name device the name of the device that owns the interface description interface description address interface address tag interface tags in the form `Facet.Word` tagFacet facets of the tags that belong to the interface ViewId view Ids the device that owns the interface is a member of (appears in maps) boxDescr device description uptime device uptime VlanId vlan id this interface is a member of; tagged interfaces are members of many vlans VlanName vlan name Vendor device vendor Model device model SerialNumber device serial number SoftwareRev software revision this device runs Role device role ifRole interface role tag facet name words in the corresponding tag facet ================ ======================================================================================== Table "alerts" -------------- ======================== ================================================================================ column value ======================== ================================================================================ key Unique key name Name device Device name deviceId Device id component Component name (also interface name) compIndex Component index (also interface ifIndex) description Alert description tag Alert tags variable Alert variable inputVariable Input variable that was used as an input for the alert condition activeSince The time when this alert became active (timestamp in milliseconds) active (boolean) Is this alert active silenced (boolean) Is this alert silenced silenceId Id of matching silence timeOfLastNotification The time when we have sent last notification for this alert (timestamp in milliseconds) updatedAt The time of the last modification of this alert (timestamp in milliseconds) ======================== ================================================================================ Table named after monitoring variables name ------------------------------------------- ================ ======================================================================================== column value ================ ======================================================================================== triplet a handle, or "triplet", that uniquely identifies the variable. See :ref:`triplet` variable variable name (the same as the name of the table) time observation time stamp metric observation value that corresponds to the time stamp above device device name component interface or component name description component description address device address BoxDescr device description ViewId ids of the views device belongs to tag All tags of the matching monitoring variable (note that these tags come from both the device and the component, e.g. for monitoring variables that track metrics related to a network interface of a device, this is going to be a union of tags that belong to the device and interface) tag facet name words in the corresponding tag facet ================ ======================================================================================== .. note:: All column names are case-sensitive, including tag facets. LIMIT and OFFSET ================ Parameters LIMIT and OFFSET are supported: .. code-block:: sql SELECT column FROM variableName WHERE matchingClause LIMIT N OFFSET M or .. code-block:: sql SELECT column FROM variableName WHERE matchingClause LIMIT N,M You can combine LIMIT with ORDER BY: .. code-block:: sql SELECT column FROM variableName WHERE matchingClause ORDER BY column LIMIT N SELECT column FROM variableName WHERE matchingClause ORDER BY column DESC LIMIT N OFFSET M Column Aliases ============== Columns referenced in SELECT can have aliases. Column aliases have limited use and are mostly intended to label columns in the output in a meaningful way. Column aliases are not allowed in WHERE and GROUP BY clauses because these are processed before any transformation or aggregation functions are applied to the input data. Column aliases can be used in ORDER BY clause though. .. code-block:: sql SELECT column as alias FROM variableName WHERE column=Word ORDER BY alias LIMIT N .. code-block:: sql SELECT triplet,device,component,tslast(metric) as last_metric, tsmin(metric) as min_metric, tsmax(metric) as max_metric FROM cpuUtil WHERE time BETWEEN 'now-1h' AND now GROUP BY time(1h) ORDER BY last_metric WHERE ===== This part of the query describes matching parameters used to select monitoring variable, device or a component. The lanugage supports the following operators: Logical: AND, OR, NOT Comparison: "=", "!=", "REGEXP", "NOT REGEXP", "==", "<>", "IN", "NOT IN", "ISNULL", "NOTNULL", "NOT NULL", "<", "<=", ">", ">=", "<<", ">>", "BETWEEN" Match `Location=SJC` returns true if the variable or a device has tag `Location.SJC`. Match `Location!=SJC` returns true if variable does not have tag `Location.SJC`. Match `Location IN (SJC, IAD)` returns true if variable has tag `Location.SJC` or tag `Location.IAD`. Names of other monitoring variables can not be used in WHERE at this time. The following query returns an error: .. code-block:: sql SELECT time,metric FROM ifInRate WHERE ifOperStatus=1 Use operator `REGEXP` to match device name, interface name or tag using regular expression. Examples of queries: .. code-block:: sql SELECT value FROM ifOperStatus WHERE device='sjc1-0101-core1' AND component='ae0' SELECT value FROM ifOperStatus WHERE device IN ('sjc1-0101-core1', 'sjc1-0101-core2') AND component='ae0' SELECT value FROM ifOperStatus WHERE Vendor=Juniper AND Role=router SELECT value FROM ifOperStatus WHERE Role=router and Location=SJC SELECT value FROM ifOperStatus WHERE Explicit==core AND Model REGEXP '^65.*$' SELECT value FROM ifOperStatus WHERE Explicit==core AND BGP4PeerAddress='169.254.1.1' .. note:: Quoting of string literals in NsgQL queries is optional, except for when they include white space or a dot. String literals that have white space or IP addresses must be enclosed in single or double quotes when they are used as right hand side of comparison operators '=', '!=' and others. Operators `ISNULL`, `NOTNULL` and their variants with whitespace (`IS NULL` and `NOT NULL`) can be used to check if variable or a device have any tags in particular facet. Expression `BGP4PeerAddress NOTNULL` evaluates to `true` when variable has at least one tag in facet `BGP4PeerAddress`: .. code-block:: sql SELECT value FROM ifOperStatus WHERE device='sjc1-0101-core1' AND BGP4PeerAddress NOTNULL Comparison operators "<", "<=", ">", ">=" work according to the column type. You can also compare values in the column `metric` to ``NaN``. Operators "<<" and ">>" are used to compare IP addresses and subnets: - "<<" determines if the address on the left belongs to subnet on the right. The subnet should be entered in CIDR notation and enclosed in quotes. - ">>" determines if subnet on the left conains address on the right. Attempt to compare value that is not an IP address using these operators causes an error. Examples: .. code-block:: sql SELECT name FROM devices WHERE address << "192.168.1.0/24" .. note:: Comparison operators "=", "!=", "REGEXP", "NOT REGEXP", "==", "<>", "IN", "NOT IN", "ISNULL", "NOTNULL", "NOT NULL", "<", "<=", ">", ">=", "<<", ">>" expect tag facet name on the left and the value to compare to on the right. These operators will silently fail if you try to reverse arguments, e.g. `Juniper=Vendor` or `192.168.1.1=address` Time Match in WHERE =================== You can match timestamp of observations inside of the WHERE clause using operators "=", "<", "<=", ">", ">=" and "BETWEEN" Operator "BETWEEN" is logically equivalent to a pair of comparisons. Expression ``time BETWEEN y AND z`` is equivalent to ``time>=y AND time<=z``. .. note:: Operator `BETWEEN` can be used only with column `time`. Query parser performs basic validation of the time match to make sure beginning of the interval is less or equal to the end. It throws exception if the beginning is greater than the end. Here is a brief table of the time match expressions: ============================= ====== ============== ======== Expression From Until Valid? ============================= ====== ============== ======== time BETWEEN 1000 AND 2000 1000 2000 True time BETWEEN 2000 AND 1000 --- --- False time > 1000 AND time < 2000 1001 1999 True time >= 1000 AND time <= 2000 1000 2000 True time >= 2000 AND time <= 1000 --- --- False time >= 1000 OR time <= 2000 --- --- False time = 1000 1000 1000 True time >= 1000 1000 current time True time < 2000 0 2000 True ============================= ====== ============== ======== The precedence of the BETWEEN operator is the same as the precedence as operators == and != and groups left to right. Parser recognizes the following values for the beginning and end of the time interval: ===================== =============================== =============================================================== Example Value Decription Example ===================== =============================== =============================================================== 1494030086509 time stamp in ms time >= 1494030086509 AND time <= 1494033686509 now current time time >= 1494030086509 AND time <= 'now' now-60s 60 sec ago time BETWEEN 'now-60s' AND 'now' now-1m 1 min ago time BETWEEN 'now-1m' AND 'now' now-1h 1 hour ago time BETWEEN 'now-1h' AND 'now' now-1d 1 day ago time BETWEEN 'now-1d' AND 'now' now-1w 1 week ago time BETWEEN 'now-1w' AND 'now' now-1M 1 month ago time BETWEEN 'now-1M' AND 'now' now-1y 1 year ago time BETWEEN 'now-1y' AND 'now' 2016-01-01T00:00:00Z date and time in ISO8601 format time BETWEEN '2016-01-01T00:00:00Z' AND '2016-01-31T23:59:59Z' ===================== =============================== =============================================================== Numbers of seconds, minutes, hours, days, weeks, months and years in `now-NNd` and similar formats can be fractional as in `now-0.1h` Besides ISO8601, the following date and time formats are supported: ========================== ========================= Format Example ========================== ========================= "dd MMM yyyy HH:mm:ss ZZ" 13 Oct 2016 00:05:48 -07 "yyyy-MM-dd HH:mm:ss ZZ" 2016-10-13 00:05:48 -07 "dd MMM yyyy HH:mm:ss z" 13 Oct 2016 00:05:48 PST "yyyy-MM-dd HH:mm:ss z" 2016-10-13 00:05:48 PST "dd MMM yyyy HH:mm:ss" 13 Oct 2016 00:05:48 "yyyy-MM-dd HH:mm:ss" 2016-10-13 00:05:48 ========================== ========================= Time zone is recognized as both offset ("-00:07", "+00:02") or abbreviation ("Z", "PST"). The server assumes UTC when format that does not specify timezone is used. Values for the beginning and the end of the interval can use different time formats. Values built using string patterns such as `now-1m` or date/time in ISO format must be quoted. GROUP BY ======== `GROUP BY` clause follows `WHERE` in the typical `SELECT` query. `GROUP BY` comes before `ORDER` and `LIMIT` parameters. `GROUP BY` should be followed by a column name or a function, such as `time()`. When followed by the column name, this operator groups results that have the same value of the specified column together and uses groups of the values to calculate aggregate values. In the following example, variables that match `WHERE` clause are grouped by the tag facet `TagFacet`. Groups of values are then passed to the aggregation function `AVG` to calculate their mean value. The query can use `GROUP BY column` but no aggregation function. In this case, `GROUP BY` only changes order of rows in the generated output. .. code-block:: sql SELECT avg(column) FROM variableName WHERE matchingClause GROUP BY TagFacet Aggregation by Time =================== `GROUP BY` can be used to group observations by time. When used this way, the `GROUP BY` clause is followed by the expression `time(TIME_SPEC)` where `TIME_SPEC` is a number, followed by a suffix that describes one of the recognized time intervals: ================ ====================== suffix value ================ ====================== 's' seconds 'm' minutes 'h' hours 'd' days 'w' weeks ================ ====================== The query that uses `GROUP BY time()` operates only on groups of observations in the time series of matching variables. The number of the observations in each group corresponds to the time interval used inside of the `time()` function. `GROUP BY` clause used this way must be accompanied by an aggregation function that wraps column `metric` after `SELECT`. The following time aggregation functions are available: ========================== ================================================================= Time aggregation function Operation ========================== ================================================================= tsmin calculates minimum value across observations in the time block tsmax calculates maximum value across observations in the time block tslast simply returns the value of the last observation ========================== ================================================================= Example: .. code-block:: sql SELECT avg(metric) FROM variableName WHERE matchingClause GROUP BY time(1h) This query groups observations that span most recent 1 hour in the time series and calculates the average of the observation values inside of each interval. .. code-block:: sql SELECT tslast(metric) FROM variableName WHERE matchingClause This query returns the value of the latest observation in `variableName`. this example also illustrates that function `tslast()` can be used even ehen `GROUP BY` is not specified. .. note:: `GROUP BY` used this way does not apply aggregation function to _every_ block of observations in the time series, instead, it operates only on the _last_ block. Queries that use time aggregation may need to retrieve data from the TSDB if the time interval inside of `GROUP BY time()` is long and requires data that the server does not have in memory. This can be expensive, especially if the query does not have very specific matching clause and fetches data for thousands of monitoring variables. The query becomes even more expensive if it also requires sorting of the returned rows by the value of the aggregated column, for example .. code-block:: sql SELECT tsmax(metric) as max_m FROM ifInRate WHERE matchingClause ORDER BY max_m Often, a query like this needs to analyse only the data the server has in memory buffer. For this, NsgQL provides a "shortcut" that makes the query much faster. If a query uses time aggregation function but does not specify both `GROUP BY time()` clause and time match (`time BETWEEN ...`), then it is assumed that the aggregation is expected to look only at the data in the memory. Some internal optimizations allow this query to run much faster. If the query also selects column `time`, returned time is reset to zero. Aggregation =========== NsgQL supports the following aggregation functions: ================ ====================== Function Operation ================ ====================== min calculates minimum value across observations in the time series of grouped variables max the same as above, but calculates maximum value avg the same but calculates arithmetic mean value median calculates median value sum the same but calculates a sum count the same but just counts grouped variables ================ ====================== The query in the next example groups by `ifBGP4Peer` and computes a average value of `ifInRate` for each time stamp in each group: .. code-block:: sql SELECT ifBGP4Peer,time,avg(metric) FROM ifInRate GROUP BY ifBGP4Peer Aggregation functions `min`, `max`, `avg`, `count` can be applied only to a single column. The following query is invalid and will return an error: .. code-block:: sql SELECT avg(ifBGP4Peer,time,metric) FROM ifInRate GROUP BY ifBGP4Peer Function `count` can be used with a wild-card column name `*`. When used like this, it just returns the number of matching rows in the table: .. code-block:: sql SELECT count(*) FROM ifInRate GROUP BY ifBGP4Peer Transformation Functions ======================== NsgQL supports the following transformation functions: ================ ====================== Function Operation ================ ====================== color can be used with column `metric` and returns the color that is determined by comparing the value with thresholds defined in the server configuration. This is the same color that appears in NetSpyGlass maps and Graphing Workbench color_level the number of the threshold matched by the value. to_long converts floating point value to the long integer. ifnan this function takes two argumens: name of the column and a number. If the last value of the column is `NaN`, this function substitutes it the number passed as second argument ifnull this function takes two argumens: name of the column and a number. If the last value of the column is `NULL`, this function substitutes it the number passed as second argument ================ ====================== Functions can be combined. The following two statements should return the same result: .. code-block:: sql SELECT time,tslast(ifnan(metric, 0.0)) FROM ifInRate SELECT time,ifnan(tslast(metric), 0.0) FROM ifInRate Command SHOW ============ This command can be used to get a list of currently available tables. There is only one valid argument for this command: ``tables``. .. code-block:: sql SHOW tables Command DESCRIBE ================ This command accepts one argument that is the name of a table and returns this table's schema. Example:: > DESCRIBE devices -----------------+------------+-------------------------------------------------- columnName | columnType | description -----------------+------------+-------------------------------------------------- BGP4LocalAS | STRING | Tag Facet BGP4Peer | STRING | Tag Facet BGP4PeerAddress | STRING | Tag Facet BGP4Role | STRING | Tag Facet BGPTag | STRING | Tag Facet Explicit | STRING | Tag Facet FromZone | STRING | Tag Facet KentikDeviceId | STRING | Tag Facet KentikDeviceName | STRING | Tag Facet Model | STRING | Tag Facet NsgRegion | STRING | Tag Facet NsgRole | STRING | Tag Facet OSPFArea | STRING | Tag Facet PolicyName | STRING | Tag Facet Protocol | STRING | Tag Facet Role | STRING | Tag Facet SerialNumber | STRING | Tag Facet SoftwareRev | STRING | Tag Facet ToZone | STRING | Tag Facet Vendor | STRING | Tag Facet ViewId | INTEGER | View Id this device is a member of VlanId | INTEGER | Tag Facet VlanName | STRING | Tag Facet address | IPADDRESS | Management address boxDescr | STRING | Box description device | STRING | Device name deviceId | INTEGER | Device Id discoveryTime | INTEGER | Last time this device was successfully discovered id | INTEGER | Device Id name | STRING | Device name tag | STRING | Device tags tagFacet | STRING | Tag facets uptime | INTEGER | Device uptime -----------------+------------+-------------------------------------------------- .. note:: If provided table name is invalid or such table does not exist, this command returns empty response rather than an error. Using NsgQL =========== NetSpyGlass v2.1 will standardize its APIs around NsgQL. API calls used to build maps, graphs and Graphign Workbench contents will use NsgQL. As part of this transition, we are using NsgQL to implement some new features, such as ability to apply filters to maps and to build new maps and map views in the UI. Command Line Query Tool ----------------------- NetSpyGlass v2.1 comes with a command line script `nsgql.py` that can be used to run NsgQL queries against NetSpyGlass server. The script returns data in the tabular form similar to popular command line SQL query tools. Here is the description of this script's command line: .. program-output:: /bin/pwd .. program-output:: ../../../../bin/nsgql.py -h Using NsgQL queries in Python scripts ------------------------------------- Module `nw2functions` now includes function :func:`nw2functions.query()` that takes NsgQL expression as an argument and returns a generator that yields instances of selected monitoring variables. The query passed to this function as an argument must begin with `FROM` and can optionally have `WHERE`. This query is not allowed to have `SELECT` clasue because it is assumed that the function always returns "whole" monitoring variable instance objects (:class:`net.happygears.nw2.py.MonitoringVariable`). Function `query()` can be used as a substitute for the function :func:`nw2functions.filter_by_tags`. Function `query()` returns the same generator as functions `input_var()` and `filter_by_tags()`, this makes the transition of old scripts easier. Here is an example:: # Old implementation using filter_by_tags: # if_out_rate = filter_by_tags(import_var('ifOutRate'), ['ifBGP4Peer.AS174', '!VariableTags.Aggregate']) # # New implementation using query(): if_out_tate = query('FROM ifOutRate WHERE ifBGP4Peer=AS174 AND VariableTags!=Aggregate') # aggr = new_var('Cogent', 'peering') aggregate(aggr, skip_nans(if_out_rate)) aggr.addTag('VariableTags.Aggregate') export_var('ifOutRate', aggr) You can use parethesis, AND/OR, IN/NOT IN and other operators supported by NsgQL. .. _nsgql_caching: Caching ======= NetSpyGlass server uses intenal cache to speed up NsgQL queries. There are two caches: the short term cache and long term cache. Long term cache is used to store results of NsgQL queries that do not involve metrics, for example those that retrieve lists of words in given tag facet. Data of this kind does not change very often and can be kept in cache for a relatively long time. This cache is configured using parameters defined in the configuration section `cache.dataQueryLongTermCache` that looks like this:: cache { dataQueryLongTermCache { # This cache is used to store computed NsgQL queries capacity = 1000 expireAfterAccess = 30 # Query results expire if not accessed this long, in munites refreshAfterWrite = 10 # Query results are refreshed if they are stored in the cache this long, in minutes } } Changes to the values here require server restart. Parameters: - `capacity`: maximum cache capacity - `expireAfterAccess` Items expire if not accessed this long, in munites - `refreshAfterWrite` Items are refreshed if they are stored in the cache this long, in minutes An item is expired and evicted from the cache if it has been stored there for a time longer than `expireAfterAccess` after last attempt to read it. The value of this parameter is time in minutes. An item is going to be returned to the caller but also refreshed in the background if it is accessed after `refreshAfterWrite` minutes after it was saved to the cache. This means the first attempt to access the item after `refreshAfterWrite` minutes still gets the old result, but at the same time, the server initiates background process that will rebuild the item, so that the next attempt to access it will get refreshed copy. NsgQL queries that involve metric data that potentially changes every polling cycle use different cache that is invalidated at the end of each polling cycle. This short term cache does not have any configuration parameters that can be set by the user. Examples ======== Get list of all tag facets, sorted alphabetically: .. code-block:: sql SELECT facet FROM tags ORDER BY facet Get list of device names with their descriptions and software revision data for specified view: .. code-block:: sql SELECT device,BoxDescr,SoftwareRev FROM devices WHERE ViewId=24 drop `WHERE ViewID=24` clause to get data for all devices in NetSpyGlass Get list of devices with their ids, name, address and role: .. code-block:: sql SELECT id,name,address FROM devices Get the same information but skip device objects created to represent clusters and other simulated devices: .. code-block:: sql SELECT id,name,address FROM devices WHERE Role!=Cluster AND Role!=SimulatedNode AND Role!=SimulatedBridge Get monitoring data for variable `ifInRate` for particular interface: .. code-block:: sql SELECT time,metric FROM ifInRate WHERE device=sjc2-b12-leaf1 AND interface=ae0 SELECT time,metric FROM ifInRate WHERE device=sjc2-b12-leaf1 AND interface='xe-0/0/48:0' note that interface name must be quoted if it includes some special characters such as ':' Get latest value of cpu utilization for all devices: .. code-block:: sql SELECT tslast(metric) FROM cpuUtil The following query is used by the Graphing Workbench to populate its data table. This query gets information about the variable, device, component, minimum, maximum and last value from the time series. Functions `tsmin()`, `tsmax()` and `tslast()` operate on the last 24 hours of data in the time series. This query also demonstrates how a simple filter that selects two interfaces of a device can be applied: .. code-block:: sql SELECT triplet,device,component,tslast(metric),tsmin(metric),tsmax(metric) FROM ifInRate WHERE device=dfw3-dr01-re0 AND (component=ae0 OR component=ae1) AND time BETWEEN "now-24h" AND now The following query is similar to the previous except functions `tsmin()`, `tsmax()` and `tslast()` operate on the data in stored in memory because this query does not specify time interval in the `WHERE` clause: .. code-block:: sql SELECT triplet,device,component,tslast(metric),tsmin(metric),tsmax(metric) FROM ifInRate WHERE device=dfw3-dr01-re0 AND (component=ae0 OR component=ae1)