Inactive Application Account Reporting with Okta Workflows

I was recently asked about reporting, and possibly recertification, of inactive accounts in Okta. We can run reports in Okta on Okta profile states to find inactive users. We also have an Okta Workflows template to find and report on Okta users who haven’t accessed Okta in a period of time.

But what about application account access? There is no out-of-the-box reporting. But, as always, our Swiss Army Knife Okta Workflows can be used to report on users who haven’t accessed applications via the Okta Dashboard in a period of time. The following article explores how this was implemented.

Article contents:

Overview of the Scenario

With Okta access itself, Okta stores the last login timestamp on the user profile, so it can be easily accessed. But with the application access, the only place the last login information is stored is in the Okta System Log against a User single sign on to app event. So we need to search the system logs. This can be a resource intensive operation if done badly, so the flows were designed to minimise the number of searches of the system log and leverage objects and a Workflows table for more efficient processing.

The flows in the solution are shown below:

There are two parts to the solution:

A set of flows will perform a single search of all SSO authentication events in the system log over a given period (say 30days before today). The results are streamed to a subflow that will process each event and store them in a Workflows table for logins. This could be run daily as a middle-of-the-night batch job.
A flow for each application that you want to identify inactive accounts. The main flow will identify each user in that application, then a subflow will check each user against the logins table and if there are no entries found, that user is stored in an application-specific inactive account table. Once all users have been processed and all inactive accounts stored in the table, another sub-flow will export the table to a google drive, add an application owner to the file on the drive and send an email about the report.

The solution includes some common re-usable components. For example, some of the variables used are stored in a separate table (rather than coding them into the flows) and a standard subflow is used to retrieve them.

The remainder of this article will detail the flows.

The first part of the solution involves finding all login events for a given period (such as 30 days) and storing them in a table. Note that Okta only stores events in the System Log for about 90 days. You may also have a large volume of login events that may take time to extract and store.

There are two flows and table used (as well as some utility flows).

The main flow is M30 – Get All App Logins for Period. It uses M31 – Process Individual SSO for each returned event. All login events are stored in a table T3 – All Recent Logins. These are explored in the next sections.

The flow is as shown below:

There are four steps in the flow:

Empty out the table that will store the login events
Get the current date/time
Take 30 days off the current date/time to provide the start window for logins (i.e. if an account hasn’t logged in during the past 30 days, we will consider it inactive)
Search the Okta System Log for app login events and process each.

The first three cards are trivial, but the last requires some explanation. There is an [Okta] Search System Logs action for the Okta connector. There are multiple way you can configure a search – in this case we are specifying three arguments (Event Type, and Since and Until dates). The Event Type is hardcoded to the user.authentication.sso event type. The dates use the today-30 and today dates from the earlier date cards.

As with many of the search cards, you can return a single record, a set of records up to a maximum (200 in this case) or stream the results. Given the volume of login events possible, we are streaming the results and passing each one to a second (helper) flow to write them to the table.

This helper flow is run for each event returned from the search.

There are three stages to this flow:

The flow is initiated for each login event and that event is passed as an object into the flow
The next four cards will extract the user (actor), target system and login date/time from that object. As the event object is complex, there are multiple cards to extract different attributes.
The last card will write a record to the table

Even though the main flow may be extracting thousands of events, many of the sub flows will run in parallel to write events to the table to reduce overall processing time.

All Recent Logins Table

This table will store all recent login events (i.e. over the last 30 days).

It’s storing six attributes from each login event:

ActorId – the Okta ID for the user profile
ActorName – the fullname (display name) of the user
ActorEmail – the email address of the user
Target1Id – the Okta ID for the application being SSOd to
Target1Name – the display name of the application
LoginDateTime – the event datetime

There will be an event for every login, so there could be duplicates (different datetime) for a user accessing a specific application. We only really need to know if a user has logged into a specific application during this period, not every login. So the M31 flow (above) could have some smarts included to check if there is already a user-application event there before writing on. This would significantly reduce the amount of events in the table.

The application-specific processing (next section) will leverage the contents of this table.

Part 2 – Find Inactive Accounts for an Application and Report on Them

In the second part of the solution we leverage the login table from above to check whether users belonging to an application have logged in over the period (30 days in this case). The following example is for one application, but could easily be duplicated for other applications (the application id, inactive user table and app owner email are parameterised, so the main flows can easily be replicated to use the different parameters).

There are three main flows:

The M40 – Get O365 Users and Check Login flow will find all users assigned to the Office365 application and pass them to the next flow (U00 – Check User in Login Table) for checking if the individual user logged in and if not write them to an inactive users table for the app. Then the last flow (U10 – Send Report via Email) will export that App Inactive Users table and distribute it to the application owner.

These are explored in the following sections.

Main Flow to Get Users Assigned to the Application

This flow would be run as required or on a schedule.

The first few cards will get the App Inactive Users table id (parameter stored in another table), clear that table, get the application id (another parameter) and use the [Okta] List Users Assigned to Application card to find all application users.

For each user assigned to the app, it will use a subflow (U00) to check if they have logged in and if not, write them to the App Inactive Users table. When all users are processed, it uses another subflow (U10) to distribute the App Inactive Users table as a report via email.

Sub Flow to Check if a User Has Logged In

With the list of users assigned to the app, we need to check if they’ve logged in over the last period (30 days). This helper flow is called for each user assigned to the application.

The flow has the following steps:

The user object is passed into the flow
Some of the user attributes are extracted from the user object
The All Recent Logins table is searched for this user and application. The card has a set of where clauses that result in the where expression ‘”ActorId” = System Properties.User ID AND “TargetId” = appid‘ (the userid comes from the Get Multiple card from the user object and the appid is passed in to this flow from the calling flow).
The length of the resulting list (i.e. number of matched login events) is calculated
If the length (i.e. number of matching records) is >0 then the flow exits (the user has logged in during the last 30 days)
Otherwise, this user is written to the App Inactive Users table

This sub flow will progressively fill the App Inactive Users table.

Sub Flow to Send the Report via Email

The last flow that is called is a flow to download the App Inactive Users table, store it as a CSV on a Google drive, assign ownership to the application owner, and email them with details. This is a common flow I use across many reporting workflows.

The first card is the Child Flow card which defines the parameters being passed to the flow. In this case it includes the recipient email, tableId for the table to export, and some report definitions.

The two time cards will get the current date and time and covert it into a format to use in the filename. The report filename is built up of a prefix passed to the flow and the datetime.

The next card exports the Workflows table to a CSV file (using the filename set in the previous card). The Compose card creates a string for the file description. The [Google Drive] Upload File card uploads the file, with description, to Google drive (based on the connector settings).

The next Compose card will format a permission message. The [Google Drive] Create Permission card assigns the new file to the email address of the recipient (this will result in an email being sent to the recipient), like the one below.

The next Compose card creates a HTML-formatted email body. Finally the [Office 365 Mail] Send Email card will send a second email to the recipient with more information. For example:

Thus we have report delivery for action by the application owner.

Application Inactive Users Table

The App Inactive Users Table is a simple list of users who haven’t logged into the application in the given period.

This is what is distributed to the application owner as a CSV file.

Conclusion

In this article I have shown how Okta Workflows can be used to produce inactive user reports. There is an out-of-the-box template for Okta users accessing Okta. But to produce a report on application access requires searching the Okta System Log. Whilst there are multiple ways to access the log, you need to consider performance and this example has shown one way to do so.

This concludes the article.

Sample Code

The workflows example described above can be downloaded from https://github.com/iamse-blog/workflows-templates/tree/main/dae-iga06-InactiveUsers

Inactive Application Account Reporting with Okta Workflows