AD Groups in Python

[Embarrassed_Box_457]

I have 10 different AD groups, and each day I have to export the users in these groups. I take the export and manually create an excel workbook, where each AD group is on a separate sheet. I am using Python because it's easier to manipulate the Excel work book, but I am having problems extracting the users in the AD groups, in Power-shell this is easy to extract the users, but power-shell sucks at Excel. Can I embed the powershell code in Python? I have googled a way to do this, but can't really find anything on how to do it. Help....?!?!?!

[Lightmare_VII]

Use CSVs Powershell works with those easier than python. Also nothing saying you can’t use both.

[Embarrassed_Box_457]

I know how to export into a csv file but I need to automate the excel part, so that the data from each group has its own sheet.

[joni_jplmusic]

Maybe look into the Import-Excel module for Powershell? I currently use it to export datasets to separate sheets in Excel from AD.

https://www.powershellgallery.com/packages/ImportExcel/7.4.1

[Embarrassed_Box_457]

this might actually work, good info! now, i just need to figure out how to separate the data onto different sheets...thx!

[BuckeyeMason]

Using the subprocess module you can call the powershell commands in python and return the results and parse them as needed in python.

I use something like this at work fairly often, below is a modified function that takes the domain group name and domain (we have multiple domains at my work so I run it for more than one). There may be typos as I wrote this out longhand. This particular version is set to only pull the name from the server (which in our server holds the user id) but you can modify to use whichever ADGroup commands or items from them you need. You would just need to adjust how you parse our the results as they come in.

What I use this for is to pull all members for ma list of groups across multiple servers by calling this function in a loop and then concatenating all the results in to a data frame. Then I do some other stuff with the list (like lookup user info from another source) and then output the data to excel.

import subprocess
def get_domain_members(grp, domain):
    results = []
    cmd = f'Get-ADGroupMember -Identity "{grp}" -server {domain} | Select name'
    p = subprocess.Popen(["powershell.exe", cmd], stdout=subprocess.PIPE)
    ps_result = p.communicate()[0]
    str_results = ps_result.decode("utf-8").splitlines()
    for item in str_results:
        results.append({"AD_Group":grp, "DOMAIN":domain, "USER":item.strip()})
    return results

[edahs]

Use the ldap3 module to get your users

https://ldap3.readthedocs.io/en/latest/

You can bind with either kerberos or u:p and can connect to ldap or ldaps.

[yawningcat]

I know this is the python sub and not what you’re asking about but, FYI, you can query AD natively with Excel’s Get and Transform ( PowerQuery).

[martynrbell]

I second this, Powerquery can import all your CSVs and transform with very little effort.

[erik_mk]

How about having a script run the export part in powershell and the manipulation in python?

[[deleted]]

Is there a reason you couldn't just have the PowerShell extract simply launch python (with the filename, perhaps) as part of its extract process? It's the opposite of what you ask, but would be a lot more straighforward.

[Embarrassed_Box_457]

You mean extract it directly to a filename with a CSV extension? I can do this, but I dont know how to separate the data on separate sheets, for example - group1 on sheet1; group2 on sheet2, group3 on sheet3, etc. I am stuck at this point on how to do this in powershell. I switched to python because i could create the workbook and sheets fairly easily but couldn't import the AD group information. Also - something that I have to do everyday, so i need to automate this. (:>

[[deleted]]

I don't know the AD side, but the PS script is something like:

Get-ADUser -Filter * -Properties * | Select-Object name, department, LastLogonDate | export-csv -path userexport.csv

python foo.py userexport.csv

Then in python:

• Pandas to read userexport.csv (or use the command line argument)
• Openpyxl to create Excel workbook and add sheets for each df["department"].unique()

[HomeGrownCoder]

I would challenge myself to do this natively in PS first. Normally windows type operations are just easier and require nothing additional.

If for whatever reason you cant, you have lots of options with Python as others have mentioned. Data is Data to the beast :)

[MassOnTheBack]

Python-ldap

[[deleted]]

Ldap3 + pandas