Import and export with the OData API (copied)
Introduction
Scalable provides an HTTPS-based OData API for programmatic or direct access to stored data, such as that collected and processed by Asset Vision or Acumen. The API can be accessed directly from a web browser, an API development tool like Microsoft Power BI or suitable code. The API returns data in either XML or JSON formats. In addition, the API will support adding or updating records.
Configuration Overview
Before using the OData API it first needs to be configured for your use model. This consists of:
1. Creating dataviews that will be exposed as API endpoints.
2. Creating the OData API endpoints.
3. Configuring authorization.
Creating Dataviews
Dataviews are used to group semantically related data required for a specific task.
1. Open the product UI once logged in as an Administrator.
2. Go to Admin -> Data View Management.
3. Click “New”.
4. Enter “Data View Name” and select a “Base Table” (for example, data view name “OData - Computers”, base table “Computers”).
5. Click “Save”.
Creating OData Endpoints
OData API endpoints provide the access name for the dataview.
1. Open the product UI once logged in as an Administrator.
2. Go to Setup -> OData -> OData Views.
3. Click “New”.
4. Enter “Endpoint Name” and select the previously created data view as the “UI Data View” to be exposed in API.
5. Select the "Read Only" option if you are only going to be querying data, not adding or updating records.
5. Click “Save”.
6. Right-click the created endpoint record in the list of endpoints and choose “Get URL”.
7. Copy the provided endpoint URL. You will need it in order to access the API.
Authorization using HTTP Basic Auth
In order to use the API, you must first setup authentication using an existing Asset Vision user credentials. Postman will be used to demonstrate the usage of the API.
1. Open the Postman application.
2. Select New -> Request.
3. Provide a name for the new request.
4. Under “Select a collection or folder to save to”, specify a collection name and save.
5. Click on the “Authorization” tab.
6. Select “Basic Auth” as the type.
7. Enter your Asset Vision user account and save.
Authorization using OAuth 2.0 – Client Credentials flow
Client Credentials authentication flow allows a client application to access the OData API on behalf of a specific Asset Vision user account. In order to use OAuth, the client application must first be registered using the following steps.
1. Open Asset Vision UI once logged in as an Administrator.
2. Go to Setup -> OAuth Provider -> Clients.
3. Click “New” and choose “OAuth Client Credentials Flow Client”.
4. Enter name and select user on whose behalf the client application will be accessing OData API.
5. Copy and save in a secure place the “Client ID”, “Client Secret”, and “Token URL” values.
6. Click “Apply” (or click “Save”, and then open this newly created record for editing again).
7. Open the “OAuth Client Granted Scopes” tab, click “New”, select “odataview” scope value, and click “Save”.
Postman will be used as an example of a client application.
1. Open the Postman application.
2. Select New -> Request.
3. Provide a name for the new request.
4. Under “Select a collection or folder to save to”, specify a collection name and save.
5. Click on the “Authorization” tab.
6. Select “OAuth 2.0” as the type.
7. Set “Add authorization data to” to the value “Request Headers”.
8. Click “Get New Access Token”.
9. Enter token name.
10. Choose “Client Credentials” grant type.
11. Fill in “Access Token URL”, “Client ID”, “Client Secret” with the previously saved values.
12. Set scope to “odataview”.
13. Click “Request Token”.
14. Click “Use Token”.
Authorization using OAuth 2.0 – Password Credentials flow
Password Credentials authentication flow allows different users to access OData API from the same client application using their corresponding product account credentials. Setup for this flow is very similar to Client Credentials flow.
1. Open Asset Vision UI once logged in as an Administrator.
2. Go to Setup -> OAuth Provider -> Clients.
3. Click “New” and choose “OAuth Resource Owner Password Flow Client”.
4. Enter client name.
5. Copy and save in a secure place “Client ID”, “Client Secret”, and “Token URL” values.
6. Click “Apply” (or click “Save” and then open this newly created record for editing again).
7. Open “OAuth Client Granted Scopes” tab, click “New”, select “odataview” scope value, and click “Save”.
Postman will be used as an example of a client application.
1. Open the Postman application.
2. Select New -> Request.
3. Provide a name for the new request.
4. Under “Select a collection or folder to save to”, specify a collection name and save.
5. Click on the “Authorization” tab.
6. Select “OAuth 2.0” as the type.
7. Set “Add authorization data to” to the value “Request Headers”.
8. Click “Get New Access Token”.
9. Enter token name.
10. Choose “Password Credentials” grant type.
11. Fill in “Access Token URL”, “Client ID”, “Client Secret” with the previously saved values.
12. Enter username and password from your Asset Vision user account.
13. Set scope to “odataview”.
14. Click “Request Token”.
15. Click “Use Token”.
Querying Data
The following queries enable the Asset Vision user to query and export data from the previously created OData API endpoints. To start submitting requests using Postman, enter the desired query URL as the “GET” operation parameter. Click “Send” to submit the query.
Usage:
Usage for querying and returning data in XML or JSON formats:
https://<av-website>/odataview/v1/<endpoint-name>?$format=<format>
Examples:
https://odata.live.scalable.com/odataview/v1/computers?$format=json
https://odata.live.scalable.com/odataview/v1/computers?$format=xml
Note: The results from the queries above will provide an “ID” value that can be used to query for individual data records.
Usage for querying for individual data records by ID:
https://<av-website>/odataview/v1/<endpoint-name>(<id>)
Example:
https://odata.live.scalable.com/odataview/v1/computers(155)
Usage for querying just one field:
https://<av-website>/odataview/v1/<endpoint-name>(<id>)/<field-name>
Example:
https://odata.live.scalable.com/odataview/v1/computers(155)/Name
Usage for querying metadata, list of endpoints:
https://<av-website>/odataview/v1/
Usage for querying the schema that is used for data returned by endpoints:
https://<av-website>/odataview/v1/$metadata
Examples:
https://odata.live.scalable.com/odataview/v1/
https://odata.live.scalable.com/odataview/v1/$metadata
Usage for querying for count of records:
https://<av-website>/odataview/v1/<endpoint-name>/$count
Example:
https://odata.live.scalable.com/odataview/v1/computers/$count
Usage for querying using a filter:
https://<av-website>/odataview/v1/<endpoint-name>/$filter=<filter>
Examples:
https://odata.live.scalable.com/odataview/v1/computers/?$filter=Asset_tag eq '12345'
https://odata.live.scalable.com/odataview/v1/software?$filter=Publisher eq '12d Solutions' and SoftwareProduct eq '12d Model'
Usage for querying using paging:
https://<av-website>/odataview/v1/<endpoint-name>?$top=<integer>&$skip=<integer>
Example:
https://odata.live.scalable.com/odataview/v1/computers?$top=5&$skip=20
Note: The parameters $top and $skip allow you to choose which section of a large dataset you want to return. In addtion, a GET query will return 200 records by default. You must use paging to return data from datasets that are larger than 200 records.
Inserting Records
The Asset Vision OData API provides the capability to insert records into an Asset Vision table using a POST request. Once again, we will be using Postman as an example. To start submitting POST requests using Postman, enter the desired URL endpoint and select “POST” as the operation parameter.
Usage:
Next, you will need to specify the data that you want to insert into Asset Vision. In the example below, we will be passing in Name, Phone and Email data in the body of the request.
| Example JSON body text: |
|---|
| { "Name": "John Doe", "Phone": "111-222-3333", "Email": "[email protected]" } |
Once you click on "Send", the data will get immediately added into the table associated with the endpoint.
Updating Records
The Asset Vision OData API provides the capability to update records to a table in Asset Vision. As an example, to start submitting PUT requests using Postman, enter the desired URL endpoint and select “PUT” as the operation parameter. Additionally, you will need to specify the ID of record that you want to update using the "record-id" parameter like below.
Usage:
Next, you will need to specify the data for the record that you want to update in Asset Vision. Similar to the POST example, we will be passing in updated Name, Phone and Email values in the body of the request.
| Example JSON body text: |
|---|
| { "Name": "Bill Smith", "Phone": "444-555-6666", "Email": "[email protected]" } |
Once you click on "Send", the record will get immediately updated in the table associated with the endpoint.
Querying Data using Excel
1. In an Excel worksheet, click Data, then Get Data (Power Query):
2. From the resulting dialog, select OData…
3. In the resulting Excel dialog, enter the URL endpoint and credentials, then click Next…
4. In the resulting Power Query Editor, you can play around with filtering/sorting/etc. When done, click Close & load, and the data will appear in the sheet.