Use queries

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

SQL request

The SQL query interface enables you to read database tables. It does not let you 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 run the query and an optional Args parameter to control the output.

For example, here is a simple query on the User table, which 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"}

Paginate result sets

Redrock queries support additional arguments to paginate large result sets:

Argument	Description
PageNumber	The specific page number of results to be returned
PageSize	The number of entities to return per page
Limit	The maximum number of results to return for the specified page
Caching	How the results should be cached This argument can be set to the following values: -1: Returns live data, but writes to the cache for query results <Less than -1: Doesn't read from or write to the cache for query results 0: Uses the cache for read and write, with caching in minutes as TTL (time-to-live) of the results
Direction	Whether the results are sorted in ascending or descending order True: The results are sorted in ascending order False: The results are sorted in 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 (the number of records that the call returns), Columns (the definitions of each column), and Results (the 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 an unsuccessful call to /Redrock/Query:

{
"success": false,
"Result": null,
"Message": "Query has failed: no such table: Users",
"MessageID": "_I18N_RedrockQuery",
"Exception": "Idaptive.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 that the user has access to.
Queries time out after two minutes.
The Args parameter specifies the page formatting for the output returned by the query.
You can try out queries on the Reports page in Cloud Manager. See Create 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')"
  }
```

For more information events and reports, see Filter events by time with DateFunc() and Manage reports.

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`	The name of the column
`IsHidden`	Whether column is hidden
`DDName`	The data dictionary name of the column
`Title`	The title of the column
`DDTitle`	The data dictionary title of the column
`Description`	A brief description of the column
`Type`	The numeric indicator of the type of data in the column
`Format`	Not used
`Width`	Not used
`TableKey`	Whether the column is a key and if so, what kind (primary or foreign)
`ForeignKey`	Whether the 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.

For a description of the fields in each table, and sample queries, see Data dictionary.

Specify the scope

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

Try the API in Postman: