topic Re: Audit Log API - How to Convert Json Data to Excel in Dynatrace tips

Audit Log API - How to Convert Json Data to Excel

ChadTurner — Mon, 04 Sep 2023 09:19:05 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

ctturner — Fri, 17 Jan 2020 13:54:14 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

ctturner — Fri, 17 Jan 2020 13:54:25 GMT

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:

Re: Audit Log API - How to Convert Json Data to Excel

larry_roberts — Fri, 17 Jan 2020 13:58:05 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

ctturner — Fri, 17 Jan 2020 14:00:12 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

rodrigo_alvare1 — Thu, 20 Feb 2020 17:36:21 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

Malaik — Sun, 11 Jul 2021 07:18:40 GMT

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

Re: Audit Log API - How to Convert Json Data to Excel

AntonioSousa — Sun, 11 Jul 2021 09:56:53 GMT

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"

Re: Audit Log API - How to Convert Json Data to Excel

Malaik — Sun, 11 Jul 2021 11:49:14 GMT

Thanks

But simply, you can select Data from Text file

Worked fine.

Great post.

Re: Audit Log API - How to Convert Json Data to Excel

andras_kovacs03 — Fri, 10 Feb 2023 20:44:24 GMT

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?