In Power BI, you can work with the downloaded JSON file produced by the Learn365 audit trail feature. Data can be interpreted and filtered for a specific purpose, for example, to find out which users were assigned as catalog admins over a specified date range, or why a certain user hasn't been awarded a skill after completing a course.
This article describes the steps you need to perform to do this, and how to filter and present the final result in a table view. The description is based on a scenario where you want to retrieve all recorded events that relate to a specific user for a specific date.
To view audit trail data provided by a JSON file in Power BI, follow these steps:
1. In Power BI, import the downloaded audit trail file. This is achieved by selecting Get data from another source on the main screen.
The Get Data window opens, where you need to select JSON and then Connect to upload to Power BI the downloaded JSON file that contains the audit trail records.
2. When the JSON file is imported, the Record Tools window opens in Power BI with the selected audit trail records file.
Select List to expand the records. When the list of records is expanded, the name of the window changes to List Tools.
3. Select To Table on the left to convert the data of the imported file to a table view. The To Table window opens, where you need to select OK to proceed.
4. Select the filter icon of the ABC Column1 to configure what data you want to be included and displayed in the table. By default, all checkboxes are preselected. You can use Select All Columns to clear the selection for all checkboxes or manually clear the relevant ones.
After all the relevant checkboxes are selected, confirm with Ok to proceed.
In our case, as we want to get all events related to a specific user at a specific date, we clear all checkboxes to then select only the relevant ones: course, enrollment, initiatedBy, timestamp, type, user. All these selected properties will become columns.
NOTE
The type property shows the data type which is the same as event, for example UserEnrolled. Find more information about the Learn365 events here.
5. After selection for the ABC Column1 is done, the view changes. Now you will see columns according to the selection made.
Each column presents a property with its own properties inside which are configured the same way as it was done for the ABC Column1 column: use the filter icon of the column to select what data inside this column is to be displayed. The column you are currently working with is marked grey.
Select what properties from the column you want to include in the table and confirm your selection with OK.
For our example, for the course column we select courseType and title.
After selecting OK, new columns are displayed. Their titles 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 all the columns to select the data you want to be displayed in the table. Skip any columns that aren't relevant for your purposes. Rename the columns if needed. You can right-click a column you don't need and select Remove to remove it.
On the left, under APPLIED STEPS, you can view the list of all your actions performed and can cancel them via a cross icon next to the relevant action.
In our case, when configuring the user column, we use filtering to select the specific user we need.
7. Once all the columns are configured, filtered and renamed (if needed), you are ready to convert the results to table view. To do this, select Close & Apply on the left.
8. To see the results, go to the Data view tab, where you can additionally filter the displayed results using columns' filters.
Comments
Article is closed for comments.