List all users groups and the actual users in them in sql

[eballinger]

We have a bunch of cloud AD groups in Databricks and I can see what users are in each group by using the user interface Manage Account -> Users and groups -> Groups

I would like to be able to produce this full list in SQL. I have found the below code online to give me exactly what I want but it is in Python. There doesnt seem to be a way in SQL to do this? What I was thinking is if the SQL was a little complex I would just put it inside a SQL View and then my report could just call this view. 

Also I tried to create a UDF that would use the code below (because you can use Python) but it gave me a error that   [FEATURE_UNAVAILABLE] Python UDTF is not supported in your environment. To use this feature, please contact Databricks Support. SQLSTATE: 56038

Thanks

# Databricks notebook source

from pyspark.sql.functions import lit

# COMMAND ----------

df_groups = spark.sql("""SHOW GROUPS""")

# COMMAND ----------

df_users = spark.sql("""SHOW USERS""")

# COMMAND ----------

result = spark.createDataFrame([], "name: string, directGroup: boolean, user: string")

for user in df_users.collect():

    df_user_groups = spark.sql(f"""SHOW GROUPS WITH USER `{user.name}`""").withColumn("user", lit(user.name))

    result = result.unionAll(df_user_groups)

# COMMAND ----------

display(result) # Here you can create temp view and select data

[BigRoux]

Try this:

SQL Query Options:

• Databricks SQL supports the SHOW GROUPS command to list all groups within a system. This command optionally allows filtering by specific user associations or regular expressions to identify desired groups.
• The statement syntax is: SHOW GROUPS [ WITH USER user_principal | WITH GROUP group_principal ] [ [ LIKE ] regex_pattern ]; This can be used to fetch specific groups associated with users. The output provides columns like name (group name) and directGroup (membership type: direct/indirect or NULL if no principal is specified)

[eballinger]

Thanks for the reply. Unfortunately with the commands SHOW GROUPS and SHOW USERS there does not seem to be a way to produce a full list of users in groups though. If there was a way to get the users first from SHOW USERS and then use that to join to the SHOW GROUPS command (like its doing in the Python code) that might work but I cant seem to get that working. 

[BigRoux]

Ok. Well, my last suggestion is to have a look at the SCIM Users API, and SCIM Groups API. You should be able to make the API calls right in the notebook.

Cheers, Lou.

[DayaSira_84428]

API doesnt seem to work for both groups and users for azure databricks