Introduction

This article is to help expand on topics of integration with Oracle’s Cloud Identity Management service called Identity Cloud Services. Identity Cloud Services delivers core essentials around identity and access management through a multi-tenant Cloud platform.  As part of the Identity Cloud Services framework, audit events are captured for all significant events, changes, and actions, which are sent to a database table.  I previously wrote an article to understand more about how to get the Identity Cloud Services Audit Events, but in this article I am going to expand on how to leverage the Identity Cloud Services Audit Events and create some nice reports using a tool called Visual Analyzer (VA) http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/bics/va/va.html - section1, which is part of Oracle’s BICS (Business Intelligence Cloud Services).  With VA anyone can quickly create useful audit reports in a visual method for a variety reasons.

Overview of Oracle Visual Analyzer

Visual Analyzer is a web-based tool that comes with BICS that provides a way to explore and analyze data visually.  It provides self-service analysis versus the more robust grand daddy BICS data modeling, which provides a way to organize and secure data in a complex business form — more on that here http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/bics/DataModeler/bimodeler.html.   However, in this article I want to cover something much simpler where a Business Analysis or Info Security person can create some quick reports using the Identity Cloud Services audit event data and VA.   The graphic below shows a couple simple reports created in minutes using Identity Cloud Services audit event data.  The pie chart on the left shows a break out in percentage of user usage of various types of web browsers and their versions. The right pie chart shows the percentage in population of the two user accounts in my test system.  Because Identity Cloud Services provides a large schema audit events the possibilities of using the data with VA are many!

First Extract Identity Cloud Services Audit Data into BICS

Before we can create interesting reports using VA we must first extract the audit data from Identity Cloud Services.   Since Identity Cloud Services is a cloud service and relies on its powerful REST APIs, we can narrow down how we can get audit data from Identity Cloud Services by what is offered by way of BICS to integrate with Identity Cloud Services.   So the two best options to get data from Identity Cloud Services into BICS for the purposes of VA are the following.

• OPTION 1 - BICS PL/SQL Procedure calling APEX Web Service that calls Identity Cloud Services REST API
  This processes uses a PL/SQL procedure that calls an APEX REST Web Service to the Identity Cloud Services REST API, which gets a response in JSON (JavaScript Object Notation) and parses the data to transform and insert it into BICS database table.  Once the data is in a BICS table a person who works with BICS can create some data models and finally expose the IDCS audit data as a data source in a BICS catalog for VA to consume.
• OPTION 2 -  Use Postman with Online JSON2XSLX Excel conversion Import to BICS
  Postman is a very useful REST Client tool used for testing and development that allows you to send REST requests to the Identity Cloud Services REST API.  The response in Postman is a JSON output that can be saved as a file. Using Postman you can easily provide filters so that your search is for specific audit data and that helps save time and reduces the amount of data you need to work with.  The goal in this approach is to use the JSON output file, then convert it to an Excel file using an online tool, and finally import the Excel file into BICS as a Data Set to be used by VA.

Using Option 2 to Import Data into BICS

Option 1 mentioned in the previous section is a great way to run extractions of audit data on a daily, weekly, monthly, basis, but for just one off simple audit reports option 2 is a great way to go.  So I am going to explain option 2 for the purposes of keeping things simple for this article and another way your Business Analysis, Info Security, or any other interested person can run one off reports with the latest data from Identity Cloud Services.  I am going to start by presenting you to a great tool called Postman.  Postman is a great REST Client Tool that can be used to send REST requests over HTTP to an API like Identity Cloud Services REST API.

To keep things simple I want to illustrate a simple use case where we will create some SSO Audit Reports from successful and failed login and logout audit records.  The audit record itself has several attributes per the Identity Cloud Services schema, but there is one attribute of interest in our case named “eventId”.  When a login or logout event happens in Identity Cloud Services, an audit record is created and has an attribute eventId that will hold a value that starts with “sso”; i.e. sso.session.create.success, sso.authentication.failure, etc. We can leverage this data to create our reports in VA.  The following are steps to start by getting audit data from Identity Cloud Services to importing that data into BICS and using VA to create the report.

STEP 1 – Use Postman to send a REST search to extract SSO events

As a FYI, this article is not going to provide a step-by-step to using Postman as there are other articles on that.  What I am going to do instead is focus on providing some example Identity Cloud Services parameters tailored for our use case in order to extract successful or failed login and logout events. The following endpoint and filters is what we will use to extract login and logout events from Identity Cloud Services.

The above value “/admin/v1/AuditEvents” is the Identity Cloud Services REST endpoint used to query audit records.  Then “filter” with optional parameters are what can be used to query specific records.  The following table breaks out each parameter, its value, and an explanation of what they are doing.

Using Postman enter the endpoint and filter to request the JSON response to return SSO events.  Below is a Postman screenshot to help illustrate what you should see when you configure the same filter with the above parameters.  You can see the data returned is a JSON response, which in the next step I will show how to save that JSON response to a file.

STEP 2 – Save the JSON response

In Postman send the same REST request from the previous step, but this time click the drop arrow next to the Send button and an option “Send and Download” should appear.   Select Send and Download and this time you will get an option to save the response to a file --- use the file name “idcs_sso_auditevents.json”.

STEP 3 – Convert the JSON tile to an Excel XslX file.

Using an Internet web browser go to http://www.json-xls.com/json2xls, and follow the follow sub steps.

3.1 – Select the tab named File

3.2 – Click the Choose File button and select the JSON file created in the previous step.

3.3 – Select the Destination Format XlsX (Unless you have an older version of Excel).

3.4 – Select the Layout Auto

3.5 – Check “I’m not a robot” which will make you verify you are not a robot.

3.6 – Finally click the Both button. A file should save as data.xslx

STEP 4 – Remove unnecessary worksheets from the Excel XslX file.

Before we import the Excel file into BICS as a Data Set all the worksheets except for “Resources” need to be removed.  In the previous step when the option Both was used selecting from Select Rendering View & Submit, it read the schema of the JSON and learned that it needed to break out the JSON file into worksheets.  If any other option was chosen like Plain or Hierarchy importing the Excel file into BICS would have caused complaints that the Excel file is not valid.  By selecting Both, json-xls.com does a good job of separating JSON file to its respective Excel worksheets based on how it reads in the schema and then puts all the necessary data we need on the worksheet Resources.  This saves us a lot of time and provides better data to use when creating an audit report in VA.  To complete this step, delete each worksheet except for Resources by right clicking on each worksheet and then select the delete option.

STEP 5 – Save As the Excel file to a new file

Once all the worksheets except for Resources are removed it should look similar to the following screenshot.  Now Save As the Excel file to a new Excel file called idcs_sso_auditevents.xlsx.

STEP 6 – Login to BICS and then click on the VA Projects icon.

STEP 7 –  In the VA Projects click the Create Project button.

STEP 8 –  Now click the Create New Data Source button.

STEP 9 –  From the Create New Data Source window click From a File.

STEP 10 –  From the File Selector pick the idcs_sso_events.xlsx created earlier.

STEP 11 –  Change the “id” column from attribute to Measure and Count Distinct.

This allows you to provide calculations to reports that I will cover later.

STEP 10 –  Click the Add to Project button to complete the upload.

STEP 11 –  Click the Save project icon in the menu bar, which is the square with a down arrow. Give the Project a name “My IDCS Audit Reports”.

Creating Sample Visual Analysis Reports

The two example reports we are going to create will use Identity Cloud Services schema attributes listed in the following table.  Just keep in mind as you expand beyond this article to think of other reports you want to create there are many more Identity Cloud Services schema attributes not only for audit events, but many others.

Report 1: Single Sign-On Count Pie Chart

This report is pretty simple and shows the count of SSO login successes, login failures, and logout successes.  The key to knowing what reports you can create is understanding the meaning of some of the Identity Cloud Services SCIM schema attributes.  From the previous section we left off with importing the data into BICS, now we continue to use that data to create a SSO Count Pie Chart.

STEP 1 –  If not already login to BICS and go to the VA Projects, then click on the My IDCS Audit Reports project.

STEP 2 –  Expand the idcs_sso_events data on the left and drag the eventId data element to the large pane on the right, it should automatically put the eventId in the Rows section.

STEP 3 – Now drag the id data element into the Values (Slice) pane. It should now look like the graphic below and immediately we see some useful counts of login and logout successes and failures.

STEP 4 –  Now to turn this data into a Pie Chart change the Pivot option from the middle column to Pie.  The Pie Chart becomes a mono chromatic blue.

STEP 5 –  Now let’s color the Pie Chart by dragging the eventId from our data set list to the Color option in the center column.  There –– easy as Pie!  Don’t worry, the puns are free.

STEP 6 –  Now all we need to do is Save our project by clicking on the icon in the menu that looks like a square with a down arrow so we can continue to use this project to build another report.

This completes our first report.  Hopefully this gives an idea of what is possible to do with Identity Cloud Services audit data and using Oracle Visual Analyzer.  I want to emphasize it is important to understand the Identity Cloud Services Schema attributes, the values in the data, and what it means, which allows us to quickly generate some pretty useful data.

Report 2: SSO Events by Username Pivot Table

This report we are going to create is a Pivot Table that lists all users and the count of their SSO event.  For example how many times did user tim.melander login successfully, login failure, logout successfully?

STEP 1 –  If not already login to BICS and go to the VA Projects, then click on the My IDCS Audit Reports to open the project.

STEP 2 –  Start by creating a new Visualization by clicking the top right menu icon Add Visualization.  Then make sure the Visualization is changed to Pivot.

STEP 3 –  Let’s build the Pivot table by first dragging the actorName into the Visualization and dropping it into the Rows.

STEP 4 –  Now drag the eventId into the Columns section.

STEP 5 –  Now drag the id first into the Values section, and grab the id again and drag a second one on to the Columnssection.  Your Visualization should now look like the following.

STEP 6 –  Now Save the project by clicking on the Save Project icon.  Then to see more of the new Pivot Chart click the Maximize button with the double arrow in the top right corner of the Pivot Chart.

From the graphic above you can now see that this Pivot Table provides some useful data on how many successful logins, failed logins, and successful logouts are counted for each User.

Summary

We learned to extract specific audit data from Identity Cloud Services using the Identity Cloud Services CLI tool, which makes it pretty simple.  Then we imported that data into BICS to use it in Visual Analyzer to quickly create a couple reports.  I hope this helps provides a start to understanding how easy and powerful both Identity Cloud Services and BICS Visual Analyzer are when used together to create useful reports.  This should give a starting point, which hopefully will push you to go even further on expanding data from Identity Cloud Services and use VA to create some amazing reports.