15. NetSpyGlass Server Query Language

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.

15.1. 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:
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
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:

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:

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            |
+--------------------+----------------------+--------------------------------+

15.2. SELECT

General syntax of the SELECT query is as follows:

SELECT column FROM variableName WHERE matchingClause ORDER BY column [DESC]

TBD

15.3. Schema

SELECT accepts the following items in place of “table”:

  • “tags”
  • “devices”
  • “interfaces”
  • “maps”
  • “alerts”
  • any existing monitoring variable name

15.3.1. Table “tags”

column value
facet names of tag facets.

15.3.2. 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

15.3.3. 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

15.3.4. 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

15.3.5. 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)

15.3.6. Table named after monitoring variables name

column value
triplet a handle, or “triplet”, that uniquely identifies the variable. See How Monitoring Variables are Stored
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.

15.4. LIMIT and OFFSET

Parameters LIMIT and OFFSET are supported:

SELECT column FROM variableName WHERE matchingClause LIMIT N OFFSET M

or

SELECT column FROM variableName WHERE matchingClause LIMIT N,M

You can combine LIMIT with ORDER BY:

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

15.5. 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.

SELECT column as alias FROM variableName WHERE column=Word ORDER BY alias LIMIT N
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

15.6. 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:

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:

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:

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:

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

15.7. 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.

15.8. 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.

SELECT avg(column) FROM variableName WHERE matchingClause GROUP BY TagFacet

15.9. 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:

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.

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

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.

15.10. 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:

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:

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:

SELECT count(*) FROM ifInRate GROUP BY ifBGP4Peer

15.11. 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:

SELECT time,tslast(ifnan(metric, 0.0)) FROM ifInRate
SELECT time,ifnan(tslast(metric), 0.0) FROM ifInRate

15.12. 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.

SHOW tables

15.13. Command DESCRIBE

This command accepts one argument that is the name of a table and returns this table’s schema. Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
> 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.

15.14. 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.

15.14.1. 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:

/Users/vadim2/src/nw2/backend/src/site/sphinx
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
This script executes NsgQL queries provided on command line or interactively

Usage:

    nsgql.py (--base-url=url) (-n|--network)=netid [(-f|--format)=format] [-h|--help] [(-c|--command)=command] [-a|--access-token=token]

       --base-url:     Base URL for the NetSpyGlass UI backend server. This includes protocol (http/https),
                       server name or address and port number. Examples: http://localhost:9100 , https://nsg-server:9100
       --network:      NetSpyGlass network id (a number, default: 1)
       --format:       how to format query result. This can be one of 'list', 'table', 'time_series', 'json'. 
                       Default is 'table'
       --raw:          print data as returned by the server. Specifically, do not try to print data returned for
                       --format=table as an ascii table
       --command:      execute NsgQL queries provided as argument. Multiple NsgQL queries can be separated by ';'
       --access-token: API access token string
       -h --help:      print this usage summary

    Parameter --base-url is optional. If it is not provided, the script tries to find script "nsgc.sh"
    in the same directory it was started from and runs it to read server url from NetSpyGlass configuration
    file.

15.14.2. Using NsgQL queries in Python scripts

Module nw2functions now includes function 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 (net.happygears.nw2.py.MonitoringVariable).

Function query() can be used as a substitute for the function 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 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.

15.15. 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:

1
2
3
4
5
6
7
8
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.

15.16. Examples

Get list of all tag facets, sorted alphabetically:

SELECT facet FROM tags ORDER BY facet

Get list of device names with their descriptions and software revision data for specified view:

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:

SELECT id,name,address FROM devices

Get the same information but skip device objects created to represent clusters and other simulated devices:

SELECT id,name,address FROM devices WHERE Role!=Cluster AND Role!=SimulatedNode AND Role!=SimulatedBridge

Get monitoring data for variable ifInRate for particular interface:

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:

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:

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:

SELECT triplet,device,component,tslast(metric),tsmin(metric),tsmax(metric)
FROM ifInRate
WHERE device=dfw3-dr01-re0 AND (component=ae0 OR component=ae1)