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.
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
