Enumerating AD using Windows Object and Windows Group Member

xms

I'm trying to do get a list of users based on their Active Directory (AD) group. Nav (we are using Nav 5.0) has the following virtual tables that should do the trick:
2000000050 Windows Object
2000000052 Windows Group Member

It should be simply to query Windows Object for the group to get a GUID, and then use that GUID to query Windows Group Member to get the users.. but what happens is it goes nuts and just repeats the users again and again.

I have tried a few different ways, one creating a report with Windows Object at the main level and Windows Group Member on a indented level linked with Group GUID=FIELD(GUID) and filtering on Name... it 'works' but just keeps building..

Alternatively tried code similar to below, where WindowsObject is a Windows Object record and ADGroupMembers is Windows Group Member Record..
WindowsObject.RESET;
WindowsObject.SETFILTER(Name,'=%1',"Users");
WindowsObject.SETFILTER(Type,'=%1','group');

WindowsObject.FIND('-');
ADGroupMembers.SETRANGE("Group GUID",WindowsObject.GUID);

ADGroupMembers.FIND('-');
counter:=0;
REPEAT
AdMemberID := ADGroupMembers."Member ID";
counter:=counter+1;

UNTIL ADGroupMembers.NEXT = 0;

CLEAR(ADGroupMembers);

As usual any help is appreciated!

rsaritzky

Through a little bit of playing around, I discovered that the "join" between Windows Object and Windows Group Member on GUID doesn't work like you would expect it to. My guess is that since these are "Virtual" tables, there isn't really an index that works like you would expect in NAV.

Here's what I did to work around this in a test mode:

1. I created a custom table that to temporarily store the same records/fields as Windows Group Member - Primary key is Group GUID,Member Guid

2. In the first dataitem, I used Windows Group Member. In that OnAfterGetRecord, I inserted a record for each Windows Group Member record I read, e.g.

TGL.INIT;
TGL."Group GUID" := "Windows Group Member"."Group GUID";
TGL."Member Guid" := "Windows Group Member"."Member GUID";
TGL."Member ID" := "Windows Group Member"."Member ID";
TGL."Group ID" := "Windows Group Member"."Group ID:
IF NOT TGL.INSERT THEN
  TGL.MODIFY;

3. The second dataitem in the report is not indented and is Windows Object. I discovered that there is a "repeating" record in Windows Object of type "group" that appears to have a duplicate key (GUID) - there is a record for every individual user. So I created a simple report that listed all the Windows Object records of type "Group" and locate the groupid of "users" and set a filter on the DataItem Properties to exclude this GUID, e.g.

SORTING(GUID) ORDER(Ascending) WHERE(Type=FILTER(group),GUID=FILTER(<>{F11CFB03-F1B8-4976-B336-FACC9D380923}))

4. The third dataitem is the table that temporarily stores the Windows Group Member data, indented and linked to Windows Object table.

The pass of the Windows Group Member table takes a few seconds, depending on how many groups you have - here at my site, there were approximately 2100 group-member records, and it took about 40 seconds to build the temporary table. Then, the rest of the report ran in a few seconds.

Hope this helps