719 - How to use OData endpoints to query the Storage Platform

To provide more flexible reporting of the Storage Platform (SP), the OData protocol can now be used for easier and richer interrogation:

1. Access the following endpoint on our Preview SP: https://<AccountServer>/api/odata
   The full schema can also be seen here: https://<AccountServer>/api/odata/$metadata
2. Log in with the your Storage Platform administrative credentials (as you normally would on the Console).

(For more information about the standard OData query options, such as select, filter, and expand, see MSDN article 301461.)

URL examples

Here are some example URLs with query options.

1. To retrieve all fields on all groups (including collections):

/api/odata/Groups

2. To retrieve only the paths of all groups:

/api/odata/Groups?$select=GroupPath

3. To retrieve all Accounts for each group per level:

/api/odata/Groups?$select=GroupPath&$expand=Accounts

4. To retrieve all Accounts for each group per level, but only present the account name and last backup date for each Account:

/api/odata/Groups?$select=GroupPath&$expand=Accounts($select=AccountName,LastBackupDate)

5. To retrieve a Group with the ID you specify:

/api/odata/Accounts?$filter=BackupGroupId eq {ID}

e.g. https://previewsp.redstor.com/api/odata/Accounts?$filter=BackupGroupId eq 2274
Note: Include white spaces.

6. The query from point 5 can be combined with the subset criteria from point 4 to present only certain fields:

/api/odata/Accounts?$filter=BackupGroupId eq {ID}&$select=AccountName,LastBackupDate

7. Experiment with more complex combinations:

E.g.

• https://previewsp.redstor.com/api/odata/Accounts?$filter=BackupGroupId eq 2274&$select=AccountName,LastBackupDate&$orderby=LastBackupDate
• https://previewsp.redstor.com/api/odata/Accounts?$filter=BackupGroupId eq 2274&$select=AccountName,LastBackupDate&$expand=Backups($filter=BackupTraceId gt 1726196) 
• https://previewsp.redstor.com/api/backup/accounts?$filter=BackupGroupId eq 2274&$select=Name,LastBackupDate

Using Excel 2013 & 2016

Perform these steps:

1. Excel 2013 only:
   
   1. You'll also need the Power Query add-in when using Excel 2013. Download and install it (either 32-bit or 64-bit) from the Microsoft site here.
   2. While in Excel, from the main menu, on the Power Query tab, click From Other Sources > From OData Feed.
2. Excel 2016 only:
   
   1. While in Excel, from the main menu, on the Data tab, click New Query > From Other Sources > From OData Feed.
3. Enter the OData endpoint/URL for your AccountServer. Click OK.
4. Select the ".../api/odata" option.
5. Select "Basic" in the left-hand pane and enter your Storage Platform login credentials (as you would in the Console)
6. Click Connect.
7. Proceed to the next step in the wizard to select relevant tables.

Note: If you are unable to connect, ensure that the root certificate and Storage Platform certificate have been added to your list of Trusted Authorities (see this article).

The Navigator will open. From here you can select various tables and preview the data in them before retrieving it in a spreadsheet.

Using Power BI

If you have Power BI, you can leverage its reporting capabilities as you would with Excel. Do the following:

1. From the menu in the Home tab, select Get Data and then select OData Feed.
2. Enter the URL for the OData endpoint on your Storage Platform: https://<AccountServer>/api/odata
3. Enter your Storage Platform administrative credentials (as you normally would when logging in to the Console).
4. Select the entities to import
   Note: Some entities won't be available to Group Administrators and might require Storage Platform-level access.
5. Wait for the import process to complete.

Storage Platform data can now be used by Power BI.