Automatic Import Of Your Active Directory Groups And Users

Community_Admin · ‎10-14-2021

Question:

How can I automate the AD import? For those of us who are using active directory, it might be a tedious process to import all of the relevant groups and users from it to Sisense.

Answer:

We have written a Python script which does that for you!

WHAT DOES IT DO?

The script imports all of the relevant groups from your active directory, and then imports all of the users who belong to those groups.

HOW TO USE IT?

Install Python 2.7 on your server, which can be downloaded from here.
Install the following Python libraries: requests, ldap3, termcolor, unicodedata
Fill in the following parameters which can be found at the beginning of the script:

server_address - your active directory server address.
domain_name - the relevant domain under your active directory.
user_name - the username of a user who has authorizations to read from your active directory.
password - the password of the above user.
groups_prefix - if you have in your active directory only several groups which are relevant for Sisense, if you have a prefix for them then put it here and the script will import these groups only. Very recommended to have one to save you the trouble of importing non-relevant groups.
search_strings - this is the search command used to retrieve all of your groups. It needs to contain the relevant OUs and DUs, so just fill those in at the right places (if needed, you can change the amount of OUs and DUs in the command).
sisense_url - your Sisense sever url.
sisense_admin - the username of one of your Sisense admins.
sisense_password - the password of the above user.
That's it! You can run the script 🙂 It is very recommended to schedule it, for example by using Windows task scheduler.

The full code:

import sys
import time

import requests
import json
import ldap3
from ldap3 import Server, Connection, ALL, NTLM, ALL_ATTRIBUTES, ALL_OPERATIONAL_ATTRIBUTES
from colorama import init
from termcolor import colored
import unicodedata


# AD credentials
server_address = "" # Replace with your AD server name
domain_name = "" # Replace with the relevant domain name
user_name = "" # Replace with AD admin username
password = "" # Replace with your admin user's password
groups_prefix = "" # if you have a prefix for all of the groups in your AD which are relevant for Sisense, put it here.
search_string = "ou={},ou={},ou={},dc={},dc={}" # replace with the relevant OUs and DUs to search through for your groups

# Sisense credentials
sisense_url = "http://localhost:8081" # replace with your Sisense url
sisense_admin = "omer.sade@sisense.com" # replace
sisense_password = "Sisense"

#-------------------------------------------------------------------------------------------------------

# get Bearer
def get_bearer(user_name, password):
    url = sisense_url + "/api/v1/authentication/login"

    payload = "username=" + sisense_admin.replace('@', '%40') + "&password=" + sisense_password

    headers = {
        'content-type': "application/x-www-form-urlencoded"
    }

    req = requests.request("POST", url, headers=headers, data=payload)
    # print(req.json())
    auth_token = req.json()["access_token"]
    return auth_token

# check if the group is already in Sisense
def check_group_in_Sisense(group):
    url = sisense_url + "/api/v1/groups?name=" + group
    auth_token = get_bearer(sisense_admin, sisense_password)

    headers = {
        "authorization": "Bearer " + auth_token,
        "accept": "application/json"
    }

    req = requests.request("GET", url, headers=headers)
    if req.json():
        print("Group " + group + " in Sisense")
        return True
    print("Group " + group + " not in Sisense")
    return False

# if the group is in Sisense, get its ID
def get_group_from_Sisense(group):
    url = sisense_url + "/api/v1/groups?name=" + group
    auth_token = get_bearer(sisense_admin, sisense_password)

    headers = {
        "authorization": "Bearer " + auth_token,
        "accept": "application/json"
    }

    req = requests.request("GET", url, headers=headers)
    if req.json():
        print("group ID: " + req.json()[0]["_id"])
        return req.json()[0]["_id"]
    return None

# check if the user is in Sisense
def check_user_in_Sisense(user):
    username = unicodedata.normalize('NFKD', user).encode('ascii', 'ignore')
    url = sisense_url + "/api/users?search=" + username.replace(' ', '%20')
    auth_token = get_bearer(sisense_admin, sisense_password)

    headers = {
        "authorization": "Bearer " + auth_token,
        "accept": "application/json"
    }

    req = requests.request("GET", url, headers=headers)
    res = req.json()
    if req.json():
        print("User " + username + " in Sisense")
        # print res
        return res[0]["email"]
    print("User " + username + " not in Sisense")
    return user

# add the user to Sisense as AD user
def add_user_to_Sisense_as_AD(user):
    payload = get_user_from_AD(user)
    # print colored(payload, "green")

    url = "http://localhost:8081/api/v1/ldap_domains?name=" + domain_name
    auth_token = get_bearer(sisense_admin, sisense_password)

    headers = {
        "Authorization": "Bearer " + auth_token,
        "Accept": "application/json",
        "Content-Type": "application/json"
    }
    response = requests.request("GET", url, headers=headers)
    response = response.json()
    if len(response) == 0:
        print colored("active directory is not linked to Sisense", "red")
        return

    response = response[0]
    # print(payload)
    email = payload[0]["userPrincipalName"]
    user = user.encode("utf-8")

    try:
        payload = {
            # "userName": payload["attributes"]["mailNickname"],
            "userName": email[:email.index('@')],
            # "ldapDomainId": response["_id"]
            "ldapDomainId": payload[0]["domain"]["_id"]
        }
    except Exception as ex1:
        print(user + " was not found on active directory via Sisense API")
        print ex1
        return
    # print(payload)
    # '{ \"userName\": \"Omer.Sade\", \"ldapDomainId\":\"5b6bf90754328431e40b822e\"}'

    url = sisense_url + "/api/v1/users/ad"
    req = requests.request("POST", url, data=json.dumps(payload), headers=headers)
    # print(req)
    if int(req.status_code) < 400:
        print colored(user + " Was added to Sisense", "green")
        return email
    print colored("There was an error in adding the user: " + req.text, "red")
    return email

# add the user to an AD group
def add_user_to_group(user, group):
    # print user.encode("utf-8")
    groups = [group.decode('utf-8')]
    url = sisense_url + "/api/v1/users?email=" + user.replace("@", "%40")
    auth_token = get_bearer(sisense_admin, sisense_password)

    headers = {
        "Authorization": "Bearer " + auth_token,
        "Accept": "application/json",
        "Content-Type": "application/json"
    }

    req = requests.request("GET", url, headers=headers)
    # print req.json()
    try:
        user = req.json()[0]["userName"].replace(" ", "%20")
    except:
        print(user + " not found in Sisense")

    if req.status_code == 200:
        try:
            req = req.json()[0]
            groups.extend(req["groups"])
        except:
            pass
    groups = list(set(groups))
    groups = {"groups": groups}
    groups = json.dumps(groups)
    print(groups, type(groups))
    url = sisense_url + "/api/users/" + user
    req = requests.request("PUT", url, data=groups, headers=headers)
    if int(req.status_code) < 400:
        print colored(user + " groups updated", "green")
        return req.text
    print colored("There was an error in updating the user's groups: " + req.text, "red")
    return None

# add the AD group to Sisense
def add_group_to_Sisense(group):
    url = sisense_url + "/api/groups/ad?search=" + group.replace(' ', '%20')
    auth_token = get_bearer(sisense_admin, sisense_password)
    # print auth_token

    headers = {
        "authorization": "Bearer " + auth_token,
        'accept': "application/json",
        'content-type': "application/json"
    }

    print("running group search")
    req = requests.request("GET", url, headers=headers)

    req = req.json()

    if len(req) == 0:
        print("Can't find group " + group)
        return

    for item in req:
        payload = {
            "name": item["cn"],
            "cn": item["cn"],
            "objectSid": item["objectSid"],
            "dn": item["dn"],
            "ldapDomainId": item["domain"]["_id"]
        }

        # print("payload is: "+payload)
        url = sisense_url + "/api/v1/groups/ad"
        req = requests.request("POST", url, data=json.dumps(payload), headers=headers)
        print(req.status_code)
        if int(req.status_code) < 400:
            print(group + " Was added to Sisense")
            return req.text
    print colored("There was an error in adding the group to Sisense: " + req.text, "red")
    return None

# get the user from AD
def get_user_from_AD(user):
    # user = user.encode("UTF-8")
    final_username = unicodedata.normalize('NFKD', user).encode('ascii', 'ignore')
    server = Server(server_address, get_info=ALL)
    conn = Connection(
        server,
        "uid={}\\{}".format(domain_name, user_name),
        password=password,
        authentication=NTLM,
        auto_bind=True
    )

    auth_token = get_bearer(sisense_admin, sisense_password)
    # print auth_token

    headers = {
        "authorization": "Bearer " + auth_token,
        'accept': "application/json",
        'content-type': "application/json"
    }
    url = sisense_url + "/api/users/ad?checkExist=true&includeDomain=true&limit=10&search=" + final_username

    req = requests.request("GET", url, headers=headers)
    req = req.json()
    # If ascii represantation failed, try replace unicode chars with *
    if len(req) == 0:
        final_username = ''
        for char in user:
            if ord(char) < 128:
                final_username += char
            else:
                final_username += "*"
        # print final_username
        url = sisense_url + "/api/users/ad?checkExist=true&includeDomain=true&limit=10&search=" + final_username
        req = requests.request("GET", url, headers=headers)
        req = req.json()
    # print(req)
    return req

    #print(user)
    conn.search("dc={}, dc=corp".format(domain_name), "(CN=" + user + ")", attributes=[ALL_ATTRIBUTES, ALL_OPERATIONAL_ATTRIBUTES])
    response = conn.response
    #print(response)
    if len(response) > 0:
        response = response[0]
        print(user+" retrieved from active directory")
        return response
    else:
        conn.search("dc={}, dc=corp".format(domain_name), "(CN=" + user + "*)", attributes=[ALL_ATTRIBUTES, ALL_OPERATIONAL_ATTRIBUTES])
        response = conn.response
        if len(response) > 0:
            response = response[0]
            print(user+" retrieved from active directory")
            return response
    print colored("There was an error in retrieving the user from the active directory", "red")
    return None


server = Server(server_address, get_info=ALL)
conn = Connection(
    server,
    "uid={}\\{}".format(domain_name, user_name),
    password=password,
    authentication=NTLM,
    auto_bind=True
)

# print(conn.request)
# print(conn.result)

conn.search(search_string,
            '(&(objectclass=group)(cn=' + groups_prefix + '*))',
            attributes=[ALL_ATTRIBUTES, ALL_OPERATIONAL_ATTRIBUTES])
# print conn.entries

groups = []

for e in sorted(conn.entries):
    groups.append(e)

print("Total groups: " + str(len(groups)))
# groups = [groups[50]]

for group in groups:
    try:
        group_name = str(group.name)
        if not check_group_in_Sisense(group_name):
            add_group_to_Sisense(group_name)
        group_id = get_group_from_Sisense(group_name)
        for user in group.member:
            try:
                print colored(user.encode("utf-8"), "yellow")
                username = user[3:user.index(",")]
                # if user[3:user.index(",")].find("(")!=-1:
                #  username = username[:username.index("(")]+"*"
                # print username
                # final_username = unicodedata.normalize('NFKD', username).encode('ascii','ignore')
                # print final_username

                final_username = check_user_in_Sisense(username)
                if final_username.find('@') == -1:
                    final_username = add_user_to_Sisense_as_AD(final_username)
                '''
                if final_username[len(final_username)-1] == '*':
                    final_username = final_username[:-2]
                    print(final_username)
                amount_of_names = final_username.split()
                if len(amount_of_names) > 2:
                    final_username = amount_of_names[0] + " " + amount_of_names[1]
                    print colored("name shortened: "+final_username, "green")
                '''
                add_user_to_group(final_username, group_id)
            except Exception as e:
                print colored("user sync failed. Reason: " + str(e), "red")
                print colored(sys.exc_info(), "red")
    except Exception as ex:
        print colored("group sync failed. Reason: " + str(ex), "red")
print colored("Done!", "green")

Sisense Community

Automatic Import Of Your Active Directory Groups And Users

Question:

Answer:

WHAT DOES IT DO?

HOW TO USE IT?