https://community.databricks.com/t5/technical-blog/databricks-for-identity-systems-part-3-group-membership/ba-p/116421 <P><FONT size="5">Welcome back!</FONT></P> <P>In <A href="https://community.databricks.com/t5/technical-blog/databricks-for-identity-systems-part-1/ba-p/116122" target="_self">Part 1</A> of this series, we walked through the process of exporting our <STRONG>Okta Users</STRONG> to Databricks. In <A href="https://community.databricks.com/t5/technical-blog/databricks-for-identity-systems-part-2-groups-and-group-rules/ba-p/116260" target="_self">Part 2</A> of the series, we exported our <STRONG>Okta Groups</STRONG> and <STRONG>Group Rules</STRONG>. In this installment, we'll collect our <STRONG>Group Members</STRONG>, so we can start tying these tables together!&nbsp;</P> <P><FONT size="5">Notebook Setup</FONT></P> <P>Okta, so...admittedly, the SDK is working out ok (except for the pickling issue in the last Notebook). So, I guess I'm going to try to keep it going. Let's make sure we have the modules/libraries installed for this Notebook and restart our kernel if we had to install any of the modules.</P> <LI-CODE lang="python">import importlib.util import sys mods = ['nest_asyncio', 'okta'] restart_required = False for mod in mods: spec = importlib.util.find_spec(str(mod)) if spec is not None: print(f'{mod} already installed') else: %pip install {mod} restart_required=True if restart_required==True: dbutils.library.restartPython()</LI-CODE> <P><FONT size="5">Don't Forget Your Secret!</FONT></P> <P>I'm not going into detail with the secret management anymore, but don't forget that you'll need to retrieve it and decode is appropriately (see Part 1 for details).</P> <P><FONT size="5">Get You Groups and Members!</FONT></P> <P>In order to get all group memberships, we need to first get all the groups, right? I mean, it makes sense to me, at least. So, let's do that step first, then iterate through all of the groups to collect the group memberships.</P> <LI-CODE lang="python">#%pip install okta import okta import nest_asyncio import asyncio from okta.client import Client as OktaClient config = { 'orgUrl': 'https://my-okta-org.okta.com', 'token': okta_key } okta_client = OktaClient(config) async def list_okta_groups(): group_list = [] groups, resp, err = await okta_client.list_groups() while True: for group in groups: group_list.append(group) if resp.has_next(): groups, err = await resp.next() else: break return group_list async def get_all_group_memberships(groups): group_data = [] #first get all the groups for group in groups: print(f'checking {group.profile.name}: {group.id}') member_list = [] members, resp, err = await okta_client.list_group_users(groupId=group.id) while True: for member in members: group_data.append({"group_id":group.id, "user_id":member.id, "user_login":member.profile.login}) if resp.has_next(): members, err = await resp.next() else: break return group_data if __name__ == '__main__': nest_asyncio.apply() groups = asyncio.run(list_okta_groups()) # get all groups members = asyncio.run(get_all_group_memberships(groups)) # for each group, let's get the members</LI-CODE> <P><FONT size="5">Add your as_of_date!</FONT></P> <P>This step is optional, of course, but I like to add today's date to the data, so we can always see a snapshot of what the environment looked like on any given day.</P> <LI-CODE lang="python">from datetime import date new_coll = [] today = date.today() for one in members: # Create a copy of the dictionary to avoid modifying the original updated_one = one.copy() # Add the new key-value pair updated_one['as_of_date'] = str(today) # Append the updated dictionary to the list new_coll.append(updated_one) members = new_coll</LI-CODE> <P><FONT size="5">Define the Schema</FONT></P> <P>This is probably one of the simplest schemas in the series. There really isn't much nested information to extract from this JSON object. In this instance, our bronze and silver layers are basically the same. I think I only kept it as both tables for consistency. /shrug</P> <LI-CODE lang="python">import json from pyspark.sql import SparkSession from pyspark.sql.functions import col from pyspark.sql.types import StructType, StructField, StringType, IntegerType, BooleanType, ArrayType # Create a SparkSession spark = SparkSession.builder.appName("OktaGroupMembers").getOrCreate() # Define the schema schema = StructType([ StructField("group_id", StringType(), True), StructField("user_id", StringType(), True), StructField("user_login", StringType(), True), StructField("as_of_date", StringType(), True) ]) df = spark.createDataFrame(members, schema) df_formatted = df.select("group_id", "user_id", "user_login", "as_of_date")</LI-CODE> <P><FONT size="5">Write to the table</FONT></P> <P>As always, our last step is to write our dataframe to a table.</P> <LI-CODE lang="python">df.write.option("mergeSchema", "true").saveAsTable("users.jack_zaldivar.okta_group_members", mode="append") df_formatted.write.option("mergeSchema", "true").saveAsTable("users.jack_zaldivar.okta_group_members_formatted", mode="append") </LI-CODE> <P><FONT size="5">Well done!</FONT></P> <P>You've made it to the end of the next installment and now you've got your Users, Groups, Group Rules, and Group Members all imported to Databricks! Don't forget to create a Schedule so that these Notebooks will all run daily. This will give you a daily snapshot of your environment.</P> Wed, 09 Jul 2025 16:17:11 GMT jack_zaldivar 2025-07-09T16:17:11Z https://community.databricks.com/t5/technical-blog/databricks-for-identity-systems-part-3-group-membership/ba-p/116421 <P>Collect group members to tie our users and groups together</P> Wed, 09 Jul 2025 16:17:11 GMT https://community.databricks.com/t5/technical-blog/databricks-for-identity-systems-part-3-group-membership/ba-p/116421 jack_zaldivar 2025-07-09T16:17:11Z