Discussion Feed
06-05-2020
10:02 AM
1 Kudo
Now that I have downloaded metrics from AppDynamics’ Metrics and Snapshots API and converted them into a useful Microsoft Power BI data source, how do I visualize and publish that data?
Part 2 of 2
Overview
In Part 1 of this series, we retrieved the metric data from AppDynamics REST API, and then converted JSON into a table in Power Query Editor.
In this article (Part 2), we will add visualizations to that table and publish the results to Power BI Online.
Table of Contents
Visualize The Data in Power BI
Build the line graph visualization
Experiment with updating of the query parameters
Change Application Parameter
Change Metric Path Parameter
Publishing your report to Power BI Online
Power BI Online Settings for publication
Updating the report in Power BI
Settings for an automatic refresh
Refresh your report in Power BI Online
Example Report available for download
Resources
Visualize The Data in Power BI
Build the line graph visualization
In Visual Designer, insert new Line Chart visual from the Visualizations pane
Resize the visual to your desired shape
Add the value field to Values property of Line Chart visual
Add EventTime field to Axis property of Line Chart visual
Click on down-pointing arrow on EventTime in Axis property of Line Chart visual and change selection to EventTime The Results should look like this: Power BI Visual Designer: building the line graph visualization
You can adjust visualization properties on the Format section of the Line Chart visual to move Legend.
Congratulations, your visualizations are now complete.
Experiment with updating of the query parameters
You can now experiment with changing of APM Application, Metric Path, and Time Range
Click the Home\Queries\Transform Data\Edit Parameters button on the ribbon
Adjust the parameters as needed: Power BI Visual Designer: Modify parameters as needed
Click Apply Changes on the Pending Changes gold bar or click Home\Queries\Refresh to retrieve data from API call again
Change Application Parameter
The application ID can either be the name or the ID of an application. You can retrieve the application ID from the URL of your browser, or from Application Model API.
For example, here is the use of ApplicationID:
Change Metric Path Parameter
You can change Metric Path to use any explicit or wildcard metric path (See Metric and Snapshot API, Using Wildcards). In Edit Parameters, adjust the MetricPath field as needed
The results of this should look like this for all Tiers: MetricPath Parameter change results - Tiers
Or like this for Backends: Changed MetricPath parameter results - Backends
Examples of relevant Metric Path wildcards
Metric
Purpose
Overall Application Performance|Calls per Minute
CPM for entire Application
Overall Application Performance|*|Calls per Minute
CPM for all Tiers in Application
Overall Application Performance|*|*
All metrics for all Tiers in Application
Application Infrastructure Performance|*|Hardware Resources|CPU|%Busy
CPU Usage for All Tiers in Application
Overall Application Performance|*|Individual Nodes|*|Calls per Minute
CPM for all Nodes in all Tiers in Application
Application Infrastructure Performance|*|Individual Nodes|*|Hardware Resources|CPU|%Busy
CPU Usage for all Nodes in all Tiers in Application
Backends|*|Calls per Minute
CPM for all Backends in Application
Business Transaction Performance|Business Transactions|*|*|Calls per Minute
CPM for all Business Transactions in all Tiers in Application
Service Endpoints|*|*|Calls per Minute
CPM for all Service Endpoints in all Tiers in Application
Errors|*|*|Errors per Minute
CPM for all Errors in all Tiers in Application
Information Points|*|Calls per Minute
CPM for all Information Points in Application
Change Time Range Parameter
You can expand the time range by adjusting NumberOfMinutes parameter: Edit Parameters - time range in minutes
Results should look like this:
Resulting report in browser in PowerBI Online
Publishing your report to Power BI Online
Microsoft Power BI Online service can display results of the PBIX file in a web browser after you publish. For this to work, your organization must be licensed for Power BI Online, and you must sign into that service with valid credentials from inside Power BI Desktop.
Power BI Online Settings for publication
Updating the report in Power BI
Settings for an automatic refresh
Refresh your report in Power BI Online
Power BI Online Settings for publication
Click Home\Share\Publish button on the ribbon
Specify the Power BI Online workspace from the list of those to which you have permissions
Wait until publish is complete
After publishing, you can open your report in the web browser:
Updating the report in Power BI
If you want to update the report in Power BI Online, you’ll need to specify credentials as described on https://docs.microsoft.com/en-us/power-bi/connect-data/service-parameters in Settings\Datasets\<yourdatasetname>\Data source credentials\Web\Edit credentials
Then, you can update parameters in Settings\Datasets\<yourdatasetname>\Data source credentials\Parameters
Settings for an automatic refresh
You can also schedule an automatic refresh using the "Settings\Datasets\<yourdatasetname>\Data source credentials\Scheduled refresh" feature:
Refresh your report in Power BI Online
Navigate to the Datasets tab
Click the Actions\Refresh button Manual data refresh
Finally, refresh the actual Report using the "Refresh" icon on the ribbon
Example Report available for download
You can download an example Power BI PBIX report file with all the above steps completed from https://appdynamics.egnyte.com/fl/LNhLFKA5tH
Resources
Tutorial Part 1: How do I retrieve metrics and so I can display them with Microsoft Power BI? - Part 1
... View more
Labels:
06-05-2020
10:02 AM
1 Kudo
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?
Test Your Query and Credentials
Transform the data into PowerBI Format
Set up the Power BI Data Source
Add parameters to the query
Modify Data Source to Use Parameters
Convert JSON to Tabular Data using Transformations
Convert some columns to different data types
Next Steps
Resources
Get REST API URL for Metrics
How do I build my credentials and query?
Create Credentials : You can create an internal AppDynamics account for basic authentication, or Create a Client API token as described in API Clients.
Install the Power BI Desktop client, either from this site https://powerbi.microsoft.com/ or from the Microsoft Store.
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 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
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 Postman
Transform the data into PowerBI Format
Set up the Power BI Data Source
Add parameters to the query
Modify Data Source to Use Parameters
Convert JSON to Tabular Data using Transformations
Convert some columns to different data types
Set up the Power BI Data Source
Open Power BI
Create a new PBIX file
Click Get Data
Select Other\Web
Click Connect
In Power BI > Get Data > Other > Web: Connect
Select the Basic option on the From Web dialog
Paste the REST URL previously tested
Click OK Paste the REST URL into the Basic option on the From Web dialog
If you’ve never authenticated to this data source before, you’ll be prompted for credentials, as follows: Access Web Content data source authentication settings
If using AppDynamics internal username/password, switch to the Basic tab.
Specify "username@account" in the "User Name" textbox
Specify your password in Password textbox
If using Client API token, specify "BEARER" in the username, and value of the token in Password
Click Connect 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 PowerBI
Add parameters to the query
Rename Query1 to GetMetricData in the list of Queries on the left
Click the Home\Parameters\Manage Parameters button on the ribbon
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 Name Manage Parameters dialog: MetricPath Manage Parameters dialog: Number of Minutes
You’ll be able to modify these parameters later.
Modify Data Source to Use Parameters
Click the Home\Data Sources\Data source settings button on the ribbon
Select your data source and click the Change Source button
Click the Advanced radio button Data Source Dialog > Advanced Settings > Constructing REST URL from parameters
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 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
Click OK
Click Close
Click the Home\Query\Refresh Preview button on the ribbon
Convert JSON to Tabular Data using Transformations
Click on GetMetricData query in the Queries list
Select the first record in the list
Click Transform\Convert\To Table button on the ribbon
Confirm default transformation settings in To Table dialog
Click OK Convert JSON List to Table
Select Column1 column in the query results window
Click Transform\Structured Column\Expand on the ribbon
Remove any text from the Default column name prefix text box
Click OK Expand Table to Columns
Select the metricValues column in the query results window
Click Transform\Structured Column\Expand in the ribbon Expand Table to Columns again
Select the metricValues column in the query results window again
Click Transform\Structured Column\Expand in the ribbon
Remove any text from the Default column name prefix text box
Click OK
Convert some columns to different data types
Select startTimeInMillis column in query results window
Click Add Column\General\Custom Column button on the ribbon
Specify EventTime in New column name text box
Enter following formula in the Custom column formula text box: =#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [startTimeInMillis]/1000)
Click OK Create DataTime column out of Unix timestamp
Select EventTime column in the query results window
Click Transform\Any Column\Data Type drop-down and select Date/Time Explicitly define DateTime column type
Select value column in the query results window
Click Transform\Any Column\Data Type drop-down and select Whole Number Change 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 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"
Click Home\Close\Close & Apply on the ribbon to save all the changes and apply the query
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.
... View more
Labels:
02-04-2019
03:15 PM
2 Kudos
That data is not available via any API.
You can extract flow map using https://github.com/Appdynamics/AppDynamics.DEXTER, specifically as part of https://github.com/Appdynamics/AppDynamics.DEXTER/wiki/Entity-Metrics-Report report. They are called Activity Grids in this context.
... View more
02-04-2019
03:14 PM
2 Kudos
This can be done for APM applications using https://github.com/Appdynamics/AppDynamics.DEXTER, specifically in https://github.com/Appdynamics/AppDynamics.DEXTER/wiki/Configuration-Report.
For all other types of applications, that is going to be in the next release per https://github.com/Appdynamics/AppDynamics.DEXTER/wiki#future-roadmap
... View more
12-14-2018
01:40 PM
1 Kudo
Vaibhav,
What kind of data are you exfiltrating with your scripts? Metric data? Events? Which ones?
Are you aware of the https://github.com/Appdynamics/AppDynamics.DEXTER/wiki toolkit? It wraps a lot of things you can't ordinarily get into and gives them to you in data warehouse format, ready to go into your data lake.
... View more
11-06-2018
05:16 PM
1 Kudo
Next release of DEXTER will provide support for Policies. That is listed on https://github.com/Appdynamics/AppDynamics.DEXTER/wiki#future-roadmap
... View more
11-06-2018
05:15 PM
Hello there,
If you are using SAML or LDAP, those types of accounts do not work for REST API and thus for DEXTER. This is documented here https://github.com/Appdynamics/AppDynamics.DEXTER/wiki/Job-File#username-string
... View more
10-08-2018
09:25 AM
1 Kudo
Hello there,
Please download Build https://github.com/Appdynamics/AppDynamics.DEXTER/releases/tag/1.0.0.35, recreate your job file using DefaultJob.json template in the 35 build and try again.
Daniel
... View more
10-03-2018
03:31 PM
3 Kudos
AppDynamics DEXTER Configuration report includes Health Rules (which is confusingly called internally Policies) https://github.com/Appdynamics/AppDynamics.DEXTER/wiki/Configuration-Report#13health-rules.
The list of Policies as they are called in the UI is not in any DEXTER report yet.
You can easily export and parse them by using https://docs.appdynamics.com/display/PRO45/Configuration+Import+and+Export+API#ConfigurationImportandExportAPI-ExportPolicies
Hope that helps,
Daniel
... View more
09-11-2018
09:07 AM
As you can see in https://github.com/Appdynamics/AppDynamics.DEXTER/wiki/Job-File#target-section,
the URL to the Controller is just its host name. You don't need anything after the host name. Remove /controller/rest/applications.
... View more
Latest Activity
- Got a Kudo for Re: Get Metrics via REST. 06-25-2020 11:50 AM
- Got a Kudo for How do I retrieve metrics and so I can display them with Microsoft Power BI? - Part 1. 06-05-2020 11:03 AM
- Got a Kudo for How do I display and publish metrics data with Microsoft Power BI? - Part 2. 06-05-2020 11:02 AM
- Posted How do I display and publish metrics data with Microsoft Power BI? - Part 2 on Knowledge Base. 06-05-2020 10:02 AM
- Posted How do I retrieve metrics and so I can display them with Microsoft Power BI? - Part 1 on Knowledge Base. 06-05-2020 10:02 AM
- Got a Kudo for Re: Extracting Flow View via API - Too Much or Too Little and looking for Just Right. 05-07-2020 04:14 PM
- Got a Kudo for Re: Collect list of agents. 02-26-2020 10:37 AM
- Got a Kudo for Re: extract overall all configured alerts(Health Rule) in AppDynamics. 02-11-2020 02:29 PM
- Got a Kudo for Re: How to save information point results. 02-10-2020 09:53 PM
- Got a Kudo for Re: Get Metrics via REST. 02-14-2019 05:45 AM
- Got a Kudo for Re: Extracting Flow View via API - Too Much or Too Little and looking for Just Right. 02-04-2019 03:16 PM
- Posted Re: Extracting Flow View via API - Too Much or Too Little and looking for Just Right on Infrastructure (Server, Network, Database). 02-04-2019 03:15 PM
- Got a Kudo for Re: extract overall all configured alerts(Health Rule) in AppDynamics. 02-04-2019 03:15 PM
- Posted Re: extract overall all configured alerts(Health Rule) in AppDynamics on Controller (SaaS, On Premise). 02-04-2019 03:14 PM
- Got a Kudo for Re: Has anyone used Kafka-Connect to Publish Appdynamics Data to Other Source?. 12-14-2018 01:54 PM
- Posted Re: Has anyone used Kafka-Connect to Publish Appdynamics Data to Other Source? on Latest Release. 12-14-2018 01:40 PM
- Got a Kudo for Re: #DEXTER: Do we have any reports in Dexter where we can check the policies?. 11-07-2018 02:18 PM
- Posted Re: #DEXTER: Do we have any reports in Dexter where we can check the policies? on End User Monitoring (EUM). 11-06-2018 05:16 PM
- Posted Re: Need help with Dexter on End User Monitoring (EUM). 11-06-2018 05:15 PM
- Got a Kudo for Re: Is collating transaction errors possible?. 10-29-2018 03:54 PM
Community Stats
Date Registered | 01-06-2015 09:47 AM |
Date Last Visited | 08-14-2020 02:05 PM |
Total Messages Posted | 34 |
Total Kudos Received | 22 |