This functionality is implemented based on Eilert Hjelmeseth's project and his GitHub project. And Eitan Blumin's enhanced Github project
Sample code
Note below code that how the Headers are constructed for certain API call, how select fields can be, and how the repsonse would be in certain format, especially from Microsoft APIs
ALTER FUNCTION [dbo].[GetBearerTokenForIntune]() RETURNS VARCHAR(MAX) AS
BEGIN
-- Oauth2 information
DECLARE @tenant_id VARCHAR(500) = '' -- add it
DECLARE @client_id VARCHAR(500) = ''; -- add it
DECLARE @client_secret VARCHAR(500) = ''; -- add it
DECLARE @resource VARCHAR(500) = 'https://graph.microsoft.com';
DECLARE @grant_type VARCHAR(500) = 'client_credentials';
DECLARE @url VARCHAR(1000)
DECLARE @parameters VARCHAR(1000)
DECLARE @response XML
DECLARE @response_json NVARCHAR(MAX)
DECLARE @Token VARCHAR(MAX)
-- Build URL
SET @url = 'https://login.windows.net/' + @tenant_id + '/oauth2/token'
-- Build params (body)
SET @parameters = 'client_id=' + @client_id
+ '&client_secret=' + @client_secret
+ '&resource=' + @resource
+ '&grant_type=' + @grant_type
-- Get response
SET @response = dataportal.dbo.[clr_http_request_modern]('POST', @url, @parameters, NULL, NULL)
-- Extract JSON from response (in the body)
SET @response_json = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)')
-- Get Token from JSON
SET @Token = JSON_VALUE(@response_json, '$.access_token')
RETURN @Token
END
ALTER PROC [HR].[Report_Expiring_Contractors_EntraID] AS
BEGIN
DECLARE @ResourceTable TABLE(
id nvarchar(max)
, deletedDateTime nvarchar(max)
, accountEnabled nvarchar(max)
, ageGroup nvarchar(max)
, assignedLicenses nvarchar(max)
, assignedPlans nvarchar(max)
, businessPhones nvarchar(max)
, city nvarchar(max)
, companyName nvarchar(max)
, consentProvidedForMinor nvarchar(max)
, manager nvarchar(max)
, country nvarchar(max)
, createdDateTime nvarchar(max)
, creationType nvarchar(max)
, department nvarchar(max)
, deviceKeys nvarchar(max)
, displayName nvarchar(max)
, employeeHireDate nvarchar(max)
, employeeId nvarchar(max)
, employeeOrgData nvarchar(max)
, employeeType nvarchar(max)
, externalUserState nvarchar(max)
, externalUserStateChangeDateTime nvarchar(max)
, faxNumber nvarchar(max)
, givenName nvarchar(max)
, identities nvarchar(max)
, imAddresses nvarchar(max)
, infoCatalogs nvarchar(max)
, isResourceAccount nvarchar(max)
, jobTitle nvarchar(max)
, lastPasswordChangeDateTime nvarchar(max)
, legalAgeGroupClassification nvarchar(max)
, licenseAssignmentStates nvarchar(max)
, mail nvarchar(max)
, mailNickname nvarchar(max)
, mobilePhone nvarchar(max)
, officeLocation nvarchar(max)
, onPremisesDistinguishedName nvarchar(max)
, onPremisesDomainName nvarchar(max)
, onPremisesExtensionAttributes nvarchar(max)
, onPremisesImmutableId nvarchar(max)
, onPremisesLastSyncDateTime nvarchar(max)
, onPremisesProvisioningErrors nvarchar(max)
, onPremisesSamAccountName nvarchar(max)
, onPremisesSecurityIdentifier nvarchar(max)
, onPremisesSyncEnabled nvarchar(max)
, onPremisesUserPrincipalName nvarchar(max)
, otherMails nvarchar(max)
, passwordPolicies nvarchar(max)
, passwordProfile nvarchar(max)
, postalcode nvarchar(max)
, preferredDataLocation nvarchar(max)
, preferredLanguage nvarchar(max)
, provisionedPlans nvarchar(max)
, proxyAddresses nvarchar(max)
, refreshTokensValidFromDateTime nvarchar(max)
, showInAddressList nvarchar(max)
, signInSessionsValidFromDateTime nvarchar(max)
, state nvarchar(max)
, streetAddress nvarchar(max)
, surname nvarchar(max)
, usageLocation nvarchar(max)
, userPrincipalName nvarchar(max)
, userType nvarchar(max)
, managerName nvarchar(max)
, managerEmail nvarchar(max)
, processed char(1) default 'N'
)
DECLARE @header VARCHAR(max)
DECLARE @parameters VARCHAR(1000)
DECLARE @response XML
DECLARE @response_json NVARCHAR(MAX)
DECLARE @Token VARCHAR(MAX)
DECLARE @NextLink VARCHAR(MAX)
DECLARE @Resources NVARCHAR(MAX)
DECLARE @options NVARCHAR(MAX)
DECLARE @userId NVARCHAR(MAX)
DECLARE @managerName NVARCHAR(MAX)
DECLARE @managerEmail NVARCHAR(MAX)
SET @Token = [DataPortal].[dbo].[GetBearerTokenForIntune]()
-- Build URL
SET @NextLink = 'https://graph.microsoft.com/v1.0/users?$count=true&$filter=employeeType eq ''VSP B2B Guest Account''&$select=id, deletedDateTime,accountEnabled, AgeGroup, assignedLicenses, assignedPlans, businessPhones, city, companyName,consentProvidedForMinor,manager, country, createdDateTime, creationType, department, deviceKeys, displayName, employeeHireDate, employeeId, employeeOrgData, employeeType, externalUserState, externalUserStateChangeDateTime, faxNumber, givenName, identities, imAddresses, infoCatalogs, isResourceAccount, jobTitle, lastPasswordChangeDateTime, legalAgeGroupClassification, licenseAssignmentStates, mail, mailNickname, mobilePhone, officeLocation, onPremisesDistinguishedName, onPremisesDomainName, onPremisesExtensionAttributes, onPremisesImmutableId, onPremisesLastSyncDateTime, onPremisesProvisioningErrors, onPremisesSamAccountName, onPremisesSecurityIdentifier, onPremisesSyncEnabled, onPremisesUserPrincipalName, otherMails, passwordPolicies, passwordProfile, postalcode, preferredDataLocation, preferredLanguage, provisionedPlans, proxyAddresses, refreshTokensValidFromDateTime, showInAddressList, signInSessionsValidFromDateTime, state, streetAddress, surname, usageLocation, userPrincipalName, userType'
SET @parameters = ''
SET @header = '<Headers><Header Name="Authorization">Bearer ' + @Token + '</Header><Header Name="ConsistencyLevel">eventual</Header></Headers>'
SET @options = ''
while @NextLink IS NOT NULL
BEGIN
SET @response = DataPortal.dbo.[clr_http_request_modern]('GET', @NextLink, @parameters, @header, @options)
-- Extract JSON from response (in the body)
SET @response_json = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)')
SELECT
@Resources = value,
@NextLink = nextLink
FROM OPENJSON(@response_json)
WITH (
value NVARCHAR(MAX) '$."value"' AS JSON,
nextLink VARCHAR(MAX) '$."@odata.nextLink"')
INSERT INTO @ResourceTable(
id
, deletedDateTime
, accountEnabled
, AgeGroup
, assignedLicenses
, assignedPlans
, businessPhones
, city
, companyName
, consentProvidedForMinor
, manager
, country
, createdDateTime
, creationType
, department
, deviceKeys
, displayName
, employeeHireDate
, employeeId
, employeeOrgData
, employeeType
, externalUserState
, externalUserStateChangeDateTime
, faxNumber
, givenName
, identities
, imAddresses
, infoCatalogs
, isResourceAccount
, jobTitle
, lastPasswordChangeDateTime
, legalAgeGroupClassification
, licenseAssignmentStates
, mail
, mailNickname
, mobilePhone
, officeLocation
, onPremisesDistinguishedName
, onPremisesDomainName
, onPremisesExtensionAttributes
, onPremisesImmutableId
, onPremisesLastSyncDateTime
, onPremisesProvisioningErrors
, onPremisesSamAccountName
, onPremisesSecurityIdentifier
, onPremisesSyncEnabled
, onPremisesUserPrincipalName
, otherMails
, passwordPolicies
, passwordProfile
, postalcode
, preferredDataLocation
, preferredLanguage
, provisionedPlans
, proxyAddresses
, refreshTokensValidFromDateTime
, showInAddressList
, signInSessionsValidFromDateTime
, state
, streetAddress
, surname
, usageLocation
, userPrincipalName
, userType)
SELECT
id
, deletedDateTime
, accountEnabled
, AgeGroup
, assignedLicenses
, assignedPlans
, businessPhones
, city
, companyName
, consentProvidedForMinor
, manager
, country
, createdDateTime
, creationType
, department
, deviceKeys
, displayName
, employeeHireDate
, employeeId
, employeeOrgData
, employeeType
, externalUserState
, externalUserStateChangeDateTime
, faxNumber
, givenName
, identities
, imAddresses
, infoCatalogs
, isResourceAccount
, jobTitle
, lastPasswordChangeDateTime
, legalAgeGroupClassification
, licenseAssignmentStates
, mail
, mailNickname
, mobilePhone
, officeLocation
, onPremisesDistinguishedName
, onPremisesDomainName
, onPremisesExtensionAttributes
, onPremisesImmutableId
, onPremisesLastSyncDateTime
, onPremisesProvisioningErrors
, onPremisesSamAccountName
, onPremisesSecurityIdentifier
, onPremisesSyncEnabled
, onPremisesUserPrincipalName
, otherMails
, passwordPolicies
, passwordProfile
, postalcode
, preferredDataLocation
, preferredLanguage
, provisionedPlans
, proxyAddresses
, refreshTokensValidFromDateTime
, showInAddressList
, signInSessionsValidFromDateTime
, state
, streetAddress
, surname
, usageLocation
, userPrincipalName
, userType
FROM openjson(@Resources)
WITH (
id nvarchar(max) '$."id"',
deletedDateTime nvarchar(max) '$."deletedDateTime"',
accountEnabled nvarchar(max) '$."accountEnabled"',
ageGroup nvarchar(max) '$."ageGroup"',
assignedLicenses nvarchar(max) '$."assignedLicenses"' as JSON,
assignedPlans nvarchar(max) '$."assignedPlans"' as JSON,
businessPhones nvarchar(max) '$."businessPhones"' as JSON,
city nvarchar(max) '$."city"',
companyName nvarchar(max) '$."companyName"',
consentProvidedForMinor nvarchar(max) '$."consentProvidedForMinor"',
manager nvarchar(max) '$."manager"',
country nvarchar(max) '$."country"',
createdDateTime nvarchar(max) '$."createdDateTime"',
creationType nvarchar(max) '$."creationType"',
department nvarchar(max) '$."department"',
deviceKeys nvarchar(max) '$."deviceKeys"' as JSON,
displayName nvarchar(max) '$."displayName"',
employeeHireDate nvarchar(max) '$."employeeHireDate"',
employeeId nvarchar(max) '$."employeeId"',
employeeOrgData nvarchar(max) '$."employeeOrgData"',
employeeType nvarchar(max) '$."employeeType"',
externalUserState nvarchar(max) '$."externalUserState"',
externalUserStateChangeDateTime nvarchar(max) '$."externalUserStateChangeDateTime"',
faxNumber nvarchar(max) '$."faxNumber"',
givenName nvarchar(max) '$."givenName"',
identities nvarchar(max) '$."identities"' as JSON,
imAddresses nvarchar(max) '$."imAddresses"' as JSON,
infoCatalogs nvarchar(max) '$."infoCatalogs"' as JSON,
isResourceAccount nvarchar(max) '$."isResourceAccount"',
jobTitle nvarchar(max) '$."jobTitle"',
lastPasswordChangeDateTime nvarchar(max) '$."lastPasswordChangeDateTime"',
legalAgeGroupClassification nvarchar(max) '$."legalAgeGroupClassification"',
licenseAssignmentStates nvarchar(max) '$."licenseAssignmentStates"',
mail nvarchar(max) '$."mail"',
mailNickname nvarchar(max) '$."mailNickname"',
mobilePhone nvarchar(max) '$."mobilePhone"',
officeLocation nvarchar(max) '$."officeLocation"',
onPremisesDistinguishedName nvarchar(max) '$."onPremisesDistinguishedName"',
onPremisesDomainName nvarchar(max) '$."onPremisesDomainName"',
onPremisesExtensionAttributes nvarchar(max) '$."onPremisesExtensionAttributes"' as JSON,
onPremisesImmutableId nvarchar(max) '$."onPremisesImmutableId"',
onPremisesLastSyncDateTime nvarchar(max) '$."onPremisesLastSyncDateTime"',
onPremisesProvisioningErrors nvarchar(max) '$."onPremisesProvisioningErrors"' as JSON,
onPremisesSamAccountName nvarchar(max) '$."onPremisesSamAccountName"',
onPremisesSecurityIdentifier nvarchar(max) '$."onPremisesSecurityIdentifier"',
onPremisesSyncEnabled nvarchar(max) '$."onPremisesSyncEnabled"',
onPremisesUserPrincipalName nvarchar(max) '$."onPremisesUserPrincipalName"',
otherMails nvarchar(max) '$."otherMails"' as JSON,
passwordPolicies nvarchar(max) '$."passwordPolicies"',
passwordProfile nvarchar(max) '$."passwordProfile"',
postalcode nvarchar(max) '$."postalcode"',
preferredDataLocation nvarchar(max) '$."preferredDataLocation"',
preferredLanguage nvarchar(max) '$."preferredLanguage"',
provisionedPlans nvarchar(max) '$."provisionedPlans"',
proxyAddresses nvarchar(max) '$."proxyAddresses"' as JSON,
refreshTokensValidFromDateTime nvarchar(max) '$."refreshTokensValidFromDateTime"',
showInAddressList nvarchar(max) '$."showInAddressList"',
signInSessionsValidFromDateTime nvarchar(max) '$."signInSessionsValidFromDateTime"',
state nvarchar(max) '$."state"',
streetAddress nvarchar(max) '$."streetAddress"',
surname nvarchar(max) '$."surname"',
usageLocation nvarchar(max) '$."usageLocation"',
userPrincipalName nvarchar(max) '$."userPrincipalName"',
userType nvarchar(max) '$."userType"')
END
SELECT TOP 1 @userId = id FROM @ResourceTable WHERE processed = 'N';
WHILE @userId is not null
BEGIN
-- reuse parameters, header & options from above
SET @NextLink = 'https://graph.microsoft.com/v1.0/users/'+@userId+'/manager';
SET @response = DataPortal.dbo.[clr_http_request_modern]('GET', @NextLink, @parameters, @header, @options);
-- Extract JSON from response (in the body)
SET @response_json = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)');
SELECT @managerName = displayName
,@managerEmail = userPrincipalName
FROM openjson(@response_json)
WITH (
id nvarchar(max) '$."id"',
businessPhones nvarchar(max) '$."businessPhones"' as JSON,
displayName nvarchar(max) '$."displayName"',
givenName nvarchar(max) '$."givenName"',
jobTitle nvarchar(max) '$."jobTitle"',
mail nvarchar(max) '$."mail"',
mobilePhone nvarchar(max) '$."mobilePhone"',
officeLocation nvarchar(max) '$."officeLocation"',
preferredLanguage nvarchar(max) '$."preferredLanguage"',
surname nvarchar(max) '$."surname"',
userPrincipalName nvarchar(max) '$."userPrincipalName"');
UPDATE @ResourceTable
SET processed = 'Y'
, managerName = @managerName
, managerEmail = @managerEmail
WHERE id = @userId;
SET @userId = null;
SELECT TOP 1 @userId = id FROM @ResourceTable WHERE processed = 'N';
END
select employeeId
, displayName
, employeeType
, jobTitle
, Json_value(onPremisesExtensionAttributes, '$.extensionAttribute15') as [OrgLevel1]
, Json_value(onPremisesExtensionAttributes, '$.extensionAttribute6') as [OrgLevel2]
, onPremisesExtensionAttributes
, managerName
, managerEmail
FROM @ResourceTable;
END