Device42 Object Query Langage (DOQL)

DOQL – Device42 Object Query Language

API Endpoints

API endpoint to run queries = /services/data/v1.0/query/

API endpoint to get the data dictionary in JSON = /services/data/v1.0/dd/

If you’d like your results returned from DOQL service 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/'

Example:

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

Example Curl Command

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/'

API Parameters:

  • 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’

  • Get the data dictionary in JSON at this API endpoint = /services/data/v1.0/dd/


    Samples (below):

        [
        {
        "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"
        },
    

    Notes

    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.

    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.