Generate a Usage Report for All of Your Okta Apps

Okta's built-in Application Usage report generator produces a CSV export of application login events from the system log. You select the start and end dates of the desired reporting period, and the application on which to report. The dropdown also includes an "All" option, so you can get login data for all apps in your org. You can also limit your report to a specific user or group.

As an Okta administrator, one of my primary uses for this feature is to view overall activity - which apps are used the most and which apps are used the least. And that's where this feature lets me down. Since the source for the data is the system log, only apps that were actually in use during the reporting period will be included in the results. If a particular app was not used during that window, there will be no login events for it in the system log, therefore that app will not appear anywhere in the results. Knowing which apps are no longer in use is very important to the business. After all, who wants to keep paying for an app or service if no one is using it?

In this article, I'll show you how to produce a usage report that includes all of the apps in your Okta org, including those that had no usage during the desired reporting period. And we'll do this using nothing but the Application Usage report in Okta, the rockstar browser add-in by Gabriel Sroka, and a bit of powershell. Finally, we'll pull the data into Excel to clean it up and make it pretty.

Step 1: Request an Application Usage report from Okta

Application Usage is not an interactive feature, you have to submit a request to Okta and then wait for them to send you a link to the results, which you will then download to your computer. This can take a significant amount of time, so it's best to put in your request first. For instance, the last time I requested one month's worth of data for all apps, it took Okta close to an hour to send me a link to the results, which ended up being almost 300MB, containing close to 2.5 million records. So, get your request submitted right up front, then move on to the next step.

Step 2: Export a CSV of All Apps in Your Okta Org

The easiest way to get a list of all apps in your org is to use the rockstar browser add-in. This is a small Chrome add-in that greatly enhances your Okta admin experience, providing you with data retrieval capabilities that should have been built into the Okta admin console. More  info is available here: https://gabrielsroka.github.io/rockstar/.

With rockstar loaded, go to your Applications page, then select Export Apps (custom) from the rockstar menu.

Next, in the Columns to export section, select Name, Label, Status, and Sign On Mode. You can include other fields if you wish, but these are the four we'll be using to produce our report.

Finally, click the Export button to have rockstar retrieve the data from your Okta org and automatically save it to your Downloads folder, with the name "Export Apps", followed by the date and time (example: "Export Apps 2023-05-03 14-44-46.csv").

For easier reference later, rename this file to "AppList.csv" and copy/move it to your c:\temp folder.

Step 3: Download Your Application Usage Report

Assuming that, by now, Okta has emailed you the link to the results of the request you submitted in Step 1, click the link and download the report. The filename will be "ApplicationUsageUnaggregatedReport_" followed by a random string of characters, ending with ".csv".

For easier reference later, rename this file to "AppUsage.csv" and copy/move it to your c:\temp folder.

Step 4: PowerShell - Import Your List of Apps

Open a powershell window and make c:\temp your current directory. We will use this powershell session throughout the rest of these steps. If you close this window and end your session, you'll have to start over at this step again.

Import the contents of your AppList.csv file to the variable $allapps. In this example, I'm excluding our active_directory "apps" and any apps whose current status is not "Active".

$allapps = import-csv '.\AppList.csv' | where-object {$_.name -notlike "active_directory" -and $_.status -like "Active"}

It's important to note that the Name field in AppList.csv contains the Okta-generated app name, while the Label field contains the name that you gave your app (and what users see on their dashboard).

At this point, $allapps is an array containing all of the apps in your Okta org, of all types, including bookmarks.

Step 5: PowerShell - Import Your App Usage Data

Import the contents of  your AppUsage.csv file to the variable $usagerpt.

$usagerpt = import-csv '.\AppUsage.csv'

At this point, $usagerpt contains every login record for every user and every app in your org (from the requested time window). We don't really care about every detail, though. Instead, we want to get a count of how often each app was used, so we're going to group the records by app name. The relevant field name in the AppUsage.csv file is named "Instance name".

We're going to pipe the contents of $usagerpt to the Group-Object command, based on the "Instance name" property, then select the Name and Count fields in the results and store that in the $usage variable. 

$usage = $usagerpt | Group-Object -Property "Instance name" | select name, count

At this point, $usage is an array containing every app that was used during the desired time window, along with a count of how many times each app was used. Now all we have to do is merge the contents of $allapps with the contents of $usage to produce an array containing every app in your Okta org, along with a count of how many times each was used during the desired time window.

Step 6: Merge the List of Apps and the Usage Data

The following command steps through each record in $allapps, then looks for a Name with a matching Label in $usage. The result of each iteration is written to the output stream, and includes the Label, Count, and Sign On Mode.

$rpt = Foreach ($app in $allapps){$appcount=($usage | Where-Object {$_.name -like $app.label}).count;Write-Output "$($app.label),$appcount,$($app."sign on mode")"}

Lastly, we'll dump the contents of $rpt to a file.

$rpt | out-file c:\temp\appusage.txt

Now, you can import this file into Excel, give it meaningful column names and sort the data as needed to produce a nice report. Here's a sample.

Epilogue

This is one of those quick-and-dirty solutions to a report that my management has been requesting for years. There's lots of room for improvement here, so this is just a starting point. My initial thought was to use Power BI Desktop to produce a more automated solution, but I don't know that product well at all and this powershell solution didn't take nearly as much time or effort, so that's what I went with. Hopefully you will find it useful.

Mike

Delete Inactive Accounts From Your Okta Org

Until a few months ago (as of this writing), I had an Automation Workflow in Okta that would delete Okta-mastered accounts that had not logged in for 2 years. That may sound like a long time to leave inactive accounts laying around, but we maintain accounts for former employees so they can get into Workday to retrieve their tax documents. Many of them will only sign in one time each year, but if an account goes unused for two years, they should be safe to delete without inconveniencing anyone.

Unfortunately, Okta recently changed the behavior of the Delete action in the automation workflow. Instead of setting the account status to DELETED, it deactivates them, turning the status to DEPROVISIONED, and the workflow is limited to just one Delete action so there's no way to actually delete the deactivated accounts in the workflow. To make matters worse, I have a powershell script that reactivates all deactivated accounts, so after the workflow deactivated the accounts, the powershell script turned around and reactivated them, even going so far as to sending activation emails to the accounts with legitimate email addresses. And THAT prompted calls and emails to the HR department, demanding to know why we continued to send them unsolicited emails. Needless to say, I deactivated that workflow as soon as I was made aware of the problem. I was quite busy at the time and so I didn't get around to finding another solution, until today. Well, yesterday, actually.

The solution has two parts. The first part involves the automation workflow. Instead of setting the account status to DELETED (DEPROVISIONED, actually), I modified it to set the status to SUSPENDED. In 5-6 years, I've used that status very rarely, so this seemed to be a great way to allow the workflow to identify dormant accounts and get them into some sort of container that I could then - and this is the second part - modify a copy of my reactivation powershell script to query Okta for all the users in the SUSPENDED status, and then delete them with an API call (two API calls, actually, since the first merely deactivates them, just as in the workflow).

Part 1: The Automation Workflow

The reactivation powershell script not only reactivates the accounts of former employees, but it also puts them into a special Okta group that assigns them to the Workday integration. The Automation Workflow also uses that Okta group to limit itself to just the former employees. The workflow is set to run at 5pm every day and look for any user that has been inactive for 730 days. If any former employee accounts meets that criteria, the account status is changed to SUSPENDED.

Part 2: The PowerShell Script

The powershell script will use the Okta API to query our org and return all accounts with a SUSPENDED status. It then loops through that array of accounts and makes two API calls to delete that account. As the script loops, information about each account is logged to a file, and when all the accounts have been processed, the script emails the log file to the designated email address. This gives us a record of which accounts were deleted, and when.

<#
.SYNOPSIS
    Okta_Purge_Inactive_Former_Associates.ps1 - Deletes suspended Okta accounts for former associates
    Created by Mike Koch on December 16, 2020
.DESCRIPTION
    Queries Okta for all SUSPENDED accounts, then deletes them
    Send email with all logged events/actions
.NOTES
    TO DO
    1. 
#>
[CmdletBinding()]
Param()

$api_token = "put_your_org_token_here"
$uri = "https://yourcompany.okta.com/api/v1/users?filter=status%20eq%20%22SUSPENDED%22"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

$allusers = @()
$logfile = "c:\temp\Okta-Purge.log"
if (Test-Path -Path $logfile) {
    Remove-Item -Path $logfile -Force
}

function LogWrite {
    param ([string]$logstring)
    Add-Content $logfile -Value $logstring
}

# Query Okta using the URI specified above, page through the results to get all matching accounts
Do {
    $webrequest = Invoke-WebRequest -Headers @{"Authorization" = "SSWS $api_token"} -Method GET -Uri $uri
    $link = $webrequest.Headers.Link.Split("<").Split(">")
    $uri = $link[3]
    $json = $webrequest | ConvertFrom-Json
    $allusers += $json
} while ($webrequest.Headers.Link.EndsWith('rel="next"'))

if ($allusers.count -gt 0) {
    foreach ($usr in $allusers) {
        LogWrite "Deleting suspended user: $($usr.profile.login), $($usr.profile.displayname)"
        Write-Output "Deleting suspended user: $($usr.profile.login), $($usr.profile.displayname)"
# the first DELETE only DEACTIVATEs the account
        Invoke-WebRequest -Headers @{"Authorization" = "SSWS $api_token"} -Method Delete -Uri "https://yourcompany.okta.com/api/v1/users/$($usr.id)"
# the second DELETE actually DELETEs the account
        Invoke-WebRequest -Headers @{"Authorization" = "SSWS $api_token"} -Method Delete -Uri "https://yourcompany.okta.com/api/v1/users/$($usr.id)"
    }
    $MailMessage = @{
        To         = "SomeoneWhoCares@youremaildomain.com"
        From       = "OktaMaintenanceBot@youremaildomain.com"
        Subject    = "Report: Okta Former Employee Account Deletions"
        Body       = Get-Content $logfile -Raw
        BodyAsHtml = $false
        SmtpServer = "your.smtp.server"
    }
    Send-MailMessage @MailMessage
}

It's been a couple of months since the automation workflow broke. I opened a case with Okta and they acknowledged that there had been an unintended behavior change. The issue was escalated to the developers and the support case was closed. I'm sure they'll fix it eventually, but as a global retailer with thousands of employees, we have a lot of turnover so I need to keep up with the dormant account deletions. If for no other reason than to ensure that we have accurate counts the next time our contract is up for renewal. Even dormant accounts cost money.

Today's initial run of SUSPENDED users came to well over 14000. That got us caught up from the last couple of months, so subsequent runs should be much more reasonable, and finish much more quickly.

Removing AD-Mastered Users From an Okta Group

When associates leave our company, their Active Directory accounts are automatically disabled, which in turn causes their Okta account to be deactivated. These former associates still need access to Workday, to get their paystubs and to retrieve their W-2 tax documents the following year. To facilitate access to Workday, their Okta accounts are reactivated (which turns them into Okta-mastered accounts) and added to a special Okta group that assigns them to the Workday integration.

Being a retailer, we hire a lot of temporary associates, particularly around the holidays, and quite a few of those are rehires from the previous season. And when a former associate is rehired, although a new AD account is created (because the old one has been deleted by this time), the new account usually has the same username and Workday number that they had previously. This is no big deal, but I recently discovered that most of these new AD accounts, once imported into Okta, were being automatically relinked to their old Okta accounts, the ones that were supposedly now Okta-mastered. And that's also not a big deal, since it requires no admin intervention due to name conflicts. The one negative is that these relinked accounts remain members of that special Okta group that assigns them to Workday. This doesn't cause a problem for the user, but since they also have an AD group membership that assigns them to Workday, membership in the Okta group is redundant. And it just annoys me, so I decided to write a script to remove them from the Okta group, since that's supposed to be for former associates only.

The following powershell script retrieves the entire list of users from the Okta group, then filters that list down to only the user profiles that have Active Directory as their credential provider. Those users are then deleted from the Okta group.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$api_token = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$groupid = "xxxxxxxxxxxxxxxxxxxx"
$uri = "https://YOURORG.okta.com/api/v1/groups/$groupid/users?limit=1000"
Do {
    $webrequest = Invoke-WebRequest -Headers @{"Authorization" = "SSWS $api_token"} -Method GET -Uri $uri
    $link = $webrequest.Headers.Link.Split("<").Split(">")
    $uri = $link[3]
    $psobjects = $webrequest | ConvertFrom-Json
    $alum += $psobjects
} while ($webrequest.Headers.Link.EndsWith('rel="next"'))
$alumAD = @($alum | where-object {$_.credentials.provider.type -like "ACTIVE_DIRECTORY"})
if ($alumAD.count -gt 0) {
    foreach ($user in $alumAD) {
        $uri = "https://YOURORG.okta.com/api/v1/groups/$groupid/users/$($user.id)"
        $deleterequest = Invoke-WebRequest -Headers @{"Authorization" = "SSWS $api_token"} -Method DELETE -Uri $uri
    }
}