MongoDB Atlas® Admin API
Overview
The Qarbine driver for the “MongoDB Atlas API” provides an interactive way to retrieve information about your Atlas deployment. The retrieved data ranges from clusters, to users, to even invoice billings depending on the permissions the Qarbine administrator sets for the token associated with the Qarbine Data Service. This retrieval is completely independent of the application data being stored in your Atlas deployment.
For DBAs and developers this enables interactive reports on the deployment which can consolidate information heavily spread out across numerous MongoDB console pages. Most of the Atlas APIs are oriented for this type of information. For managers and appropriate staff, the driver also provides information about Atlas actual and pending invoices.
Users can interact with this driver using either MongoDB query language (MQL) or a subset of SQL (i.e. joins are not supported at the query level). The data source query and template results can be exported into a variety of formats for much better workflows and knowledge sharing of this information. Templates can apply Qarbine’s vast macro language formula support and even include charts. The results can include dynamic Retrieval Augmented Generations (RAG) and interactive RAG text as well. Important Considerations
Important Notes
Data Visibility
Since Qarbine the software is deployed by you the customer, Qarbine the company has zero visibility to your data in this driver or any other driver.
Network Access
In the Data Source Designer or when running any query, you will see the following when an IP address is not permitted for the associated Atlas API key.
When running a prompt the error is shown as the following.
As a reminder, it is the Qarbine compute node IP address that must be added to the Atlas API allowed IP list, not the IP address of user browsers.
The Atlas Administrator can cross reference values by navigation to the networking section of the MongoDB portal.
API Key Usage
An incorrect Atlas API key as set by the Qarbine Administrator may result in the following,
The Qarbine Administrator needs to cross reference the API key being used by the Qarbine data service to understand the answer sets that API key enables.The Atlas Administrator can cross reference values by navigation to the Access Manager section of the MongoDB portal. The network access permissions of the API key need to be reviewed as well.
Query Language Options
MQL and SQL Querying Options
The Qarbine driver for the Atlas Administration API permits either MongoDB Query Language (MQL) or traditional SQL-like queries. Qarbine presents many virtual collections that can be queried using either of these languages. For example, to get all of the organizations you can run either the SQL
select * from orgs
or using the MongoDB Query Language (MQL)
db.orgs.find()
To get all of the groups within the organization with the identifier “5b5201234567890abcdef” you can run either
select * from 5b5201234567890abcdef.groups
or
db.5b5201234567890abcdef.groups.find()
The use of SQL is useful for queries which do not have criteria based on any embedded arrays or documents. For example, you can get all of a group’s custom roles using either
select * from 5b5201234567890abcdef.customRoles
or
db.5b5201234567890abcdef.customRoles.find()
The SQL querying interface provides for common querying operations and supports several common SQL functions. Sample usage of the toDate() function is shown below.
select *
from 5b5201234567890abcdef.invoices
where startDate > toDate('2022-Jan-01')
and endDate <= toDate('2022-April-30')
See the “SQL Functions Reference” section below for more details.
The custom role objects look like the following.
To filter based on a first level field you can use either
select * from 5b5201234567890abcdef.customRoles
where roleName = 'qf_general_role'
or
db.5b5201234567890abcdef.customRoles.find( {roleName : 'qf_general_role' )
To filter on embedded values you must use MQL. Some examples are
db.5b5201234567890abcdef.customRoles.find(
{ "actions.action" : "CREATE_INDEX" } )
db.5b5201234567890abcdef.customRoles.find(
{inheritedRoles: { db: "admin", role: "atlasAdmin" } } )
For more information on embedded criteria see
Recall that Qarbine can store data retrieval queries as Data Sources separate from the Template data analysis components. In this manner MQL and SQL savvy team members can define data retrieval components which other team members can simply choose by name and move on to their analysis. These retrieval components can be parameterized and the parameter values can be obtained by using a Qarbine no-code Prompt component. There is no need for all team members to be SQL and MQL ninjas!
Basic Queries
Let’s start with 2 simple queries. First, let's get a list of all of the organizations within the Atlas instance for the defined Qarbine Data Service. For the Atlas Administration data, Qarbine supports using either basic SQL or MQL for the queries. We can run either
select * from orgs
or
db.orgs.find()
The query result is the same no matter the syntax you choose to run. Sample output is below.
The ‘//’ snippet in the text above is a convenient way to comment out a line in the Data Source Designer. Select the row to see the details of the organization element on the right hand side. An example is shown below.
We can do a similar query to get a listing of our groups using
Sample output is below.
The details of the group element are shown below.
Note the ‘id’ value above as it will be soon put into use. Double click within the double quotes to select the value. Then right click for the pop up menu.
Then choose “Copy”.
Also note that the MongoDB documentation frequently uses ‘group’ and ‘project’ interchangeably.
Enhanced Queries
The queries above provide the raw organization and group\project identifiers. To query for the clusters in an group we can run
select * from <GROUP_ID>.clusters
A single cluster element is
. . .
. . .
A query using SQL is
select * from 5cbb9798xxxxx.clusters where mongoDBMajorVersion = '5.0'
and one using MQL is
db.5cbb9798ffxxxxx.clusters.find( {mongoDBMajorVersion : '5.0'} )
The answer sets are the same in either case.
Below is a sample Data Source.
It is associated with a prompt component.
Atlas Admin API Data Model
The general root of the MongoDB Atlas data model is the organization.
Organizations and Projects are flexible containers that you can decide how to use based on the need. Authorization boundary needs of your company and application plays an important role in your decision on how to use them.
Ideally, in most cases, you can think of your company as an Organization. This is the container you define organization level settings as well as keep your users and projects in. In most cases one organization is enough as long as you don’t separate the organization owners (and some other user base) or need separate organization settings.
Groups\Projects in each organization have clusters, databases and other properties. Below is a diagram of the virtual schema relationships of the Atlas API.
Virtual Collection Mappings
The virtual names and mappings to their API calls are listed in the tables below. The linked pages provide more detail on the structure and semantics of the returned documents. One nuance is that the organizational table such as “orgUsers” has the “org” prefix in its name to disambiguate between the group table “users”. This naming technique is used for the events, users, and teams information as well.
In the grid below the virtual table is described as “@orgId.group”. To define a query the “@org” placeholder must be replaced with the organization’s identifier. Conveniently for operations accounting analysts and developers, a Qarbine Prompt component can be used to obtain the identifier when running data sources or templates.
General Queries
| Virtual Collection | Reference |
|---|---|
| clusters | /clustershttps://www.mongodb.com/docs/atlas/reference/api/clusters/https://www.mongodb.com/docs/atlas/reference/api-resources-spec/v2/#tag/Clusters/operation/listClustersForAllProjects |
| fieldNames | /alertConfigs/matchers/fieldNameshttps://www.mongodb.com/docs/atlas/reference/api/alert-configurations/https://www.mongodb.com/docs/atlas/reference/api/alert-configurations-get-matchers-field-names/ |
Organization Queries
An organization is a high level object within the Atlas Administration API. The virtual collection to API mappings are listed below. Some general API web pages are
https://www.mongodb.com/docs/atlas/reference/api-resources-spec/v2
https://www.mongodb.com/docs/atlas/app-services/admin/api/v3/
Atlas Invoice Related Queries
The Atlas Administration API provides optional access to invoices at the organization level. The virtual collection to API mappings related to invoices are listed below.
| Virtual Collection | Reference |
|---|---|
| @orgId.invoices | /orgs/{ORG-ID}/invoiceshttps://www.mongodb.com/docs/atlas/reference/api/invoices/ |
| @orgId.pendingInvoices | orgs/{ORG-ID}/invoices/pendinghttps://www.mongodb.com/docs/atlas/reference/api/invoices/ |
Group and Project Queries
Groups and projects are synonymous terms. Your {GROUP-ID} is the same as your project ID. The resource and corresponding endpoints use the term “groups”. You can find your Project ID from the MongoDB Atlas dashboard or by simply running a query in the Qarbine Data Source Designer. App Services APIs frequently require two parameters: your Atlas Project/Group ID, and your App Services Application ID.
SQL Functions Reference
General Functions
abs
cast(value, type)
To cast types to Number you can use one of three forms:
SELECT CAST(a AS NUMBER), a::NUMBER
concat
convert(type, value, style)
Style information can be found at https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16
toDate Performs additional string conversion checking.
date
exp
iif
ifnull
instr
len
lower
lTrim
rTrim
max
min
substr
mid
power
ceil
floor
rowNum
sqrt
trim
upper
replace
Aggregation Functions
sum
avg
count
max
min
first
last
array() – create array of values
quart() - 1st quartile
quart2() - 2nd quartile
quart3() - 3rd quartile
Quartile aggregations are based on the European calculation
median
var
stdev
varp
std
Reviewing the Generated Specification
You can enter criteria of the form “EXPLAIN SELECT ….” to have the SQL statement processed and have the returned answer set be the query just prior to performing the action.
explain
select * from [! @group.id !].clusters
This query has a variable which is prompted for. Prompts are explained in more detail below.
Select the single result element and its details are shown to the right.
A convenient way of specifying this is to have “explain” on the first line and the rest of your SQL on the next lines.
explain
select * from [! @group.id !].clusters
Then simply “comment out” the first line when not in use
// explain
select * from [! @group.id !].clusters
You can also use “explain: true” in the JSON query specification for similar information.
Another way to get the specification is to press ALT and click 
. Below is a sample result.
Any “explain SELECT” or “explain: true” takes precedence over the ALT-click interaction.
Atlas Catalog Templates and Data Sources
Overview
The standard Qarbine catalog deployment provides a variety of template and data source examples in the folder noted below.
Supporting Atlas Admin API Prompts
Organization
The Qarbine catalog contains a prompt named “Prompt for organization” at “example/MongoDB/Atlas API/Prompt for organization”. When run, the prompt shows.
The list of Atlas organizations is based on the determined data service configuration. In this case there is one organization visible from that data service’s Atlas API key. Sample prompt output variables are shown below.
The data sources can then define querying specifications using the following pattern to inject the organization ID for the query.
db.[! @org.id !].groups.find()
The organization object’s identifier field will replace the “[!...!]” text above to form the final query processed by the Atlas API driver.
Group or Project
There is also a Prompt named “Prompt for group” at “example/MongoDB/Atlas API/Prompt for group”. When run, the prompt shows.
The list of Atlas groups (AKA projects) is based on the determined data service’s configuration. The prompt’s elements are shown below.
The 2nd element’s definition includes,
. . .
In the example dialog there is a variable or annotation named “orgName” with a value of “Qarbine”. The determined data service is “Atlas Virtual DB Qarbine” and using its configured API key there are 2 projects. Sample prompt output variables after choosing “project2” are shown below.
Recall that “group” and “projects” are synonymous within the Atlas Admin API world.
The data sources can then define querying specifications using the following pattern to inject the group ID for the query.
db.[! @group.id !].clusters.find()
The group object’s identifier field will replace the “[!...!]” text above to form the final query processed by the Atlas API driver.
Using Dynamic Data Services
Overview
Qarbine’s “dynamic data service” feature mentioned above enables data sources and templates to refer to a general data service by name and have the final data service resolved at runtime. For example, the data service “Atlas VirtualDB @orgName” can be configured with an Atlas API key for the default organization. In this scenario there are also two other data services defined:
| Data Service Name | Description |
|---|---|
| Atlas Virtual DB Prod | Configured with the Atlas API key for Production deployment. |
| Atlas Virtual DB Test | Configured with the Atlas API key for Test deployment. |
The ‘@orgName’ placeholder in the referenced data service name will be dynamically replaced at runtime with a variable value in the following priority order:
- one that has been prompted for (prompts are described below),
- one that has been passed in as a runtime variable,
- a value in the Qarbine principal’s annotations, or
- an annotation value in a principal’s referenced access group.
The Qarbine administrator sets a principal’s annotations. The determined data service must still be visible to the principal to obtain results. This visibility is set by the Qarbine administrator.
End User Set Annotations
End users may also change their annotations by clicking in the upper right of a tool.
In the dialog, navigate to the tab shown and enter key\value pairs separated by commas. Remember to quote any strings containing spaces.
Then click
Then dismiss the subsequent dialog.
A convenient way to move between various organization values is to have several defined but with only one with the “orgName” key. To change, simply edit the annotations for the desired orgName value and update the annotations.
In these examples the dynamic data service variable was “orgName”. It can be any valid variable name.
The determined data service must still be visible to the principal as well to actually end up using.
Shown below is a prompt listing different content based on the orgName value.
Note- If you are switching “orgName” values then you may need to use control-click for the Run button to clear any cached variable values. Some cache clearing is automatically done.
References
For general information see