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 endpoint, /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. You can set ‘caching’ to greater than 0 to define the TTL of the cache in minutes. If you set ‘caching’ to 0, it uses the default TTL
direction: set to true to sort the results in ascending order, or false to sort by descending order.
SortBy: an optional, comma-separated list of column names to sort by.
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": "[email protected]"
        }
      },
      {
        "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": "[email protected]"
        }
      },
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "d9fed598-7f16-4305-aaa8-97a9f5c12c00",
            "IsForeignKey": false
          }
        ],
        "Row": {
          "ID": "d9fed598-7f16-4305-aaa8-97a9f5c12c00",
          "Username": "[email protected]"
        }
      }
    ],
    "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 the Admin Portal --see "Creating a new report".
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" for more information about events.

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": "[email protected]",
          "Username": "[email protected]",
          "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": "[email protected]",
          "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.

You can also execute stored procedures via a Redrock query as described here.

Scope

When authenticating using OAuth, you can specify a scope indicating which API's to grant access to. To grant access to the query functionality, set a scope to redrock/query.

Use Queries