DOQL – Device42 Object Query Language

Querying DOQL using the Device42 API

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

Should you have any questions, consult the “Obtaining DOQL Support” section on this page.

DOQL query API endpoint

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

If you’d like your DOQL query results in JSON, set the output_type query parameter to JSON. e.g.
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 utilized with DOQL via the API:

  • query = the DOQL query you want to run, a SELECT command whose results are to be returned as CSV data (required)

  • delimiter = Specifies the character that separates columns within each row (line) of the file. The default a comma. This must be a single one-byte character. (optional)

  • header = if ‘yes’ 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. (optional)

  • quote = Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. (optional)

  • null_string = Specifies the string that represents a null value. The default is an unquoted empty string. You might prefer an empty string even in text format for cases where you don’t want to distinguish nulls from empty strings. (optional)

  • quote_escape = 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). This must be a single one-byte character. (optional)

  • output_type = Get JSON results by setting this parameter to ‘json’

The Data Dictionary, ERD, & Viewer Schema

The data dictionary is available via the API (in JSON).
API Endpoint for the data dictionary [in JSON] = /services/data/v1.0/dd/

See the Device42 DB viewer schema page for information on the DB viewer schena & Entity Relatinoship Diagram, both of which are very handy when authoring queries, providing valuable insight into the Device42 database schema.


Obtaining DOQL Support

DOQL support is NOT included. We will NOT write queries for you! That said…

  • If you need help writing a query, Device42 can only offer paid professional services.

  • If you happen to come across a BUG, please submit it. We will fix it as part of our normal bug-fix process. There is, of course, no charge for bug fixes.

About Device42 DOQL

Note that wherever possible, DOQL Syntax are equivalent to PostgreSQL syntax.

  • This document does its best to highlight those areas in which the syntax DIFFER.
  • POST calls are recommended rather than GET calls, as url length does not have limitations 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 (i.e. %20 for space, %3B for “%”).
  • If something isn’t working that you think should, see DOQL Support notes below.

Samples

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