If you have exported a security events JSON file from our system and want to import it into Microsoft Excel, this article is for you. If you want to export security events in CSV format to import it into Microsoft Excel, this article is for you. Through a straightforward example, we will show the proper method of importing a JSON file into Microsoft Excel using the Power Query feature.
Exporting security events from the portal
- In the Elements Endpoint Protection portal, on the Security events page, filter the data according to your needs. For example, you might want to see all the events for the last 30 days that require your attention.
- Export the filtered security events by selecting Export security events (JSON).
- After exporting the JSON file with the filtered events, proceed to the second part on how to import the file to Microsoft Excel.
Importing the JSON file into Microsoft Excel
The gif file below shows you the importing process:
To import JSON file:
- Open Microsoft Excel.
- Select the Data tab, and then select Get Data > From File > From JSON.
- Select the JSON file that you exported and then press Import.
A new window pops up showing a list of "Record" entries.
The following steps demonstrate how you can transform this list to a usable Excel table.
- Right-click the column heading and from the pop-up menu, select To table.
Note: You can keep default values of the following two settings, and press OK.
The only visible change is that the column heading now shows "Column1" (instead of "List").
- Click the expand button next to the Column1 heading.
The following dialog box opens.
- If you have exported more than 1000 events having more than one source, we suggest that you click Load more to load properties from other events.
Note: By default, the system loads only the first thousand entries.
Important: Uncheck the 'Use original column name as prefix' option as there is no need to save the "Column1" name.
You now have the data in a table format.
- You can leave the timestamp format (scientific) as is and change it later in Excel workbook or you can change its type to text now for better visibility.
In the following example, we have changed the timestamp format for both the “serverTimestamp” and “persistenceTimestamp” columns.
- Continue to go through the table and look for columns, such as target, device, details, account, subscription, acknowledgement status, and fingerprint that show "Record" as their entries. You can expand them the same way as we explained above.
Note: For these columns, we recommend checking the 'Use original column name as a prefix' option to enhance visibility and comprehension.
Note: There might be other columns that need to be expanded, depending on the events or possible security event improvements. If you find "Record" entries inside the expanded entries, repeat the same procedure for them.
There may be many empty or null cells due to the different sources and structures of security events. This occurrence is not uncommon; in fact, it frequently happens when integrating unstructured data into a table. - After you finish expanding the columns, select the Home tab, and then select Close & load at the top-left corner. This loads the data that you have transformed into a new list in an open Excel document.
Below you can see a table full of imported security events.
Troubleshooting
If you notice that you have missed certain columns, you can easily edit the Power Query transformation by selecting the relevant query from the right-side panel.
Alternatively, in the Query tab, you can select Edit.
A dialog box opens in which you can add the missing items or remove unnecessary steps.
