Directly import API data into a spreadsheet

The API Key is associated with the Agent Key user, permissions and assigned Client Groups. If the Agent Key user has been assigned to a Client Group that contains either no Clients or a subset of Clients, API calls made using the generated API Key can only return data associated with the Clients in the assigned Client Group.

Where security requires that the Agent Key user access to Client information within N-sight RMM must be restricted, we recommend that the Agent Key Access is revoked.

You can query the Data Extraction API URL to export data in an XML format to import into your choice of software for analysis and manipulation. You can choose several options to manipulate XML data such as a Microsoft Excel Power Query or using PivotTables.

In this section, we show the basic steps to import data from the Data Extraction API into Microsoft Excel, and provide steps for the 2010 edition.

We recommend you ensure the API URL returns data before you attempt to import data to a spreadsheet.

Due to the reduced feature set for Microsoft Excel online, not all these options may be available if you use the online version.

Import the API data as XML

To enable the Developer tab in the Excel ribbon:

  1. In Microsoft Excel, select the File tab, then go to Options > Customize Ribbon
  2. In the Customize the Ribbon drop-down menu select Main Tabs
  3. Select the Developer check box then OK to apply

To create XML Map:

  1. Open a blank workbook
  2. Go to the Developer tab and click on Source
  3. Click the XML Maps button in the XML Source pane on the right
  4. Click Add, enter the full API URL including the API Key and service call (Format example: https://SERVER/api/?apikey=yourAPIkey&service=list_failing_checks) in the File name then select Open
  5. Click OK to create a schema based on the XML source data when prompted by Excel, then OK again in the XML Maps dialog

  6. To apply this schema to the workbook, hold "result" at the top of the XML schema in the "XML Source" pane then drag it to the target location in the spreadsheet (for example cell 'A1')
  7. Right-click the location to import the API data to (for example cell 'A2') from the context menu select XML > XML Import

  8. Enter the full API URL including the API Key and service call (Format example: https://SERVER/api/?apikey=yourAPIkey&service=list_failing_checks) then Open

    Excel connects to the API, retrieves the data and populates the workbook

    9. To refresh the information in the workbook go to the Data tab, and click Refresh All.

Microsoft Excel 2010

api_excel_imported

  1. Launch Microsoft Excel and create a blank workbook
  2. Click the Data tab then select From Web in the Get External Data section

    3. api_excel_get_data

  3. Enter the API URL in the Address field of the New Web Query dialog and click Go
  4. The contents of the URL display in the dialog’s window and if everything is correct click Import

    api_excel_new_query

    The download of data into Microsoft Excel begins (depending upon its configuration you may be required to accept an XML scheme informational message).

  5. In the Import Data dialog, select the location where you want to put the data

    api_excel_import_data

  6. You can now manipulate and analyze the data using Microsoft Excel, for example with Pivot Tables or a Microsoft Excel Power Query.