Using Excel, you can analyze data from a downloaded LMS365 audit trail file. The data is initially produced in JSON format but viewing it in Excel presents it in an easy-to-read format.
The audit trail feature enables you to identify which user completed a specific process on a specific date. For example, you can use the audit trail to find admins who have ever been assigned to a specific catalog, or to investigate why some users haven't been awarded a certificate after completing a specific course.
This article describes how to do this, and how to filter and present the information in a table. The steps will show how to retrieve all recorded events related to a specific user for a specific date across multiple catalogs.
To convert the downloaded audit trail data to an Excel table, follow these steps:
1. In Excel, import the downloaded audit trail file into Excel by going to the Data tab > Get Data > From File > From JSON. Navigate to and select the downloaded JSON audit trail file and select Import.
When the file is imported, the Record Tools window opens in Excel.
2. Select List to expand the data.
When the list is expanded, the window name changes to List Tools.
3. To convert the imported data to a table view, select To Table on the left-hand side. The To Table window opens, where you should select OK to proceed.
4. Select the filter icon in the Column1 column heading to configure the data you want to display in the table. All filter checkboxes are selected by default. You can use Select All Columns to clear the selection of all checkboxes or manually clear the relevant ones.
In this example, we want to see all events that relate to a specific user on a specific date across multiple catalogs, so we'll clear all checkboxes, then select only the relevant ones: course, courseCatalog, enrollment, initiatedBy, timestamp, type, and user.
The type property shows the data type, which is the same as the event name, for example UserEnrolled. Find more information about the LMS365 events that are recorded in the audit trail in this article.
With the relevant checkboxes selected, select OK to proceed. All the selected filters will become columns in Excel.
The selected filter items are displayed as columns in Excel.
If a column displays Record, it means that the column filters haven't yet been defined.
Each column represents a property, which in turn has its own set of filters (or sub-filters) that enable you to determine the specific data fields you want to display. Sub-filters are configured in the same way as the ABC Column1 column: use the filter icon of the relevant column to select the data inside this column you want to display. The column you're currently working with is marked green.
Select the properties from the column you want to include in the table and confirm your selection with OK.
For our example, we select the following sub-filters: courseType, isPublished, and title.
The columns are added to the worksheet once you select OK. The column headings inherit the title of the parent column followed by the selected property, for example courseType selected from the ABC Column1.course column is displayed as a column called ABC Column1.course.courseType.
Columns can be renamed by double-clicking their title.
6. Configure each column to show only the relevant information in the table. Skip the columns whose properties are irrelevant for your purpose. Rename the columns if needed. To remove any unwanted columns, right-click the column heading and select Remove.
On the right-hand side, under APPLIED STEPS, you can see a list of all the actions you've performed. You can easily cancel an action by selecting the cross icon next to the relevant action.
Once all columns are configured, filtered, and renamed (if needed), you are ready to convert the results to an Excel table.
7. To do this, select Close & Load on the left-hand side.
The results are presented in a table view, and can be filtered and used as an Excel worksheet.