Audit Log API - How to Convert Json Data to Excel

[ChadTurner]

Many people have asked how to convert Json Files into Excel. Follow these simple stepson how to get started.

[ctturner · Accepted solution]

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".

[ctturner · Accepted solution]

9.) Fill out the form as desired and select ok.

10.) Once you are satisfied with the lay out, select Close and load. The data will now be imported into Excel.

11.) The results:

[larry_roberts]

Excellent post! Love tutorial contributions like this. Thank you!

[ctturner]

Thank you! You are more then welcome! Happy to share my knowledge!

[rodrigo_alvare1]

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>" )

[Malaik]

Thanks @ChadTurner very helpful
But Im in environment with Excel 2016, no way to find the option to import json file.

[AntonioSousa]

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"

[Malaik]

Thanks

But simply, you can select Data from Text file

Worked fine.

Great post.

[andras_kovacs03]

Hi @ctturner, 

great post, it works for most APIs, but it just doesn't work for Cluster API v1 GET /groups/managementZones.

As I break out the mzPermissions coloumn, I get an error message: We cannot convert a value of type Record to type Text.

What am I doing wrong?

Can you reproduce the error on your side?