SysQueryDef

The SysQueryDef table contains system queries available to the tenant.

Column

Type

Description

ID

String

Query's UUID (primary key).

Name

Localized String

Query's name.

Description

Localized String

Query's description.

Category

String

Category assigned to the query (reports, etc.).

Owner

String

User who owns the query.

Rank

Integer

Query ranking.

Query

String

Query text (sql, stored procedure reference, etc.).

Example Query

The following query lists the queries in the SysQueryDef table by Name:

{Script: "Select Query from SysQueryDef ORDER BY Name COLLATE NOCASE"}

SysQueryDef table query response:

...
"FullCount": 2,
"Results": [
  {
    "Entities": [
      {
        "Type": "SysQueryDef",
         "Key": "Active Devices",
        "IsForeignKey": false
      }
    ],
    "Row": {
      "Query": "select ID, DeviceID, Carrier, State, ModelName, DisplayState, 
          Name, OSVersion, OwnerID, DisplayModelName, InternalDeviceType, Owner, 
          Serial, Capabilities, EnrollmentType, SafeSdkVersion, MobileManagerVersion, 
          AfwCapableDevice, AfwDeviceId, StatusFlags, PhoneNumber, CASE PhoneNumber 
          WHEN 'null' THEN '' else PhoneNumber END as PhoneNumber from Device 
          where State = 2 OR State = 3 ORDER BY Owner COLLATE NOCASE",
      "Name": "Active Devices",
      "ID": "Active Devices",
      "Owner": "Idaptive",
      "Description": "List of Active Devices",
       "_MatchFilter": null,
      "Rank": 20,
      "Category": "Device"
    }
  },
  {
    "Entities": [
      {
        "Type": "SysQueryDef",
        "Key": "Directory Service Users",
        "IsForeignKey": false
      }
    ],
    "Row": {
      "Query": "Select * from User where SourceDsType = 'AdProxy' ORDER BY Username COLLATE NOCASE",
      "Name": "Active Directory Users",
      "ID": "Directory Service Users",
      "Owner": "Idaptive",
      "Description": "List Web Applications",
      "_MatchFilter": null,
      "Rank": 20,
       "Category": "User"
    }
  }
]