Drawing An Org Chart From Active Directory (AD)

Why Use Active Directory For the Org Chart?

Organisations that store their hierarchy in Active Directory(AD) can use that information store to produce their organisation chart. Main advantage is that the application displaying the organisation chart does not need to maintain a database of users, their names, job titles and contact details as all these can be retrieved from the AD.

This helps keep consistency with other applications that use Active Directory such as Microsoft SharePoint and Microsoft Exchange.

Linking SQL Server to Active Directory

To start drawing our organisation chart direct from AD first we must access the Active Directory through Microsoft SQL Server. We do this by adding a Liked Server to SQL Server by executing the following command.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject','adsdatasource' GO

For full details please refer to this Microsoft MSDN Article

Querying The Active Directory

After creating a linked server use an OPENQUERY statement to send a query to the Active Directory Service. The following SQL query returns the Common Name of all objects in the Active Directory.

You must replace LDAP://YourCompany.Com.Local with your Active Directory details.

SELECT * FROM OPENQUERY( ADSI, 
     'SELECT cn FROM ''LDAP://YourCompany.Com.Local''
) GO

The field CN stands for Common Name and is one of the fields defined in the Active Directory schema. The two fields most important to drawing an organisation chart are:

• DN or distinguished name. This is a unique name of the current AD object and we can use this as a PrimaryKey in the Org Chart Component.
• Manager this identifies the manager of the current AD object. This can be used as the ParentField in the Org Chart Component.

Full technical details of AD and the available fields can be found on MSDN Here.

Creating a View On Active Directory

Active Directories can contain numberous other types of objects that would not be required in an organisation chart, for example, printers, task accounts and sercurity groups.

To filter these a View can be created containg a Where clause to restrict certain AD items from the result set.

The view below returns a dataset limited to users and is the correct structure to use in the Org Chart Component.

CREATE VIEW dbo.OrgChartFromAD AS
SELECT  
    distinguishedName as UniqueId, 
    Manager as ManagerId, 
    CN as FullName, 
    mail as email

FROM OpenQuery(ADSI,
 'SELECT mail,CN,distinguishedName, Manager 
   FROM ''LDAP://YourCompany.com.local'' 
   WHERE objectCategory=''Person'' AND objectClass = ''User''')
WHERE
    manager is not null OR distinguishedName ='CN=BigBoss,CN=Users,DC=YourCompany,DC=com,DC=local'

Finishing the Chart

Once the view has been created the developer can use it to display an organisation chart by binding the OrgChartComponent to the view. See the tutorials Creating a databound Organisation Chart and Programming Examples for a detailed description.