Use Queries

Introduction

Identity Platform stores all the data it maintains (e.g. users, roles, and tenants) in database tables. Many of the API functions access these tables to retrieve or write information. In addition, you can directly access these tables through an SQL query interface. This topic shows you how.

Request

The SQL query interface allows you to read database tables — it does not allow you to modify or create data in these tables.

The API provides a single function, /Redrock/Query to query the database tables. The payload for this function requires a script with the SQL code to execute the query and an optional Args parameter to control the output. For example, here is a simple query on the User Table, that returns the ID and name for each user who has accessed the cloud service.

/Redrock/query

{"Script":"Select ID, Username from User ORDER BY Username COLLATE NOCASE"}

Pagination

Redrock queries support additional arguments to paginate large result sets:

  • PageNumber: the number of pages of results to return.
  • PageSize: the number of entities to return per page.
  • Limit: the maximum number of results to return for the specified page.
  • Caching: can be set to the following values:
    • -1: returns live data but writes to the cache for query results.
    • < -1: don’t read from or write to the cache for query results.
    • 0: use the cache for both read/write with ‘caching in minutes’ as TTL of the results.
  • direction: set to true to sort the results in ascending order; false to sort by descending order.
  • SortBy: an optional, comma-separated list of column names with which to sort.
    Specifying these parameters requires you to invoke the endpoint multiple times and to specify the page and size of results to be returned each time. For example:
/Redrock/query

{
  "Script":"Select ID, Username from User ORDER BY Username COLLATE NOCASE",
  "args":
  {
    "PageNumber":10000,
    "PageSize":10000,
    "Limit":1000,
    "Caching":-1,
    "direction":false
    "SortBy": "Username"
  }
}

Response

As with any REST API call, the output includes success, Result, and standard error messages:

  • success indicates whether the call succeeded (true) or not (false).
  • Result includes Count (number of records that the call returns), Columns (definitions of each column), and Results (rows from the table that the call returns).
  • Standard REST API errors. For a successful call, all errors are set to null. On failure, success is false and Result is null. The Message parameter provides a human-readable description of the error.

The /Redrock/Query User table response:

{
  "success": true,
  "Result": {
    "IsAggregate": false,
    "Count": 44,
    "Columns": [
      {
        "Name": "ID",
        "IsHidden": false,
        "DDName": "ID",
        "Title": "ID",
        "DDTitle": "ID",
        "Description": "Row Identifier (primary key)",
        "Type": 12,
        "Format": null,
        "Width": 0,
        "TableKey": "Primary",
        "ForeignKey": null
      },
      {
        "Name": "Username",
        "IsHidden": false,
        "DDName": "Username",
        "Title": "Username",
        "DDTitle": "Username",
        "Description": "User name.",
        "Type": 12,
        "Format": null,
        "Width": 0,
        "TableKey": "Alternate",
        "ForeignKey": null
      }
    ],
    "FullCount": 44,
    "Results": [
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "e7ed3e73-d115-44f3-9553-4bcc1a4ecc05",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "ID": "e7ed3e73-d115-44f3-9553-4bcc1a4ecc05",
          "Username": "auser1@qa.com"
        }
      },
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "1ee22405-59b8-49a9-b64b-5b13aee592ce",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "ID": "1ee22405-59b8-49a9-b64b-5b13aee592ce",
          "Username": "admin@abc1234"
        }
      },
       
  ...
    
   {
        "Entities": [
          {
            "Type": "User",
            "Key": "0ee88549-7c48-486d-a9f5-63cf9167890a",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "ID": "0ee88549-7c48-486d-a9f5-63cf9167890a",
          "Username": "test2@ddog.com"
        }
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "d9fed598-7f16-4305-aaa8-97a9f5c12c00",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "ID": "d9fed598-7f16-4305-aaa8-97a9f5c12c00",
          "Username": "user1@ddog.com"
        }
      }
    ],
    "ReturnID": ""
  },
  "Message": null, "MessageID": null, "Exception": null, "ErrorID": null, "ErrorCode": null, "InnerExceptions": null
}

Example of unsuccessful call to /Redrock/Query:

{
"success": false,
"Result": null,
"Message": "Query has failed: no such table: Users",
"MessageID": "_I18N_RedrockQuery",
"Exception": "Centrify.Cloud.Query.RedrockQueryException: Query has failed: no such table: Users ---> System.Data.SQLite.SQLiteException: ...",
"ErrorID": "5da234d1-0fd9-43b1-b121-04d5177112f3:e62a3498eff04b9999aea4ec72ddea52",
"ErrorCode": null,
"InnerExceptions": [
{
    "Detail": "System.Data.SQLite.SQLiteException (0x80004005): SQL logic error or missing database\r\nno such table: Users\r\n  ...",
    "Message": "SQL logic error or missing database\r\nno such table: Users",
    "MessageID": "_I18N_System.Data.SQLite.SQLiteException"
}
    ]
}

Additional Information

Keep the following points in mind when making queries:

  • Queries only return data to which the user has access.
  • Queries time out after two minutes.
  • You can try out queries on the Reports page in Cloud Manager. See "Creating a new report" in Cloud Manager Online Help.
  • When querying the Event table, you must include a time boundary by using the DateFunc() SQL function to limit the query results. For example, the following query returns events that occurred in the last 24 hours (one day):
/Redrock/Query
  {
  "Script": "Select WhenOccurred,EventType from Event where WhenOccurred > datefunc('now', '-1')"
  }

See "Filtering events by time with DateFunc" in Cloud Manager Online Help for more information about events.

"Managing reports" in Cloud Manager Online Help contains details about using SQL to query identity platform.

The Args parameter specifies the page formatting for the output returned by the query.

Column definitions

In the result, the call shows the number of rows returned and a definition for each column that the call returns:

Column
Definition

Name

Name of the column.

IsHidden

Whether column is hidden.

DDName

Data dictionary name of the column.

Title

Title of the column.

DDTitle

Data dictionary title of the column.

Description

Brief description of the column.

Type

Numeric indicator of the type of data in the column.

Format

Not used.

Width

Not used.

TableKey

Whether column is a key and if so what kind (primary or foreign).

ForeignKey

Whether column holds a foreign key.

For example, a call to select ID and Username from the User Table returns definitions for the ID and Username columns:

"Result": {
    "IsAggregate": false,
    "Count": 41,
    "Columns": [
     {
        "Name": "ID",
        "IsHidden": false,
        "DDName": "ID",
        "Title": "ID",
        "DDTitle": "ID",
        "Description": "Row Identifier (primary key)",
        "Type": 12,
        "Format": null,
        "Width": 0,
        "TableKey": "Primary",
        "ForeignKey": null
      },
      {
        "Name": "Username",
        "IsHidden": false,
        "DDName": "Username",
        "Title": "Username",
        "DDTitle": "Username",
        "Description": "User name.",
        "Type": 12,
        "Format": null,
        "Width": 0,
        "TableKey": "Alternate",
        "ForeignKey": null
      },
       ...
  }

Column data

Following the column definitions, Results shows the data for each record returned by the query. For example, the query to return all columns in the User table returns data similar to this:

"Results": [
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "e7ed3e73-d115-44f3-9553-4bcc1a4ecc05",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "DisplayName": "QA1",
          "DirectoryServiceUuid": "09B9A9B0-6CE8-465F-AB03-65766D33B05E",
          "LastInvite": "/Date(1438715143250)/",
          "LastLogin": "/Date(1438715156801)/",
          "SourceDsLocalized": "Cloud",
          "StatusEnum": "Active",
          "_MatchFilter": null,
          "Email": "john@acme.com",
          "Username": "QA1@qa.com",
          "Forest": null,
          "SourceDs": "CDS",
          "Status": "Active",
          "ID": "e7ed3e73-d115-44f3-9553-4bcc1a4ecc05",
          "SourceDsType": "CDS"
        }
      },
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "c5101b4a-34b1-4494-af72-dfcf1766e3b1",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "DisplayName": "ABC",
          "DirectoryServiceUuid": "09B9A9B0-6CE8-465F-AB03-65766D33B05E",
          "LastInvite": null,
          "LastLogin": null,
          "SourceDsLocalized": "Cloud",
          "StatusEnum": "Created",
          "_MatchFilter": null,
          "Email": "shin@acme.com",
          "Username": "abc@ldap4m",
          "Forest": null,
          "SourceDs": "CDS",
          "Status": "Not Invited",
          "ID": "c5101b4a-34b1-4494-af72-dfcf1766e3b1",
          "SourceDsType": "CDS"
        }
      },
   ]   ...

As you can see, for each user in the database, the query returns a row that contains data for all columns in the User Table.

See the Data Dictionary for a description of the fields in each table as well as sample queries.

See Also

Use Queries