To homepage To homepage
Solved

Azure AD Data Source cannot generate schema files?

  • 23 May 2024
  • 6 replies
  • 71 views
J
Rank

Hi Folks,

 

I'm trying to use the Azure AD data source to get information from our Azure AD into TimeXtender. I'm extracting User, Group and GroupMembership information using this data source. So far it's working but i notice that a lot of columns that were found are not filled (NULL). If i do a test with PostMan to the Graph API, i see that the fields are filled in (Department and CompanyName).

I’ve also enabled logging, i see the Graph API request being made:
 


I don't see the fields that i want come back in the results:

 

I'm guessing that the schema used to get the data from the API does not contain my field so i want to add them. Normally i would generate the schema files using the ‘Generate Schema Files’ option on the portal and edit those to expand the call to the API. There is an option to specify the location of the schema files, but i'm lacking the ‘Generate Schema Files’ option like we have on other similar data sources:
 

 

Is there maybe an option to enable this outside the portal? Or is the option simply missing from the portal?

 

We are on TimeXtender version 6536.1 and i'm using data source type Azure Active Directory version 23.0.8770.0.

icon

Best answer by Thomas Lind 27 May 2024, 16:03

View original

6 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @JogchumSiR 

I have made some files for this.

The MembersByGroups_input file uses a queryslicer. That means that you run it like so.

SELECT * FROM [REST].[MembersByGroup_Input] WHERE [GroupId] IN (SELECT [Id] FROM [REST].[Groups])

The issue is that Microsoft enforces some limits in the Groups endpoint. Once you attempt to look at members and similar. It would appear to work except all fields were empty as you also experience.

So this file is supposed to subvert this.

1 Attachment
J
Rank

Hi @Thomas Lind ,

I've tried your solution by placing the rsd files in the folder that was configured in the data source in the TimeXtender Portal. Unfortunately i get some errors.

First when i synchronize the data source, suddenly a lot of tables are not recognized anymore:

* These are all tables where not rsd file is present.

Second, when i execute one of the tables that have a rsd file the following error occurs:

 

I'm not sure how to continue on this as the file looks alike from other rsd files we've been able to generate but somehow the data source drive does not understand the contents of the rsd file.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @JogchumSiR 

It would appear that while the Azure AD provider does use the Graph API, it apparently can’t handle RSD files.

If you remove the changes made to point at these files it should start working again.

You can try to use a REST CData provider and using the same App credentials in the OAuth area.

The difference is that you need to apply the following:

OAuth Authorization URL https://login.microsoftonline.com/{TentantId}/oauth2/v2.0/authorize

OAuth Access Token URL https://login.microsoftonline.com/{TentantId}/oauth2/v2.0/token

OAuth Refresh Access Token URL https://login.microsoftonline.com/{TentantId}/oauth2/v2.0/token

And set the scope to .default.

J
Rank

Hi @Thomas Lind,

I will remove the files.
It's a pity that it doesn't support adding extra fields, as we now have to do a whole set of extra work to achieve something that in my opinion should be supported by this data provider.
I will post an idea for this.

 

Thank you for helping with this issue!

sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Hey @Thomas Lind, 

 

I tried to use your RSD files, this worked perfectly for Users and for Groups. But in MembersByGroups we run into a problem, it seems the group-part does not paginate well, so it'll successfully obtain the first page of groups and then obtain those group's members. But it does not loop over the other pages for Groups. 

I tried adding 

<api:set attr="groupin.pageurlpath" value="/\[@odata.nextLink\]" />

as that is used in Groups.rsd, but it seems to ignore it and still only retrieve the 1st page. 

Do you know what can be changed to the RSD file to make it paginate? 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @sierd.zuiderveld 

That is where the managed query comes in. I seem to have missed the Input version. I attached it to this. You can see the setup here.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

	<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
	<api:info title="Members" desc="Generated schema file." other:queryslicercolumn="GroupId" xmlns:other="http://apiscript.com/ns?v1">
		<!-- You can modify the name, type, and column size here. -->
		<attr name="id" 				xs:type="string" 	readonly="false" 	other:xPath="/json/value/id" />
		<attr name="displayName" 		xs:type="string" 	readonly="false" 	other:xPath="/json/value/displayName" />
		<attr name="@odata.type" 		xs:type="string" 	readonly="false" 	other:xPath="/json/value/[@odata.type]" />
		<attr name="mail" 				xs:type="string" 	readonly="false" 	other:xPath="/json/value/mail" />
		<attr name="officeLocation" 	xs:type="string" 	readonly="false" 	other:xPath="/json/value/officeLocation" />
		<attr name="userPrincipalName" 	xs:type="string" 	readonly="false" 	other:xPath="/json/value/userPrincipalName" />
		<attr name="GroupId" 			xs:type="string" 	readonly="false" 	other:filter="{GroupId}" default="21a70927-0e6d-45f5-8ec3-8a9c5eb16594" />
	</api:info>

	<api:set attr="URI" value="https://graph.microsoft.com/v1.0/groups/{GroupId}/members" />
	<api:set attr="DataModel" 		value="DOCUMENT" />
	<api:set attr="EnablePaging" 	value="TRUE" />
	<api:set attr="JSONPath" 		value="$.value" />
	<api:set attr="pageurlpath" 	value="/\[@odata.nextLink\]" />
	<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
	<api:script method="GET">
		<api:call op="jsonproviderGet" out="row">
			<api:set attr="row.GroupId" value="[_input.GroupId | allownull()]" />
			<api:push item="row"/>
		</api:call>
	</api:script>

</api:script>

Notice the <api:info> tag at the top I have added a other:queryslicercolumn=”GroupId” part to it.

This and the last field in there called <attr name="GroupId" xs:type="string" readonly="false"  other:filter="{GroupId}" default="21a70927-0e6d-45f5-8ec3-8a9c5eb16594" /> is what makes it able to work with the managed query below.

SELECT * FROM [REST].[MembersByGroup_Input] WHERE [GroupId] IN (SELECT [Id] FROM [REST].[Groups]

This works with incremental load, the normal one can’t handle more than 20 rows per call.

1 Attachment

Reply


Terms and ConditionsCookie settings