Querying Microsoft Graph API with Python

Introduction

In order to deliver a rich data-driven experience to your users, you are likely to integrate custom-built applications with your organization’s data. Microsoft Graph, a REST API, offers the ability to interact with data in Office 365. In this post, I will illustrate connecting to your Azure Active Directory (Azure AD) using python. The main steps are setting up an enterprise application on Azure and writing code to handle the data.

Get this service offering here

Creating an Enterprise Application

Login to Azure using a Global admin account.

On the left pane navigate to >> Azure Active Directory >> Enterprise Applications >> +New application >> Add you own App (Application your are Developing) >> +New Registration

After Successfully Registering an app you will see the below;

Snapshot

On the right, Add a redirect URL with the below template

https://login.microsoftonline.com/<DIRECTORY (tenant) ID>/oauth2/token

Certificates & Secrets

Create a Client Secret and note it.

API — Permissions

By default, Microsoft Graph User.Read permissions are added. For demonstration, I will query O365 Planner (Trello equivalent ) data. The below are permissions granted with consent to an account with NO 2 MFA. For more information see docs

Getting the Data

Let’s dive into the python script;

#import libraries
import requests
import json
import pandas as pd
import pyodbc

Create a token data dictionary to use in the requests

app_id = 'xxxxxx' #Application Id - on the azure app overview page
client_secret = 'xxxxx' #Use the redirect URL to create a token url
token_url = 'https://login.microsoftonline.com/<DIRECTORY (tenant) ID>/oauth2/token'token_data = {
 ‘grant_type’: ‘password’,
 ‘client_id’: app_id,
 ‘client_secret’: client_secret,
 ‘resource’: ‘https://graph.microsoft.com',
 ‘scope’:’https://graph.microsoft.com',
 ‘username’:’john.doe@companyxxx.com’, #Account with no 2MFA
 ‘password’:’Happy2020’,
}token_r = requests.post(token_url, data=token_data)
token = token_r.json().get(‘access_token’)

Test the token

token_r.content

Create a plan on Office 365 Planner and create a group dictionary as below:

#groups list
group_ids = {‘plannergroup01’:’xxxx-xxx–xxx-xxxx–xx',’test’:’xxxx-xxx–xxx-xxxx–xx’}#define required lists
userId,displayName,mailAddress,plans_data,planId,PlanGrpOwnerId,PlanTitle,PlanCreatedBy,bucketId,bucketName,bucketPlanId,taskId,taskPlanId,taskBucketId,taskName,taskPercentComplete,taskStartDateTime,taskDueDateTime,taskCompleteDateTime,taskIdAssignment,taskUserIdAssignment = [],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]

Users Query:

# Use the token using microsoft graph endpoints
users_url = ‘https://graph.microsoft.com/v1.0/users?$top=500'
headers = {
 ‘Authorization’: ‘Bearer {}’.format(token)
}user_response_data = json.loads(requests.get(users_url, headers=headers).text)
# user_response_data[‘@odata.nextLink’]#initial user data
#get all users
for user in user_response_data[‘value’]:
 userId.append(user[‘id’])
 displayName.append(user[‘displayName’])
 mailAddress.append(user[‘userPrincipalName’])
 
users_dict = {‘userId’:userId,’displayName’:displayName,’mailAddress’:mailAddress}
users_df = pd.DataFrame(data=users_dict)#additional user query for paging
while ‘@odata.nextLink’ in user_response_data:
 user_response_data = json.loads(requests.get(users_url, headers=headers).text)
 if ‘@odata.nextLink’ in user_response_data: 
 users_url = user_response_data[‘@odata.nextLink’]
 
 for user in user_response_data[‘value’]:
 userId.append(user[‘id’])
 displayName.append(user[‘displayName’])
 mailAddress.append(user[‘userPrincipalName’])users_dict = {‘userId’:userId,’displayName’:displayName,’mailAddress’:mailAddress}
users_df = pd.DataFrame(data=users_dict)
users_df.head()

Planner plans Query:

#get all plansfor key, value in group_ids.items():
 
 plans_url = ‘https://graph.microsoft.com/v1.0/groups/'+ value +’/planner/plans?$top=500' 
 plans_response_data = json.loads(requests.get(plans_url, headers=headers).text)for plan in plans_response_data[‘value’]:
 planId.append(plan[‘id’])
 PlanGrpOwnerId.append(plan[‘owner’])
 PlanTitle.append(plan[‘title’])
 PlanCreatedBy.append(plan[‘createdBy’][‘user’][‘id’])
 
plans_dict = {‘planId’:planId,’PlanGrpOwnerId’:PlanGrpOwnerId,’PlanTitle’:PlanTitle,’PlanCreatedBy’:PlanCreatedBy}
plans_df = pd.DataFrame(data=plans_dict)

Planner Buckets Query:

#get all buckets
for plan_id in planId:
 buckets_url = ‘https://graph.microsoft.com/v1.0/planner/plans/'+ plan_id +’/buckets’
 buckets_response_data = json.loads(requests.get(buckets_url, headers=headers).text)
 
 for bucket in buckets_response_data[‘value’]:
 bucketId.append(bucket[‘id’])
 bucketName.append(bucket[‘name’])
 bucketPlanId.append(bucket[‘planId’]) 
 
bucket_dict = {‘bucketId’:bucketId,’bucketName’:bucketName,’bucketPlanId’:bucketPlanId} 
bucket_df = pd.DataFrame(data=bucket_dict)

Planner Tasks Query:

#get all tasksfor plan_id in planId:
 tasks_url = ‘https://graph.microsoft.com/v1.0/planner/plans/'+ plan_id +’/tasks’
 tasks_response_data = json.loads(requests.get(tasks_url, headers=headers).text)
 
 for task in tasks_response_data[‘value’]:
 taskId.append(task[‘id’])
 taskPlanId.append(task[‘planId’])
 taskBucketId.append(task[‘bucketId’])
 taskName.append(task[‘title’])
 taskPercentComplete.append(str(task[‘percentComplete’]))
 if task[‘startDateTime’] is not None:
 taskStartDateTime.append(datetime.strptime(task[‘startDateTime’], ‘%Y-%m-%dT%H:%M:%SZ’))
 else:
 taskStartDateTime.append(0)
 if task[‘dueDateTime’] is not None:
 taskDueDateTime.append(datetime.strptime(task[‘dueDateTime’], ‘%Y-%m-%dT%H:%M:%SZ’))
 else:
 taskDueDateTime.append(0)
# if task[‘completedDateTime’] is not None:
# complete_time = task[‘completedDateTime’].replace(task[‘completedDateTime’].split(‘:’)[-1],’00Z’)
# taskCompleteDateTime.append(datetime.strptime(complete_time, ‘%Y-%m-%dT%H:%M:%S%z’))
# else:
# taskCompleteDateTime.append(str(0))
 
 for assignment in task[‘assignments’]:
 taskIdAssignment.append(task[‘id’]) 
 taskUserIdAssignment.append(assignment)tasks_dict = {‘taskId’:taskId,’taskPlanId’:taskPlanId,’taskBucketId’:taskBucketId,’taskName’:taskName,’taskPercentComplete’:taskPercentComplete,’taskStartDateTime’:taskStartDateTime,’taskDueDateTime’:taskDueDateTime,’taskCompleteDateTime’:0} 
task_df = pd.DataFrame(data=tasks_dict)

Insert user data into a database:

The below function can be replicated for all data frames created above. In my case, I am using SQL Server 2017.

server = 'SERVERNAME\INSTANCEID,64346'
database = 'GRAPHAPI' 
username = 'uname' 
password = 'Happy2020'def insert_user_db(users_df,server,database,username,password):#Create a connection string
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
 
 for index in range(users_df.shape[0]): 
 #insert into table 
 try:
 insert_query = “INSERT INTO GRAPHAPI.dbo.[DIM_User] ([userId],[displayName],[mailAddress]) VALUES (?,?,?)”
 cursor.execute(insert_query,users_df[‘userId’][index],users_df[‘displayName’][index],users_df[‘mailAddress’][index])except:cnxn.rollback()finally:cnxn.commit()
 cnxn.close()#Call the function
insert_user_db(users_df,server,database,username,password)

Conclusion

With the wide range of Microsoft suite services, there are many other application scenarios for Graph API requests. This tremendous amount of data provide business intelligence for an organization and would enable developers build data-driven applications.