cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Audit Log API - How to Convert Json Data to Excel

ChadTurner
DynaMight Legend
DynaMight Legend

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


-Chad
9 REPLIES 9

ctturner
Inactive

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
Inactive

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
DynaMight Champion
DynaMight Champion

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


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


rodrigo_alvare1
Dynatrace Champion
Dynatrace Champion

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
DynaMight Champion
DynaMight Champion

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

Sharing Knowledge

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"

Antonio Sousa

Malaik
DynaMight Champion
DynaMight Champion

Thanks

 

But simply, you can select Data from Text file

Worked fine.

 

Great post.

Sharing Knowledge

andras_kovacs03
Participant

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.

andras_kovacs03_0-1676061746319.png

What am I doing wrong?

Can you reproduce the error on your side?

 

 

Featured Posts