9 Replies Latest reply on Jan 6, 2012 12:47 PM by brsamuel

    Obtain list of users in AD workgroup from FM - possible?


      Is it possible to query the Active Directory service from inside a database and get a list of users who are in a specified workgroup? Assuming, for the sake of argument, that the database in question has an account attached to that workgroup.


      I have a solution where the customer has requested the ability to flag records where user IDs do not already exist in the workgroup. If there's another way to accomplish this, I'll entertain suggestions.



        • 1. Re: Obtain list of users in AD workgroup from FM - possible?

            I do not think that there is any direct way to do this.  When you use External authentication in FMPro, all you get when a person logs in is a list of groups (and then you match on the group you want).  Thus, FileMaker is not given any information about the group itself -- just that the person who logged in is a member of that group.  You might be able to cobble together a scripted solution that queries the host system via the Smartpill plugin (http://www.scodigo.com/products/smartpill-php/), but that might be more complex than you are looking for.

            Otherwise, you would need to set up several externally authenticated groups in a database file and have the one you are interested in tracking be on top (that is, if 'foo' is the name of the workgroup, have it be the top-most account in the database).  Then, give each group account a different priv set.  If the user logs in and gets the priv set for 'foo' then you know that they belong to 'foo' in AD, etc. 

          • 2. Re: Obtain list of users in AD workgroup from FM - possible?

            Thanks. We already have a series of workgroups, but the problem is not knowing that the current user is a member of group X. Rather, the problem is knowing whether user X is in group X (based on a text user ID) without the admins having to look. Little background: This solution is a document routing system, and any of the 10,000 people on site could potentially be on the review list. The admins basically have to check an outside tool to see if the people they're adding to the routing list have access to the database (i.e. are members of the AD group), which is annoying and time-consuming. They were wanting to have the system do that checking for them.



            • 3. Re: Obtain list of users in AD workgroup from FM - possible?



              I client I work for has Active Directory setup and virtually all FM solutions use External Authentication with this. In one dB, we needed to query the AD, but I seem to recall it was to just get info about a user, not any groups they were a member of. One of the client's staff wrote a VBScript that performed the AD query. We read the resulting returned data into the FM dB. Unfortunately, I don't have access to the client's network at the moment, so I can't look at what was done. When I am able to get into their network, I will look this up and let you know what I find.


              On a related note, the same client had at one time, a SQL dB that was populated with data that was pulled out of the AD on a regular basis. We set up an ESS connection from FM to the SQL dB and were able to get the exact information that you are looking for - group memberships and so on. Maybe something like this exists at your site? For us, the SQL dB was removed some time ago for other reasons, thus us having to find a way to query the AD directly.


              So, what I am suggesting, is for you to see if the client has any IT staff that are familar with VBScript, Java, .NET, etc., that could write the code needed to query the AD. Then you need to get it into the FM dB. The previous response mentioned SmartPill. We thought about that as it does say it has this ability. We did not have any other needs that SmartPill might fill, so we went with the route of VBScripting.




              Doug de Stwolinska

              • 4. Re: Obtain list of users in AD workgroup from FM - possible?

                I would consider talking to the guys at 360Works, or at least using ScriptMaster and finding a Groovy library that works with LDAP.  That should help you query AD and get the info you need.  We don't use AD for anything here at our office, but I've seen a lot of LDAP discussions on the Groovy sites.

                • 5. Re: Obtain list of users in AD workgroup from FM - possible?

                  Yep.  This was why I recommended Smartpill.  It has built-in functions for querying the AD LDAP.  I'm actually thinking of using it myself to help keep some user tables in sync with the LDAP...

                  1 of 1 people found this helpful
                  • 6. Re: Obtain list of users in AD workgroup from FM - possible?

                    If your customer has an intranet (internal web server) you could consider creating an unadvertised internal web page that does the LDAP query and display the results within a web viewer.


                    I've taken this approach with FM and AD as far as user accounts go. I have a table in my FM database with a record for each user.  BTW: I'm using 100% external authentication so the table is not used for security at all, just reference.  In that table I have a field for the user's Windows username.  When a new person is hired I manually enter their Windows login ID into that field.  Then on the layout I have a web viewer that displays a web page that contains the user's AD information.  The key field is obviously the Windows username/ID.


                    This setup allows us to see both AD and FM information about a user from a single layout.  We can also scrape the webviewer to copy information to FM when necessary.


                    The advantage is that this setup is plug-in free.  Since the web page is behind the firewall and created by me I have full control over the format.  I match the colors and fonts on the web page to the colors and fonts on the layout and everybody thinks the content in the webviewer is just native FM data and objects.


                    The LDAP query is a different beast.  Yon can spend a lot of time getting the syntax right, and I mean A LOT OF TIME.


                    I doubt many small shops have intranets so this method won't be feasable for many but I just wanted to offer it up as an alternative.

                    1 of 1 people found this helpful
                    • 7. Re: Obtain list of users in AD workgroup from FM - possible?

                      Thanks. This is close to what I've come up with, which is basically to show the external tool - which is web-based - in a web viewer. Not exactly what they were asking for, but it's better than having to leave FileMaker, come back, leave, come back ... etc., etc. I'll have to see if that flies.



                      • 8. Re: Obtain list of users in AD workgroup from FM - possible?

                        Thanks. The problem I have is our server folks are notoriously squirrely about allowing direct LDAP access. Security. Sigh. But thanks for the suggestion. I'll keep it in mind; maybe I can sweet-talk them.     



                        • 9. Re: Obtain list of users in AD workgroup from FM - possible?

                          Depending upon the OS that the client is running on, you may be able to accomplish what you want with ScriptMaster by 360works.  It includes a function "Run Shell Script".


                          From a Windows command prompt the following command will provide the information that you are looking for (and then some):


                          net group /domain {AD-group-name}


                          (replacing {AD-group-name} with the name of the relevant group.)