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.