Published in

Move Microsoft Graph metadata to Azure Data Explorer using pandas dataframe



pip install azure-kusto-ingest
from azure.identity import InteractiveBrowserCredential
from msgraph.core import GraphClient
import json
from configparser import SectionProxy
from azure.identity import DeviceCodeCredential, ClientSecretCredential
from msgraph.core import GraphClient
from import KustoClient, KustoConnectionStringBuilder
from import KustoServiceError
from import dataframe_from_result_table
from import KustoServiceError
from import dataframe_from_result_table
from import KustoClient, KustoConnectionStringBuilder,ClientRequestProperties
from azure.kusto.ingest import QueuedIngestClient, IngestionProperties, FileDescriptor, BlobDescriptor, ReportLevel, ReportMethod
graph_scopes = "User.Read"
tenant_id = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
client_id = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
client_secret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
client_credential = ClientSecretCredential(tenant_id, client_id, client_secret)
user_client = GraphClient(credential=client_credential,
result = user_client.get('/users')
result1 = result.json()
for i in result1['value']:
print(i["givenName"], i["mail"], i["mobilePhone"], i["officeLocation"], i["userPrincipalName"], i["id"])
import pandas as pd
#df = pd.read_json(result1)
df_nested_list = pd.json_normalize(result1, record_path =['value'])
AAD_TENANT_ID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
KUSTO_DATABASE = "Benchmark"
kcsb_ingest = KustoConnectionStringBuilder.with_aad_application_key_authentication(
KUSTO_INGEST_URI, client_id, client_secret, tenant_id)
KUSTO_INGESTION_CLIENT = QueuedIngestClient(kcsb_ingest)
csvmapping = '[  {"Column": "businessPhones", "Properties": {"Ordinal": "0"}},  {"Column": "displayName", "Properties": {"Ordinal": "1"}},  {"Column": "givenName", "Properties": {"Ordinal": "2"}},  {"Column": "jobTitle", "Properties": {"Ordinal": "3"}},  {"Column": "mail", "Properties": {"Ordinal": "4"}},  {"Column": "mobilePhone", "Properties": {"Ordinal": "5"}},  {"Column": "officeLocation", "Properties": {"Ordinal": "6"}},  {"Column": "preferredLanguage", "Properties": {"Ordinal": "7"}},  {"Column": "surname", "Properties": {"Ordinal": "8"}},  {"Column": "userPrincipalName", "Properties": {"Ordinal": "9"}},  {"Column": "id", "Properties": {"Ordinal": "10"}}]'
from import KustoConnectionStringBuilder, DataFormat
ingestion = IngestionProperties(database="Benchmark", table="graphdata", data_format=DataFormat.CSV, ingestion_mapping_kind=None)
QueuedIngestClient.ingest_from_dataframe(KUSTO_INGESTION_CLIENT,df_nested_list, ingestion)
df_nested_list.to_csv('graphdata.csv', header=True)



Data Scientists must think like an artist when finding a solution when creating a piece of code. ⚪️ Artists enjoy working on interesting problems, even if there is no obvious answer ⚪️ 🔵 Follow to join our 28K+ Unique DAILY Readers 🟠

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store