Skip to content
Advertisement

Formatting Big List from AD Query / Python

Trying to write a program that will look at all users in AD and place them in the correct ADGroup based on location, job title, and job code. Currently I am using a query to pull all the users by department number. Which is working fine but the output I am getting is in a giant list that is formatted like below:

[{'physicalDeliveryOfficeName': 'Denver, Colorado', 'title': 'Logistics Account Executive Trainee', 'cn': 'Jim Beam'}, {'physicalDeliveryOfficeName': 'Denver, Colorado', 'title': 'Logistics Account Executive', 'cn': 'Joseph Coots'}, 

I am wanting to change the formatting so that it can actually be read and uploaded to a CSV file for latter use. Below is the code I have so far.

from sqlite3 import Row
from unicodedata import name
from pyad import*
from pyad import adquery
import pyad.adquery
import re
import csv



UserName = "122"

q = pyad.adquery.ADQuery()

q.execute_query(
    attributes = ["cn","title", "physicalDeliveryOfficeName"],
    where_clause = f"departmentNumber = '{UserName}'",
    base_dn="OU=*** Users,OU=**,DC=***,DC=com"
)



result = list(q.get_results())
for list in result():
  namead = list["cn"]

name = namead.pop

# group = pd.Dataframe({'P1': [x['parameter1'] for x in result], 'P2':[x['parameter2'] for x in result], 'P3':[x['parameter3'] for x in result]}) #'P3':[x['parameter3'] for x in result]

# f= open(r'C:UsersjustinDenver.csv', 'w+', newline='') 

# writer = csv.writer(f)

# writer.writerow(group)

print(name)

Advertisement

Answer

Assuming the formatting of the output is consistent, I’d say the following solves your problem:

import pandas as pd
# result = [{'physicalDeliveryOfficeName': 'Denver, Colorado', 'title': 'Logistics Account Executive Trainee', 'cn': 'Jim Beam'}, {'physicalDeliveryOfficeName': 'Denver, Colorado', 'title': 'Logistics Account Executive', 'cn': 'Joseph Coots'}]

result_dataframe = pd.concat([pd.DataFrame.from_dict(d, orient= "index").transpose() for d in result]).reset_index(drop = True)
result_dataframe.to_csv("C:/Users/justin/Denver.csv")
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement