How to use eG REST API for Extracting eG Data into Power BI?

To pull data from the eG database into Power BI, the following broad steps should be performed from Power BI:

Connect to the eG manager using the HTTP Post method;
Makes eG REST API calls to the eG manager for retrieving the desired data

For achieving both the steps above, a Power Query has to be built and executed in Power BI. Microsoft Power Query provides a powerful "get data" experience that encompasses many features. A core capability of Power Query is to filter and combine, that is, to "mash-up" data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query formula language (informally known as "M").

For building and running a Power Query that extracts data from the eG database, follow the steps below:

Open the Power BI tool, click on the Get Data menu, and select the Blank Query option in it (see Figure 1).

Figure 1 : Selecting the Blank Query option from the Get Data menu
Figure 2 will then appear. Select the Advanced Editor option indicated by Figure 2 to open the Power Query editor.

Figure 2 : Selecting the Advanced Editor option
Use the blank 'let-in' code block that you see in Figure 3 to enter your Power Query.

Figure 3 : The Advanced Editor where you have to enter the Power Query
Your Power Query should include the following:
- payload: This should indicate what data should be extracted from the eG database. For instance, if historical measure data should be pulled, then your payload should indicate the measure name, the test that reports the measure, the component to which the measure pertains, the type of component, and the past period for which data should be pulled. The typical payload specification will be as follows:
  
  payload = "{""timeline"": ""<Past_period_for_data_extraction"",""componentName"": ""<IP_address_of_target_component>:<port_number>"",""componentType"": ""<Name_of_the_component_type>"",""test"": ""<Name_of_the_test>"",""measure"": ""<Name_of_the_measure>""}"
  
  For example, assume that you want to extract data that matches the following specification:
  
  Timeline: Last 1 hour
  
  Measure: Packet loss
  
  Test: Network
  
  Component : 172.168.8.82:80
  
  Component Type: Citrix Delivery Controller 7.x
  
  The payload entry in this case, will be as follows:
  
  payload = "{""timeline"": ""1 hour"",""componentName"": ""172.16.8.82:80"",""componentType"": "Citrix Delivery Controller 7.x"",""test"": ""Network"",""measure"": ""Packet loss""}"
  
  Note:
  - Make sure you specify the test name, measure name, and component type correctly. Use the exact names you see in the eG monitoring console.
  - Remember that the format of you payload specification will change according to the data you want to retrieve. Refer to Automatically Configuring the Target Environment Using eG REST API document to figure out how to build the payload specification for the data of interest to you.
- url: Here, specify the API endpoint URL. The Power Query should hit this URL to pull the desired data.To perform different REST API actions, eG provides different REST URLs. For instance, to retrieve historical data of a measure, the URL to use is: http://<eG manager IP:port>/api/eg/analytics/getHistoricalData. Remember to replace the <eG manager IP:port> in the URL specification with the actual IP and port number of the target eG manager. To know which URLs to use to perform what action, refer to the Automatically Configuring the Target Environment Using eG REST API document.
- options: This section includes a Headers and a Content specification. Against Headers, you need to specify the eG manager to integrate with and the credentials for accessing the eG manager. The Content specification is where you need to set the format into which the payload is to be converted. Typically, it should be set to binary. Given below is the format of the options specification:
  
  options = [
  
  Headers = [#"Content-Type"="application/json",#"user"="<Name_of_valid-eG_user>",#"pwd"="Base64_encoded_password_of_user>",#"managerurl"="http://<eG manager IP:port>"], // Set the required headers
  
  Content = Text.ToBinary(payload) // Convert the payload to binary
  
  ]
  
  Note:
  - Against 'user' and 'password', make sure you specify the credentials of a user who has monitoring rights to the component(s) for which data is to be extracted from the eG database.
  - Make sure that you first use an encoder to encrypt the user's password in the Base-64 format. Then, specify the encoded password against the 'password' parameter.
- response: Specify the POST method syntax, as indicated below:
  
  response = Web.Contents(url, options), // Make the POST request
- data: Specify the format in which the output of the query should be reported. This can be Json or any other format. Given below is a sample data specification:
  
  data = <Output_format>.Document(response)
The complete Power Query will be of the following format:

let

payload = "{""timeline"": ""<Past_period_for_data_extraction"",""componentName"": ""<IP_address_of_target_component>:<port_number>"",""componentType"": ""<Name_of_the_component_type>"",""test"": ""<Name_of_the_test>"",""measure"": ""<Name_of_the_measure>""}"

options = [

Headers = [#"Content-Type"="application/json",#"user"="<Name_of_valid-eG_user>",#"pwd"="Base64_encoded_password_of_user>",#"managerurl"="http://<eG manager IP:port>"], // Set the required headers

Content = Text.ToBinary(payload) // Convert the payload to binary

]

response = Web.Contents(url, options), // Make the POST request

data = <Output_format>.Document(response)

in

To help you understand better, let us now see how the Power Query should be structured for the following sample scenario:

Timeline: Last 1 hour

Measure: Packet loss

Test: Network

Component : 172.168.8.83:80

Component Type: Citrix Delivery Controller 7.x

eG Manager URL: http://172.16.8.82:7077

eG user name: john

Password (Base 64 encoded): c5VjdY7yNXc=

Payload conversion format: binary

Output format: Json

The Power Query that you need to build for performing the sample action described above is as follows:

let

payload = "{""timeline"": ""1 hour"",""componentName"": ""172.16.8.83:80"",""componentType"": "Citrix Delivery Controller 7.x"",""test"": ""Network"",""measure"": ""Packet loss""}"

url = "http://172.16.8.82:7077/api/eg/analytics/getHistoricalData", // Replace with the actual API endpoint URL

options = [

Headers = [#"Content-Type"="application/json",#"user"="john",#"pwd"="c5VjdY7yNXc=",#"managerurl"="http://<eG manager IP:port>"], // Set the required headers

Content = Text.ToBinary(payload) // Convert the payload to binary

],

response = Web.Contents(url, options), // Make the POST request

data = Json.Document(response)

in
Once you configure the Power Query fully within the 'let-in' block, click on the Done button in Figure 3.
Figure 4 will then appear., where you can view the response output. Click on List in Figure 4 to view the list of records the query retrieved from the eG database.

Figure 4 : The Power Query response output
In Figure 5 that then appears, you can view the record list.

Figure 5 : Viewing the list of records returned by the query
Click on any Record in Figure 5 to view the values retrieved. This will invoke Figure 6, where you can view the details of the record that you chose.

Figure 6 : Viewing the values of the chosen record
Also, using the Transform menu in Figure 5 you can even transform the raw output data into any visualization you desire - e.g., graphs, tables, charts etc.