Adi's Shower

 

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

Dynamic Distribution Group Changes Coming in 2022

According to a recent article by Tony Redmond, Microsoft is planning to modify the behavior of dynamic distribution groups in Exchange Online next year. The rollout is scheduled to begin in January and finish worldwide by the end of March.

The change is to when the members of dynamic distribution groups are calculated. Currently, dynamic distribution group queries are run every time someone sends a message to the group. While pre-canned queries are quick and cheap to resolve (performance-wise), queries that utilize custom filters can grow to be very complex and their resolution can have a performance impact on the Exchange transport service, introducing delivery delays. By "pre-resolving" dynamic distribution group queries once a day, the performance impact will be negligible and the reliability of the service will be improved.

So what's the downside? Well, by only calculating the membership once a day, your dynamic distribution groups will be a little less dynamic than before. But really, how often does membership in your dynamic distribution groups actually change throughout a single day? At my company, directory changes do occur almost hourly, but the vast majority of those changes occur to newly-created accounts, usually days or even weeks before the employee's first day of work. And even if a a relevant change occurs today, it's going to be less than one day for the associated dynamic distribution group member to be recalculated.

Currently, when you create a new dynamic distribution group or update the filter of an existing group, Exchange Online calculates the membership immediately. However, after this change goes into effect, it will take up to two hours for Exchange Online to calculate the membership of a new group or an existing group whose filters have been updated. This behavior will then be similar to how dynamic groups work in Azure AD.

There's no official word on whether this change will allow Outlook clients to view or expand dynamic distribution group memberships in real time (as they currently do with legacy distribution groups), but that would be a welcome feature, eliminating that "are they or are they not included?" question that I often get from our various executive assistants and members of the communications team.

Quick Hit: Subscribing Existing Members of a Microsoft 365 Group

Took me quite a while to find this solution so I'm just putting it out here so i can find it if I need it again.

The Problem: I re-purposed an O365 group in my Azure AD console, one that was already configured with the dynamic query I needed. Unfortunately, I didn't realize that, by default, members of O365 groups don't receive a copy of emails sent to the group in their personal mailbox. The messages just go to the group mailbox.

After doing some research, I reconfigured the O365 group to automatically "subscribe" new members, but that doesn't change the subscription status of the existing members. A lot of the "solutions" I found on the internet involved enabling the "Subscribe new members" flag and then removing all existing members and then adding them back to the group so that they would be subscribed. While that method probably works, it's not very elegant. There must be another way to accomplish my goal.

Turns out there is, and here it is: 

$group = Get-UnifiedGroup -Identity "Group_Name_or_Email_Address"

# Get list of all members

$members = Get-UnifiedGroupLinks -Identity $group.Name -LinkType Members

# Get list of all subscribers (a-ha!)

$subscribers = Get-UnifiedGroupLinks -Identity $group.Name -LinkType Subscribers

# Subscribe all members not subscribed

foreach ($member in $members) { 

    If ($member.Name -notin $subscribers.Name) {

        Write-Host "Adding $($member.Name)."

        Add-UnifiedGroupLinks -Identity $group.Name -LinkType Subscribers -Links $member.Name

    } else {

        Write-Host "$($member.Name) is already subscribed."

    }

}

# Done!

Almost all of the solutions I found online took the "remove everyone and then add them back again" approach, and maybe that's because that was the only solution at the time. Microsoft is always making changes and introducing new features and functionality, so maybe this PowerShell-based solution was not available until recently. Anyway, it works and it's a pretty simple solution.

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.

Don't Use O365 Portals To Set Permissions, Part 1 - FullAccess

I'm finding the O365 and Exchange Admin portals to be quite unreliable when it comes to viewing, setting, and changing permissions on Exchange objects. Retrieval times are slow and timeouts frequently occur. It's particularly frustrating when the current permissions finally appear and I quickly realize that they're not right, that some entries are missing. Our multi-geo environment undoubtedly makes this worse. It's just quicker and easier to use PowerShell, so I'm going to share the various commands and one-liners that I use on a regular basis to get the job done.

In almost all instances, a mailbox or a user can be referenced in a command parameter using their userprincipalname, email address or even their full name enclosed in double quotes. For example, you can use "jdirt@redneck.org", "JoeDirt@redneck.org" or "Joe Dirt" and Exchange will quickly and correctly locate and use the right object. There are always exceptions, but I haven't run into one yet. In the examples below, wherever you see "<mailboxaddress>" or "<useraddress>", you can substitute one of these IDs.

Mailbox Permissions - this is usually just a case of either adding or removing the FullAccess. Since completing our migration to Exchange Online, I'm finding that most of our shared mailboxes have a lot of stale permissions, be it unresolved SIDs or deleted O365 accounts.

The first step is to review the current permissions:

Get-MailboxPermission <mailboxaddress>

That works fine, but the output includes a lot of extra information you don't necessarily need or care about, and some of the stuff you do care about gets truncated. Here's what I use to get just the output I'm interested in.

Get-MailboxPermission <mailboxaddress> | where {$_.isinherited -eq $false -AND $_.user -notlike "NT AUTHORITY*"} | select user, accessrights | sort user

This removes the default and inherited permissions, and in most cases, the output doesn't get truncated. That final Sort helps when I need to copy multiple user strings to the clipboard so I can use Get-Clipboard to pull them into a variable.

The following commands handle the permissions changes.

Add-MailboxPermission <mailboxaddress> -AccessRights FullAccess -User <useraddress>

Remove-MailboxPermission <mailboxaddress> -AccessRights FullAccess -User <useraddress> -Confirm:$false

Next time, I'll cover the Send-As and Send On Behalf Of permissions.

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