First and foremost you will need to have enabled SaaS audit logging and created an API token that will authorize the audit log Json data. These instruction can be found here: Setting up SaaS Audit Logs
1.) Once you have run the API to capture the data it will then allow you to download this data in the form of a json File: Click Download. The file will then download on your device.
2.) Open up excel and select Data > Get Data > From File > From JSON. Browse and open the recently downloaded Json File
3.) Here we can see that there are 156 entries. lets go ahead and click the list.
4.) You will now see all the records that were in that list that make sup the audit log. To break these out of the list we will need to select "To Table".
5.) Keep the Defaults and select OK
6.) the second part of the data break out is to Select the break out arrows, and select the columns you'd like to be included. These are the columns that are listed in the records. Selected them as desired.
7.) The data will then be broken out. Take this opportunity to move the columns around as you see fir. This can be done by right clicking a column and selecting move.
8.) You can also replace values. For example if you what to change the environment ID to Prod or NonProd you can do so by right clicking the column and selecting "Replace Values".
Additional to this just remember the options that we have to extract information from Dynatrace and read it in CSV format:
1) Use Environment API v1 + script that converts JSON to Excel
- For this, you may extract the information you are interested in Timeseries endpoint. The request will return a JSON file.
- Download the JSON returned
- Use the script from https://github.com/HAtree/DtJsonToCsv to convert the JSON in a CSV format
2) Use Environmental API v2
- Extract information by using the Metrics endpoint and passint he header "accept: text/csv", this will return the metric values in csv format
Check this blog article for more details https://www.dynatrace.com/news/blog/integrate-dynatrace-more-easily-using-the-new-metrics-rest-api/
(example of the call:
curl -X GET "https://<YOUR_URL>api/v2/metrics/query? metricSelector=builtin%3Aservice.response.time&resolution=m&from=-15m" -H "accept: text/csv; header=present; charset=utf-8" -H "Authorization: Api-Token <YOUR_TOKEN>" )
You might want to use jq, like in the command below. I only use the entries below, but you can adapt to your own case. In my case, I also use TSV, as it's better than CSV, as commas may appear elsewhere in the data.
jq -r ".auditLogs|[.eventType,.category,.entityId,.user,.userType,.userOrigin,.timestamp,.success,(.patch|@text)]|@tsv"