DOQL - Device42 Object Query Language

DOQL queries are run against the Device42 database via both HTTPS and the RESTful API. You can make requests using curl, or alternatively, by pointing your favorite REST API client at the DOQL query endpoints below. Consulting the DOQL query parameters and example queries that follow can be helpful as well.

tip

Use our InsightsAI chat, under Analytics > InsightsAI on the MA, to create and interpret DOQL query snippets quickly in the context of the Device42 data tables.

DOQL Query API Endpoint Example

Visit the Device42 API documentation for more information on DOQL query endpoints.

DOQL Endpoint: /services/data/v1.0/query/

Example:

https://localhost/services/data/v1.0/query/?query=SELECT name, os_name, os_version FROM view_device_v1 order by device_pk

Curl example:

Click to expand the code block

curl -X POST \
  -d "query=select name from view_appcomp_v1 where lower(name) like 'microsoft sql%' or lower(name) like 'postgresql%' or lower(name) like 'mongodb%' or lower(name) like 'mysql%' or lower(name) like 'oracle database%' or lower(name) like 'sap%' or lower(name) like 'sybase%' or lower(name) like 'mariadb%'" \
  -u $(cat apipw) \
  'https://yourdevice42/services/data/v1.0/query/'

Query Results in JSON Format

If you'd like your DOQL query results in JSON format, set the output_type query parameter to json. For example:

Click to expand the code block

curl -k -s -X POST \
  -d 'output_type=json&query=select * from view_device_v1' \
  -u 'admin:adm!nd42' \
  'http://server/services/data/v1.0/query/'

DOQL API Query Parameters

The following database query parameters can be used with DOQL via the API. The required parameters are query and output_type.

• query: This is the DOQL query you want to run, a SELECT command that returns results as CSV data (required).
• delimiter: This specifies the character that separates columns within each row (line) of the file. The default is a comma (,). This must be a single one-byte character.
• header: If yes, this specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table.
• quote: This specifies the quoting character to be used when a data value is quoted. The default is a double-quote ("). It must be a single one-byte character.
• null_string: A parameter defining how null values are represented in the CSV. The default is an empty string, but you can customize this (for example, to represent nulls with a specific string like 'NULL').
• quote_escape: This specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data). It must be a single one-byte character.
• output_type: Get JSON results by setting this parameter to json.

The Data Dictionary, ERD, and Viewer Schema

The Data Dictionary is available in JSON format via the API at the following endpoint:

/services/data/v1.0/dd/

See the Database Viewer Schema page for information on the DB viewer schema and Entity Relationship Diagram, both of which are very handy when authoring queries, providing valuable insight into the Device42 database schema.

DOQL Queries Against Custom Fields

When constructing DOQL queries against custom fields, keep in mind that custom field names are usually case-sensitive. Custom field names with only lowercase characters and no spaces can be referenced (as the column name in the database) with any casing in queries. Custom field names with uppercase characters or spaces must be referenced using the exact casing in double quotes.

For example, consider the following telco custom field names:

• For Region, the appropriate query would be:

select "Region" from view_telcocircuit_custom_fields_flat_v1

• For region name, the query would need to be:

select "region name" from view_telcocircuit_custom_fields_flat_v1

• For region, since it only has lowercase characters and no spaces, the query is:

select region from view_telcocircuit_custom_fields_flat_v1

note

In DOQL views, custom numeric and date fields are now represented as DATE and BIGINT instead of TEXT, which might affect existing queries. Queries containing TEXT-related builtins, like SUBSTRING, may fail and need to be updated using the updated data type. Queries with basic operators in the WHERE clause, like = and <>, should all continue without issues.

Saved DOQL Queries

With Saved DOQL Queries, you can centrally store, track, and re-use queries written for prior integrations, automation projects, or other efforts. It is easy to add and customize these queries. You can reference the name of a saved query (for example, "Prod_Switches") to call it from any integration. Saved queries are also leveraged by Device42 integrations as 'system-defined' queries.

To add, edit, and view your stored DOQL queries, go to Tools > Integrations > Saved DOQL Queries from the main menu.

Add a New Saved DOQL Query

• Click + Add Saved DOQL Query to add a new query from the DOQL Query View list page.

  

• Enter the query name, the DOQL query itself, and any notes you'd like to add. Use the Test SQL button to check the validity of the query before saving.

  

DOQL Query URL

Each saved DOQL query has an associated Query URL, which can be seen next to each query on the Saved DOQL Queries list page and at the bottom of an individual query's details page.

The query URL links to the DOQL query API endpoint and returns the results of that specific DOQL query in CSV format. For more information on running DOQL queries via the Device42 API, see the DOQL Query API Endpoint Example and DOQL API Query Parameters sections of this page.

Edit or Clone an Existing Saved DOQL Query

• To edit an existing DOQL query, click the name of the query you'd like to edit, and from the View Saved DOQL Query screen, click Edit in the upper-right corner. You can also Clone DOQL Query to create a new query based on the existing one.

  

• You can now make any necessary edits to the DOQL query you were looking at, and can even add notes. Click the Save button when you are done editing your DOQL Query.

  

Device42 DOQL Notes

• Wherever possible, DOQL syntax is equivalent to PostgreSQL syntax, but this document highlights the areas where the two syntaxes differ.
• POST calls are recommended rather than GET calls, as the URL length isn't limited in POST calls.
• If using GET calls, note that URL length is limited, and any special characters in the query need to be URL-encoded (for example, %20 for space and %3B for %).

Sample Database Schema

Below is a sample database schema in JSON format:

Click to expand the code block

[
  {
    "columns": [
      {
        "column": "appcomp_fk",
        "description": "Foreign Key to ID for Application Component",
        "data_type": "integer"
      },
      {
        "column": "Test End User",
        "description": "Related Field for end users - Log for API - for Application Component",
        "data_type": "text"
      }
    ],
    "view": "view_appcomp_custom_fields_flat_v1"
  },
  {
    "columns": [
      {
        "column": "asset_fk",
        "description": "Foreign Key to ID for Asset",
        "data_type": "integer"
      }
    ],
    "view": "view_asset_custom_fields_flat_v1"
  },
  {
    "columns": [
      {
        "column": "asset_fk",
        "description": "Foreign Key to ID for Asset",
        "data_type": "integer"
      },
      {
        "column": "asset_name",
        "description": "Name for Asset",
        "data_type": "character varying"
      },
      {
        "column": "type_id",
        "description": "Identifier Key for Type for Asset Custom Field",
        "data_type": "smallint"
      },
      {
        "column": "type",
        "description": "Type for Asset Custom Field",
        "data_type": "text"
      },
      {
        "column": "related_model_name",
        "description": "Name of Related Field for Asset Custom Field",
        "data_type": "character varying"
      },
      {
        "column": "filterable",
        "description": "Filterable for Asset Custom Field",
        "data_type": "boolean"
      },
      {
        "column": "mandatory",
        "description": "Mandatory for Asset Custom Field",
        "data_type": "boolean"
      },
      {
        "column": "log_for_api",
        "description": "Log for API for Asset Custom Field",
        "data_type": "boolean"
      },
      {
        "column": "key",
        "description": "Field for Asset Custom Field",
        "data_type": "character varying"
      },
      {
        "column": "value",
        "description": "VLAN Custom Field for VLAN",
        "data_type": "text"
      }
    ],
    "view": "view_asset_custom_fields_v1"
  }
]

Obtaining DOQL Support

Generate DOQL queries by entering natural language descriptions in the InsightsAI chat, under Analytics > InsightsAI on the MA. See the InsightsAI page for more details.

You can also refer to the Entity Relationship Diagram and Data Dictionary table to get the names of Device42 database tables and columns (fields) and lookup relationships. See the Database Viewer Schema page for more details.

Please note that Device42 does not assist users with writing DOQL queries. However, if you encounter a bug, please report it to our support team at support@device42.com and we will fix it as part of our normal bug-fixing process.