How to query active directory from Board ?

Options
Etienne CAUSSE
Etienne CAUSSE Customer
First Anniversary Advocate Community Voices Name Dropper
in Platform

Hi everyone,

I am currently looking for ways to import in a Board database a few key information about our users, coming from Active Directory.

Basically, our user names are email addresses, and I would like to use AD information to associate the location, company, manager name, etc. to our users. This would be very useful to better analyze the logs for example.

 

Is there any best practice about that ? Currently my best option is to build a query in Excel, export to a CSV and import into Board, but as you can imagine this is not very satisfying.

 

Thanks in advance for all your ideas

Etienne

Tagged:

Answers

  • Robert Barkaway
    Robert Barkaway Employee
    First Anniversary Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Comment
    Options

    Hi, this article would appear to suggest that it's possible to query Active Directory information into a SQL database using linked servers and OPENQUERY, I can't say that I've tried it myself but that might be an option if you have a SQL instance available to use?

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    Options

    Hi Robert,

    I saw this article as well, but I'm not sure what would be the right steps to implement this:

    - should I setup a SQL server alongside the board engine on the server ?

    - if yes, how do I update the local sql server before querying it from Board ? Are you thinking about a scheduled process (i.e. schedule the execution of a refresh query on the SQL server, then schedule a procedure on the Board server) ?

     

    It seems a bit heavy and not very dynamic, but it could be a solution I guess. Any other ideas ?

  • Robert Barkaway
    Robert Barkaway Employee
    First Anniversary Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Comment
    Options

    Hi Etienne,

     

    I wouldn't suggest setting it up on the Board server, no, to be honest it was more a suggestion for if you already have a SQL server set up somewhere in your company which you could use, if not then it probably isn't the most convenient solution    If you did go down that route then you could schedule it all through SQL Agent, the same job could extract the data to either a SQL table or a flat file, and then use the command line call to Board to trigger a procedure for importing the data.

     

    I've just had a quick Google (as I'm sure you've already done yourself), and to be honest it looks a lot like your only other option might be a third party tool for command-line interrogation of AD unfortunately.

     

    If you do get a solution working be sure to share it on here, I'm sure it's something which would be of considerable interest to other community members

     

    Kind Regards,

    Rob

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    Options

    I will of course share if I find anything more dynamic

    I was wondering since the authentication through Windows is possible, if anybody in the lab would have an idea to use that... ?

  • Unknown
    Unknown Active Partner
    Options

    I'd recommend setting up a linked server inside SQL to query Active Directory as a ADSI datasource. Below are links to articles with all the details. At a high level, we can setup a linked server to an AD domain controller to query it like a schema. You may want to setup a view on top of this linked server connection, which you can use in a datareader to get the data into BOARD. As a warning, linked servers can be challenging. In some configurations, the link drops occasionally. 

     

    Microsoft OLE DB Provider for Microsoft Active Directory Service | Microsoft Docs 

     

    https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/ 

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    Options

    Hi Bob Gill and thanks for this suggestion.

     

    I was wondering, since Board is natively capable of using Windows & AD , or even another LDAP source to authenticate itsusers, why is it not possible to query a few additional AD fields at the same time ? Would that be very complicated to implement ?

  • Unknown
    Unknown Active Partner
    Options

    Hi Etienne CAUSSE,

       You raise a very good point. BOARD does have native support for AD and other LDAP tools. I think you have a very good idea here. Since BOARD is already querying AD for group membership, it just needs to be extended to query for other user and group attributes. I've included some links below on all the attributes available for user, group and computer object attributes.

     

     

       In the meantime, you could also use powershell to query AD, but I don't think we can link that to a datareader. If you want to run a powershell script to generate a CSV file each night, then you could use a datareader to import into BOARD. 

     

     

        You might want to write this idea out in the ideas area to see if we can get it into the product. 

     

    Good luck,

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    Options

    Exactly what I was looking for... I'll test that ASAP

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    edited March 2020
    Options

    Hi Fabio Donatellis,

    In the RDBMS field, what am I supposed to setup ? "OLEDB for OLAP" ? Or "General" ? It seems I cannot reach the server when I query it...

    image

  • Fabio Donatellis
    Fabio Donatellis Employee
    First Anniversary Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Answer
    Options

    Hi Etienne CAUSSE

     

    In the RDBM, you must set the field to "General".

    "OledDB for OLAP" is to connect to multidimensional databases like SSAS.

     

    If you use integrated authentication, the system will log in to the domain controller with the user that running the service "Board Engine". This user must be authorized to access and query the AD.

     

    Regards

    Fabio

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    edited March 2020
    Options

    Hi Fabio Donatellis,

    I'm stuck with this message when querying my AD :

    image

    My query is as follows:

    Select displayName, employeeNumber, physicalDeliveryOfficeName, department, company, title, manager,
    from 'LDAP://DC=eu,DC=seb,DC=com'
    WHERE objectClass='user' and objectClass = 'organizationalPerson'

     

    Any obvious mistake ? I'm not very familiar with AD & LDAP.

  • Fabio Donatellis
    Fabio Donatellis Employee
    First Anniversary Level 100: Foundations of Building in Board Level 200: Leveraging Board for Business Insights First Answer
    Options

    No, no obvious error.

     

    Remember to run the query browser from the tab "Manual".

    On the net there are many AD Query tools that might be useful to write the query.

     

    If you can not configure it in any way, open a ticket and send us all the information and screenshots.

    We will try to help you.

     

    Fabio

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    First Anniversary Advocate Community Voices Name Dropper
    Options

    I'll do that, thanks

  • Unknown
    Unknown Active Partner
    Options

    ...linking to a related question

     

    How to query the User List from MS Active Directory

Categories