Introduction

Sometimes, API Responses aren't ideal in their layout and readability, we can improve this by a number of tools such as JQ - IriusRisk etc.

Another tool in your disposal is the usage of Microsoft excel. This will outline how to import API responses from IriusRisk, such as the audit log, into excel.

Prerequisites

To follow you will need the following:

• a sufficiently up to data excel application and license
• If you are obtaining API results,
  • an appropriate tool to save API output as JSON - API Articles ,
  • an appropriate level of permissions for the associated request,
  • API enabled on your application (Settings > API > API Enabled),
  • and a valid API token

Outcome of this article

to create a readable, easy to follow excel table to display our API responses.

Steps

1. Query the API request of your choice in the tool of your choice. In this example, I am following the /api/v1/audit/events endpoint.
2. Save the response. I will be saving this as raw response (JSON), but additional options are available
   • 
3. Inside Excel, create a new project or sheet, Navigate to 'Data > Get Data' 
   • 
4. Select 'Get Data > From File > From JSON' (or file of choice)
   • 
5. Select your file you saved from (2)
6. The query editor wizard will appear. From here select 'To Table'
   • 
   • Keep the default values on the popup and click OK
7. We should have a setup looking something as follows
   • 
8. Next, we need to convert the list into meaningful records. Select the icon at the top of the column and choose all of the columns we wish to pull into our excel sheet. I also recommend against selecting 'Use original column name as prefix' to keep the output closer to what we have inside IriusRisk.
   • 
   • Click OK
9. Congratulations, we have successfully imported and transformed our JSON data into a meaningful, easy to read format inside excel. Click 'Close & Load' to bring this data into our sheet.
   • 
   • Below is our final output in our excel sheet.
   •