Knowledge Base

cancel
Showing results for 
Search instead for 
Did you mean: 

How do I retrieve metrics and so I can display them with Microsoft Power BI? - Part 1

What method can I use to programmatically call AppDynamics’ Metrics and Snapshots API from Microsoft Power BI to retrieve time series/metrics data, and then display that data using MS Power BI?
Part 1 of 2

 

Overview

AppDynamics Metrics and Snapshots API  can be easily called programmatically.

 

If you want to get Time Series/Metrics data from Microsoft Power BI, first consider whether you want to retrieve it directly via API, or indirectly through a batch process such as Entity Metrics Report in Power BI

 

NOTE: Power BI is only available for Windows

This article focuses on how to retrieve Time Series/Metrics data directly via API.

To learn how to take the next steps of visualizing and publishing this data in Power BI, see How do I display and publish metrics data with Microsoft Power BI? Part 2 .

 

Table of Contents

 

Get REST API URL for Metrics

 

How do I build my credentials and query?

  1. Create Credentials:
    You can create an internal AppDynamics account for basic authentication, or Create a Client API token as described in API Clients

  2. Install the Power BI Desktop client, either from this site https://powerbi.microsoft.com/ or from the Microsoft Store.

  3. Get REST URL for Metric Extraction:
    In AppDynamics, open Metric Browser and find the metric you want.

    For example, here we are retrieving "Overall Application Performance|Calls per Minute" metric for an Application.
    Metric Browser - E-Commerce: retrieving "Overall Application Performance|Calls per Minute" metric for an ApplicationMetric Browser - E-Commerce: retrieving "Overall Application Performance|Calls per Minute" metric for an Application
    As above, right-click on metrics and click copy the URL to REST API invocation.

    The resulting URL will look something like this:
    http://yourcontrollerhost.maybeonsaas.com/controller/rest/applications/ECommerce/metric-data?metric-path=Overall%20Application%20Performance%7CCalls%20per%20Minute&time-range-type=BEFORE_NOW&duration-in-mins=60​

 

  1. Modify the URL by appending "output=json" and "rollup=false" parameters like this:
    http://yourcontrollerhost.maybeonsaas.com/controller/rest/applications/ECommerce/metric-data?metric-path=Overall%20Application%20Performance%7CCalls%20per%20Minute&time-range-type=BEFORE_NOW&duration-in-mins=60

    The components in the Query String are as follow:

    "metric-path=Overall Application Performance|Calls per Minute"

    specifies metric path

    "time-range-type=BEFORE_NOW"

    specifies that the measuring is going from NOW back some time range. Other values can be "BETWEEN_TIMES"

    "duration-in-mins=60"

    specifies how many minutes to go back from BEFORE_NOW

    "output=json"

    specifies that the API should return data formatted as JSON object

    "rollup=false"

    specifies that all metric values should be returned

 

Test Your Query and Credentials

Use your favorite tool—such as cURL, Wget, PowerShell, or Postman—to test the query. Below, it’s being tested in Postman and clearly returns some nice data:

 

Query results in PostmanQuery results in Postman


 

Transform the data into PowerBI Format

 

Set up the Power BI Data Source

  1. Open Power BI

  2. Create a new PBIX file

  3. Click Get Data

  4. Select Other\Web

  5. Click Connect

In Power BI > Get Data > Other > Web: ConnectIn Power BI > Get Data > Other > Web: Connect

  1. Select the Basic option on the From Web dialog

  2. Paste the REST URL previously tested

  3. Click OK
    Paste the REST URL into the Basic option on the From Web dialogPaste the REST URL into the Basic option on the From Web dialog

  4. If you’ve never authenticated to this data source before, you’ll be prompted for credentials, as follows:
    Access Web Content data source authentication settingsAccess Web Content data source authentication settings

    1. If using AppDynamics internal username/password, switch to the Basic tab.
    2. Specify "username@account" in the "User Name" textbox
    3. Specify your password in Password textbox
    4. If using Client API token, specify "BEARER" in the username, and value of the token in Password
    5. Click Connect
      image1.png

      Your credentials will be saved in both this workbook and in the global credential cache for your OS.

 

Congratulations! You have data from AppDynamics as a JSON document. You can't do much with it yet, though. Next, you must massage it into something you can graph.

 

Data in unprocessed form in the Power Query editor in PowerBIData in unprocessed form in the Power Query editor in PowerBI

 

Add parameters to the query

  1. Rename Query1 to GetMetricData in the list of Queries on the left

  2. Click the Home\Parameters\Manage Parameters button on the ribbon

  3. Create 3 new Parameters by clicking the New button at the top of the parameter list, then entering the following settings:

    Name

    Type

    Value

    ApplicationNameOrID

    String

    [Name or ID of your Application]

    MetricPath

    String

    Overall Application Performance|Calls per Minute

    NumberOfMinutes

    String

    60


    Manage Parameters dialog: Application NameManage Parameters dialog: Application Name

    Manage Parameters dialog: MetricPathManage Parameters dialog: MetricPath

    Manage Parameters dialog: Number of MinutesManage Parameters dialog: Number of Minutes

You’ll be able to modify these parameters later.

 

Modify Data Source to Use Parameters

  1. Click the Home\Data Sources\Data source settings button on the ribbon

  2. Select your data source and click the Change Source button

  3. Click the Advanced radio button
    Data Source Dialog > Advanced Settings > Constructing REST URL from parametersData Source Dialog > Advanced Settings > Constructing REST URL from parameters

  4. Using the buttons to the left of the URL Part, create the following flow:

    Part Type

    Value

    Text

    http[s]://<yourcontroller>/controller/rest/applications/

    Parameter

    ApplicationNameOrID

    Text

    /metric-data?metric-path=

    Parameter

    MetricPath

    Text

    &time-range-type=BEFORE_NOW&duration-in-mins=

    Parameter

    NumberOfMinutes

    Text

    &output=json&rollup=false


    The screen should look like this:
    Parameters intermingled with text literals produce the REST API URLs that can accept parametersParameters intermingled with text literals produce the REST API URLs that can accept parameters

    The URL Preview text box shows the result of your configuration and it should look like this: 
    http://yourcontrollerhost.maybeonsaas.com/controller/rest/applications/{ApplicationNameOrID}/metric-data?metric-path={MetricPath}&time-range-type=BEFORE_NOW&duration-in-mins={NumberOfMinutes}&output=json&rollup=false

     

  5. Click OK

  6. Click Close

  7. Click the Home\Query\Refresh Preview button on the ribbon

 

Convert JSON to Tabular Data using Transformations

  1. Click on GetMetricData query in the Queries list

  2. Select the first record in the list

  3. Click Transform\Convert\To Table button on the ribbon

  4. Confirm default transformation settings in To Table dialog

  5. Click OK
    Convert JSON List to TableConvert JSON List to Table

  6. Select Column1 column in the query results window

  7. Click Transform\Structured Column\Expand on the ribbon

  8. Remove any text from the Default column name prefix text box

  9. Click OK
    Expand Table to ColumnsExpand Table to Columns

  10. Select the metricValues column in the query results window

  11. Click Transform\Structured Column\Expand in the ribbon
    Expand Table to Columns againExpand Table to Columns again

  12. Select the metricValues column in the query results window again

  13. Click Transform\Structured Column\Expand in the ribbon

  14. Remove any text from the Default column name prefix text box

  15. Click OK

 

Convert some columns to different data types

  1. Select startTimeInMillis column in query results window

  2. Click Add Column\General\Custom Column button on the ribbon

  3. Specify EventTime in New column name text box

  4. Enter following formula in the Custom column formula text box:
    =#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [startTimeInMillis]/1000)

  5.  Click OK
    Create DataTime column out of Unix timestampCreate DataTime column out of Unix timestamp

  6. Select EventTime column in the query results window

  7. Click Transform\Any Column\Data Type drop-down and select Date/Time
    Explicitly define DateTime column typeExplicitly define DateTime column type

  8. Select value column in the query results window

  9. Click Transform\Any Column\Data Type drop-down and select Whole Number
    Change data type of the value column to numberChange data type of the value column to number

 

Repeat these steps for all other columns you have a need for, like "sum", "count", "min", "max" and "standardDeviation".

 

Resulting Dataset

You are done! The resulting dataset is a thing of beauty.

The resulting dataset is a thing of beautyThe resulting dataset is a thing of beauty

 

Final GetMetricData query in Power Query M

Your final query for GetMetricData in Power Query M language should look like this:

let

    Source = Json.Document(Web.Contents("http://demo2.appdynamics.com/controller/rest/applications/" & ApplicationNameOrID & "/metric-data?metric-path=" & MetricPath & "&time-range-type=BEFORE_NOW&duration-in-mins=" & NumberOfMinutes & "&output=json&rollup=false")),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"metricId", "metricName", "metricPath", "frequency", "metricValues"}, {"metricId", "metricName", "metricPath", "frequency", "metricValues"}),

    #"Expanded metricValues" = Table.ExpandListColumn(#"Expanded Column1", "metricValues"),

    #"Expanded metricValues1" = Table.ExpandRecordColumn(#"Expanded metricValues", "metricValues", {"startTimeInMillis", "occurrences", "current", "min", "max", "useRange", "count", "sum", "value", "standardDeviation"}, {"startTimeInMillis", "occurrences", "current", "min", "max", "useRange", "count", "sum", "value", "standardDeviation"}),

    #"Added Custom" = Table.AddColumn(#"Expanded metricValues1", "EventTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [startTimeInMillis]/1000)),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"EventTime", type datetime}, {"value", Int64.Type}})

in

    #"Changed Type"

 

  1. Click Home\Close\Close & Apply on the ribbon to save all the changes and apply the query

  2. Save the resulting PBIX file somewhere on your machine

 

Next Steps

To visualize and publish your data using Microsoft Power BI, you will need the PBIX file you saved in Step 11, above.

 

Go to How do I display and publish metrics data with Microsoft Power BI? - Part 2 for the step-by-step instructions.

 

Resources

To learn how to take the next steps of visualizing and publishing this data in Power BI, see How do I display and publish metrics data with Microsoft Power BI? Part 2.

Version history
Revision #:
26 of 26
Last update:
‎06-05-2020 10:08 AM
Updated by:
 
Labels (1)


Found this article helpful? Click the Thumbs Up button.
Have an additional comment? Post it below.


Found this article helpful? Click the Thumbs Up button.
Have an additional comment? Post it below.