Advanced Reports
Get All Queries
This endpoint retrieves a list of query resources.
GET /api/v2/queries/
Query Parameters
The following parameters may be used to filter the query resources in the response.
| Parameter | Description |
|---|---|
| ordering | Sort applications by the specified field. Prefix field name with minus to sort descending. Sortable fields: id, title, desciption, created, modified. |
| search | Filter applications by performing a textual search on title, created_by__first_name, created_by__last_name. |
GET /api/v2/queries/ HTTP/1.1
Accept: application/json
Authorization: Token "YOUR SDE ACCESS TOKEN"
HTTP/1.1 200 OK
Content-Type: application/json
{
"results": [
{
"id": 60,
"title": "Sample Query 1",
"description": "Sample query created by Admin Testerton",
"created": "2022-05-19T10:52:45.186148-04:00",
"modified": "2022-05-26T11:37:36.974065-04:00",
"created_by": 1,
"query": {
"schema": "application",
"limit": 20,
"order": [
],
"filters": [
{
"or": [
{
"member": "Task.accepted",
"values": [
"true"
],
"operator": "equals"
}
]
}
],
"measures": [
"Project.count",
"Project.complianceCount"
],
"dimensions": [
"BusinessUnit.name",
"Application.id"
]
},
"chart": "table",
"chart_meta": {
"columnOrder": [
"Application.id",
"BusinessUnit.name",
"Project.count",
"Project.complianceCount"
]
},
"type": "D"
}
]
}
Get a Specific Query
This endpoint retrieves a specific Query resource, as specified by the query_id parameter.
GET /api/v2/queries/{query_id}/
URL Parameters
| Parameter | Description |
|---|---|
| query_id | The id of the query to retrieve |
Note: All of the expand parameters for the Get all Queries endpoint apply here as well.
GET /api/v2/queries/60/ HTTP/1.1
Accept: application/json
Authorization: Token "YOUR SDE ACCESS TOKEN"
HTTP/1.1 200 OK
Content-Type: application/json
{
"id": 60,
"title": "Sample Query 1",
"description": "Sample query created by Admin Testerton",
"created": "2022-05-19T10:52:45.186148-04:00",
"modified": "2022-05-26T11:37:36.974065-04:00",
"created_by": 1,
"query": {
"schema": "application",
"limit": 20,
"order": [
],
"filters": [
{
"or": [
{
"member": "Task.accepted",
"values": [
"true"
],
"operator": "equals"
}
]
}
],
"measures": [
"Project.count",
"Project.complianceCount"
],
"dimensions": [
"BusinessUnit.name",
"Application.id"
]
},
"chart": "table",
"chart_meta": {
"columnOrder": [
"Application.id",
"BusinessUnit.name",
"Project.count",
"Project.complianceCount"
]
},
"type": "D"
}
Create a New Query
| Fields | Required | Description |
|---|---|---|
| title | Yes | The title of the new query |
| description | No | The description of the new query |
| chart | Yes | The type of visualization for the query. Possible values: table, pie, number, stacked_bar, horizontal_bar, line |
| query | Yes | The query object for the query which must be a valid cube query in the format described in the Cube API documentation, with an additional schema property corresponding to the Reporting Context. Possible schema values: all, activity, application, countermeasure, integration, library, project_survey_answers, training, user, trend_application, trend_task, trend_project. The timeDimensions field is required for Trend Reports and apply to each of the Trend contexts, denoted by the prefix of trend*. To learn more about Trend Reporting please see the Trend Reports tab in the Reporting User Documentation |
| chart_meta | No | The column order of the new query |
| type | No | The type of Report that the Query is. Standard Advanced Reports are the Default type, denoted as "D". Trend Reports are denoted as "T". To learn more about Trend Reporting please see the Trend Reports tab in the Reporting User Documentation |
POST /api/v2/queries/ HTTP/1.1
Accept: application/json
Authorization: Token "YOUR SDE ACCESS TOKEN"
{
"title": "Sample Query",
"description": "",
"chart": "table",
"query": {
"schema": "application",
"dimensions": [
"Application.name"
],
"measures": [
"Project.count"
],
"order": [
[
"Application.name",
"desc"
]
],
"filters": [
{
"or": [
{
"member": "Project.count",
"operator": "lt",
"values": [
"5"
]
}
]
}
],
"limit": 20
},
"chart_meta": {
"columnOrder": []
},
"type": "D"
}
HTTP/1.1 201 CREATED
Content-Type: application/json
{
"id":71,
"title":"Sample Query",
"description":"",
"created":"2022-06-07T11:56:35.333559-04:00",
"modified":"2022-06-07T11:56:35.333590-04:00",
"created_by":1,
"query":{
"schema": "application",
"dimensions":[
"Application.name"
],
"measures":[
"Project.count"
],
"order":[
[
"Application.name",
"desc"
]
],
"filters":[
{
"or":[
{
"member":"Project.count",
"operator":"lt",
"values":[
"5"
]
}
]
}
],
"limit":20
},
"chart":"table",
"chart_meta":{
"columnOrder":[
]
},
"type": "D"
}
Update a Query
Update a single query by specifying new query fields. The query to update is identified by the query_id.
PATCH /api/v2/queries/{query_id}/
URL Parameters
| Parameter | Description |
|---|---|
| query_id | The id of the query to edit must be submitted in the request |
Payload
| Fields | Required | Description |
|---|---|---|
| title | No | The title of the new query |
| description | No | The description of the new query |
| query | Yes | The query object for the query which must be a valid cube query in the format described in the Cube API documentation, with an additional schema property corresponding to the Reporting Context. Possible schema values: all, activity, application, countermeasure, integration, library, project_survey_answers, training, user, trend_application, trend_task, trend_project. The timeDimensions fields are required for Trend Reports and apply to each of the Trend contexts, denoted by the prefix of trend. To learn more about Trend Reporting please see the Trend Reports tab in the Reporting User Documentation |
| chart_meta | No | The column order of the new query |
| type | No | The type of Report that the Query is. Standard Advanced Reports are the Default type, denoted as "D". Trend Reports are denoted as "T". To learn more about Trend Reporting please see the Trend Reports tab in the Reporting User Documentation |
PATCH /api/v2/queries/70/ HTTP/1.1
Accept: application/json
Authorization: Token "YOUR SDE ACCESS TOKEN"
{
"chart_meta": {
"columnOrder": []
},
"title": "Sample Query Edited",
"description": "",
"chart": "table",
"query": {
"schema": "application",
"limit": 20,
"order": [],
"filters": [],
"measures": [
"Project.count"
],
"dimensions": [
"Application.id"
]
},
"id": 70,
"created": "2022-06-07T08:56:25.809108-04:00",
"modified": "2022-06-07T08:56:25.809130-04:00",
"created_by": 1
}
HTTP/1.1 200 OK
Content-Type: application/json
{
"id":70,
"title":"Sample Query Edited",
"description":"",
"created":"2022-06-07T08:56:25.809108-04:00",
"modified":"2022-06-07T09:13:28.287805-04:00",
"created_by":1,
"query":{
"schema": "application",
"limit":20,
"order":[
],
"filters":[
],
"measures":[
"Project.count"
],
"dimensions":[
"Application.id"
]
},
"chart":"table",
"chart_meta":{
"columnOrder":[
]
},
"type": "D"
}
Delete a Query
This endpoint deletes a single Query resource, as specified by the query_id parameter.
DELETE /queries/{query_id}/
URL Parameters
| Parameter | Description |
|---|---|
| query_id | The id of the query to delete |
DELETE /api/v2/queries/70/ HTTP/1.1
Accept: application/json
Authorization: Token "YOUR SDE ACCESS TOKEN"
HTTP/1.1 204 NO CONTENT