SQL queries using the Enterprise API

Table of contents

The CFEngine Enterprise Hub collects information about theenvironment in a centralized database. Data is collected every 5minutes from all bootstrapped hosts. This data can be accessed throughthe Enterprise reporting API.

Through the API, you can run CFEngine Enterprise reports with SQLqueries. The API can create the following report queries:

Synchronous query: Issue a query and wait for the table tobe sent back with the response.
Asynchronous query: A query is issued and an immediate response with an ID is sentso that you can check the query later to download the report.
Subscribed query: Specify a query to be run on a scheduleand have the result emailed to someone.

Synchronous queries

Issuing a synchronous query is the most straightforward way of runningan SQL query. We simply issue the query and wait for a result to comeback.

Request:

code

curl -k --user admin:admin https://test.cfengine.com/api/query -X POST -d{  "query": "SELECT ..."}

Response:

code

{  "meta": {    "page": 1,    "count": 1,    "total": 1,    "timestamp": 1351003514  },  "data": [    {      "query": "SELECT ...",      "header": [        "Column 1",        "Column 2"      ],      "rowCount": 3,      "rows": [      ]      "cached": false,      "sortDescending": false    }  ]}

Asynchronous queries

Because some queries can take some time to compute, you canfire off a query and check the status of it later. This is useful fordumping a lot of data into CSV files for example. The sequence consistsof three steps:

Issue the asynchronous query and get a job id.
Check the processing status using the id.
When the query is completed, get a download link using the id.

Issuing the query

Request:

code

curl -k --user admin:admin https://test.cfengine.com/api/query/async -X POST -d{  "query": "SELECT Hosts.HostName, Hosts.IPAddress FROM Hosts JOIN Contexts ON Hosts.Hostkey = Contexts.HostKey WHERE Contexts.ContextName = 'ubuntu'"}

Response:

code

{  "meta": {    "page": 1,    "count": 1,    "total": 1,    "timestamp": 1351003514  },  "data": [    {      "id": "32ecb0a73e735477cc9b1ea8641e5552",      "query": "SELECT ..."    }  ]]

Checking the status

Request:

code

curl -k --user admin:admin https://test.cfengine.com/api/query/async/:id

Response:

code

{  "meta": {    "page": 1,    "count": 1,    "total": 1,    "timestamp": 1351003514  },  "data": [    {      "id": "32ecb0a73e735477cc9b1ea8641e5552",      "percentageComplete": 42,    ]}

Getting the completed report

This is the same API call as checking the status. Eventually, thepercentageComplete field will reach 100 and a link tothe completed report will be available for downloading.

Request:

code

curl -k --user admin:admin https://test.cfengine.com/api/query/async/:id

Response:

code

{  "meta": {    "page": 1,    "count": 1,    "total": 1,    "timestamp": 1351003514  },  "data": [    {      "id": "32ecb0a73e735477cc9b1ea8641e5552",      "percentageComplete": 100,      "href": "https://test.cfengine.com/api/static/32ecb0a73e735477cc9b1ea8641e5552.csv"    }  ]}

Subscribed Queries

Subscribed queries happen in the context of a user. Any user can createa query on a schedule and have it emailed to someone.

Request:

code

curl -k --user admin:admin https://test.cfengine.com/api/user/name/   subscription/query/file-changes-report -X PUT -d{  "to": "email@domain.com",  "query": "SELECT ...",  "schedule": "Monday.Hr23.Min59",  "title": "Report title"  "description": "Text that will be included in email"  "outputTypes": [ "pdf" ]}

Response:

code