Visualising Organisational Charts from Active Directory

Overview

The success of a simulated attack is usually based on a good understanding of the target organisation, both from an infrastructure perspective and from a business perspective; one frequently adopted strategy to achieve a specific goal is to identify the employees involved in the execution of that goal and launch targeted attacks against them. It is often useful to understand the hierarchy and reporting structures too, simply because managers often have access to information that employees do not and because abusing a process involving interaction between an employee and their manager relies on knowing who those individuals are. Most organisations usually have an organogram or organisational chart, whether this be on paper or dynamically generated (for example, through Sharepoint); in most cases, this information is also held in Active Directory. This can be accessed by a low-privileged user by launching ADSI queries that include the manager attribute, which is the distinguished name of the employee's manager. I wrote a metasploit module last year, the details of which are at https://github.com/rapid7/metasploit-framework/pull/6377 which will perform the relevant ADSI queries and export a CSV file containing the relevant output. This can be imported into Visio to statically render an organisation chart, and works very well for small organisations. However, large organisations typically have thousands of users; it is impractical to render the entire organisational chart at once and it is often desirable to be able to dynamically query these relationships, such as identifying:

Who reports to person A?
Who is in person B's team, i.e. reports to them directly or indirectly?
Who are the CEO's direct and indirect reports?
Who else is managed by person C's line manager?

This type of information is useful when planning more targeted attacks or simply to gain a better understanding of the organisational structure. However, performing the above queries is natively difficult because each user has a single user to manager relationship, and any sort of analysis needs to involve traversing the hierarchy. Ideally, this would happen offline to enable plans to be constructed without interacting directly with the target network. This post describes and demonstrates:

How to use the generate_csv_org_chart metasploit module in conjunction with Visio to draw a static organisational chart.
How to use ADOffline in conjunction with neo4j to use the power of graph databases to perform various queries and dynamically draw the organisational chart hierarchy.

I have used CSV files as the method of transporting data between the systems. It is not the most efficient but does illustrate how information can be interchanged and how to apply these techniques to other scenarios. It is written from the perspective of a simulated attacker looking to gain a better understanding of the organisation's hierarchy and who has been able to obtain low-privileged access to the target, for example through a successful phishing attempt.

Scenario

For the purposes of this example, the fictitious STUFUS organisation has been compromised; an implant has been successfully placed on a fully patched and updated workstation running as a low privileged user. In this organisation, the usernames are actually the same as the job titles (for example, the MD's username is managing.director) simply because it is easier to relate to the scenario than making up random names.

Metasploit & Visio

The example below shows how to retrieve user and manager data from AD using the generate_csv_org_chart module and render it using Visio; further details are available at https://github.com/rapid7/metasploit-framework/pull/6377.

[*] https://192.0.2.161:8443/ handling request from 192.0.2.180; (UUID: f7eszpm8) Staging Native payload ...
[*] Meterpreter session 1 opened (192.0.2.161:8443 -> 192.0.2.180:59386) at 2016-04-30 13:32:18 +0100

msf exploit(handler) > use post/windows/gather/generate_csv_org_chart
msf post(generate_csv_org_chart) > set SESSION 1
SESSION => 1
msf post(generate_csv_org_chart) > show options

Module options (post/windows/gather/generate_csv_org_chart):

Name Current Setting Required Description
---- --------------- -------- -----------
ACTIVE_USERS_ONLY true yes Only include active users (i.e. not disabled ones)
DOMAIN no The domain to query or distinguished name (e.g. DC=test,DC=com)
FILTER no Additional LDAP filter to use when searching for users
MAX_SEARCH 500 yes Maximum values to retrieve, 0 for all.
SESSION 1 yes The session to run this module on.
STORE_LOOT true yes Store the organisational chart information in CSV format in loot
WITH_MANAGERS_ONLY false yes Only users with managers

msf post(generate_csv_org_chart) > run

Users & Managers
================

cn description title phone department division e-mail company reports_to
-- ----------- ----- ----- ---------- -------- ------ ------- ----------
Administrator Built-in account for administering the computer/domain
Finance Manager Head of Finance finance.manager@stufus.lan Director Alpha
...snip...
[*] CSV Organisational Chart Information saved to: /usr/home/s/stuart/.msf4/loot/20160430133836_stufus_192.0.2.180_ad.orgchart_566699.txt
[*] Post module execution completed
msf post(generate_csv_org_chart) > cat /usr/home/s/stuart/.msf4/loot/20160430133836_stufus_192.0.2.180_ad.orgchart_566699.txt
[*] exec: cat /usr/home/s/stuart/.msf4/loot/20160430133836_stufus_192.0.2.180_ad.orgchart_566699.txt

cn,description,title,phone,department,division,e-mail,company,reports_to
"Administrator","Built-in account for administering the computer/domain","","","","","","",""
"Finance Manager","","Head of Finance","","","","finance.manager@stufus.lan","","Director Alpha"
"Finance User","","General Finance Person","","","","finance.user@stufus.lan","","Finance Manager"
"Director Alpha","","Director of IT and Finance","","","","director.alpha@stufus.lan","","Managing Director"
"Director Bravo","","Director of Grunts","","","","director.bravo@stufus.lan","","Managing Director"
"IT Manager","","Head of IT","","","","it.manager@stufus.lan","","Director Alpha"
"IT User","","General IT Support","","","","it.user@stufus.lan","","IT Manager"
"Dogsbody One","","General Dogsbody","","","","dogsbody.one@stufus.lan","","Grunt Manager"
"Dogsbody Two","","General Dogsbody","","","","dogsbody.two@stufus.lan","","Grunt Manager"
"IT Manager (Admin Account)","","","","","","it.manager-admin@stufus.lan","",""
"IT User (Admin Account)","Password is Pa$$w0rd1 - do not change","","","","","it.user-admin@stufus.lan","",""
"Grunt Manager","","Head of Dogsbody Team","","","","grunt.manager@stufus.lan","","Director Bravo"
"Managing Director","","Managing Director","","Board","","managing.director@stufus.lan","",""
"Payroll Manager","","Head of Payroll","","","","payroll.manager@stufus.lan","","Finance Manager"
"Payroll Deputy Manager","","Assistant Payroll Lead","","","","payroll.deputy.manager@stufus.lan","","Payroll Manager"
"Payroll Team Leader","","","","","","payroll.team.leader@stufus.lan","","Payroll Manager"
"Payroll User","","","","","","payroll.user@stufus.lan","","Payroll Manager"
"Payroll Trainee","","","","","","payroll.trainee@stufus.lan","","Payroll Team Leader"
"Dogsbody Three","","","","","","dogsbody.three@stufus.lan","","Grunt Manager"
"Dogsbody Four","","","","","","dogsbody.four@stufus.lan","","Grunt Manager"
"NonExec One","","","","","","nonexec.one@stufus.lan","","Managing Director"
"NonExec Two","","","","","","nonexec.two@stufus.lan","","Managing Director"
"ManagingDirector PA","","","","","","managing.director.pa@stufus.lan","","Managing Director"
"Alpha PA","","","","","","director.alpha.pa@stufus.lan","","Director Alpha"
"Bravo PA","","","","","","director.bravo.pa@stufus.lan","","Director Bravo"
msf post(generate_csv_org_chart) > sessions -i 1
meterpreter > getuid
Server username: STUFUS\dogsbody.one

The CSV format is relatively self-explanatory; the last column shows the CN of the manager. This does make the assumption that the CN field is unique (i.e. that two employees do not have the same name). To import this into Visio, the Organization Chart Wizard can be used:

Following the steps with their default options will result in the box below being displayed. This is because there are 3 users that do not have a manager and do not report to anyone (the Administrator user and the IT admin accounts). Press Yes.

This resulted in 4 pages; the 3 users above on separate pages and the bulk organisation chart on the last one.

If suitable filtering is performed using the FILTER parameter, this may result in a manageable organisational chart. However, this option does not scale from a rendering perspective, nor can dynamic queries be performed on this.

ADOffline & Neo4j

Another option is to use the parsed information from ADOffline (https://github.com/stufus/ADOffline and https://labs.mwrinfosecurity.com/blog/offline-querying-of-active-directory/). In order to extract organisational chart information from the ADOffline database, it is necessary to:

Obtain a list of users whose manager field is not NULL.
As the manager field is a DN, obtain the common name associated with the manager.
Include anyone who does not have a manager but who manages other people.
Return a number of key fields (DN, title and CN) because the unique identifier for a user in this instance is the DN, not the CN (because two people may have the same name)

The following query will retrieve the distinguished name of each user and manager, the common name of the user and their manager and the title of the user and their manager:

select u.dn,u.cn,u.title,m.dn,m.cn,m.title from view_users u LEFT JOIN view_users m ON u.manager = m.dn where u.dn IS NOT NULL and (m.dn IS NOT NULL OR u.dn IN (select manager from view_users where manager IS NOT NULL))

ADOffline now creates a view (view_orgchartusers) of this query automatically by executing the SQL code below, meaning that the query below:

select * from view_orgchartusers

will deliver identical results.

sqlite> .schema
...snip...
CREATE VIEW view_orgchartusers AS select u.dn as u_dn,u.cn as u_cn,u.title as u_title,m.dn as m_dn,m.cn as m_cn,m.title as m_title from view_users u LEFT JOIN view_users m ON u.manager = m.dn where u.dn IS NOT NULL and (m.dn IS NOT NULL OR u.dn IN (select manager from view_users where manager IS NOT NULL));
...snip...
sqlite> select * from view_orgchartusers;
CN=Director Alpha,OU=Directors,DC=stufus,DC=lan|Director Alpha|Director of IT and Finance|CN=Managing Director,OU=Directors,DC=stufus,DC=lan|Managing Director|Managing Director
CN=Alpha PA,OU=Directors,DC=stufus,DC=lan|Alpha PA||CN=Director Alpha,OU=Directors,DC=stufus,DC=lan|Director Alpha|Director of IT and Finance
CN=Director Bravo,OU=Directors,DC=stufus,DC=lan|Director Bravo|Director of Grunts|CN=Managing Director,OU=Directors,DC=stufus,DC=lan|Managing Director|Managing Director
...snip...

Field	Description
u_dn	Distinguished name of the user
u_cn	Common name of the user
u_title	User's title
m_dn	Distinguished name of the user's manager
m_cn	Common name of the user's manager
m_title	The manager's title

Using SQLite, the following commands will output the results and headers to a CSV file.

sqlite> .headers on
sqlite> .mode csv
sqlite> .once /tmp/stufus_chartcsv
sqlite> select * from view_orgchartusers;

This CSV can then be imported, creating the user-manager relationship, into neo4j using the neo4j shell:

$ ./neo4j start-no-wait
Starting Neo4j Server...WARNING: not changing user
process [17568]...Started the server in the background, returning...
$ ./neo4j-shell -host 127.0.0.1
Welcome to the Neo4j Shell! Enter 'help' for a list of commands
NOTE: Remote Neo4j graph database service 'shell' at port 1337

neo4j-sh (?)$ CREATE INDEX ON :Person(dn);
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+
96 ms
neo4j-sh (?)$ CREATE INDEX ON :Person(title);
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+
17 ms
neo4j-sh (?)$ CREATE INDEX ON :Person(name);
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+
8 ms
neo4j-sh (?)$ USING PERIODIC COMMIT
> LOAD CSV WITH HEADERS FROM "file:///tmp/stufus_chart.csv" AS line
> WITH line
> WHERE line.m_dn IS NOT NULL and line.u_dn IS NOT NULL
> MERGE ( u:Person { dn: line.u_dn, title:coalesce(line.u_title,''), name:coalesce(line.u_cn,''), full:coalesce(line.u_cn,'')+' ['+coalesce(line.u_title,'')+']' })
> MERGE ( m:Person { dn: line.m_dn, title:coalesce(line.m_title,''), name:coalesce(line.m_cn,''), full:coalesce(line.m_cn,'')+' ['+coalesce(line.m_title,'')+']' })
> CREATE (u)-[:REPORTS_TO]->(m);
+-------------------+
| No data returned. |
+-------------------+
Nodes created: 22
Relationships created: 21
Properties set: 88
Labels added: 22
1156 ms

The code above performs a number of tasks; firstly, database indices are created. This is simply to speed queries up later. Firstly, a Person label is being indexed with three attributes; dn, title and name:

CREATE INDEX ON :Person(dn);
CREATE INDEX ON :Person(title);
CREATE INDEX ON :Person(name);

The code below reads the CSV and creates (but will not duplicate) information about each user and manager as a Person. The CREATE clause has the effect of linking the user to their manager. This is how neo4j's graph database can keep track of who reports to whom.

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///tmp/stufus_chart.csv" AS line
WITH line
WHERE line.m_dn IS NOT NULL and line.u_dn IS NOT NULL
MERGE ( u:Person { dn: line.u_dn, title:coalesce(line.u_title,''), name:coalesce(line.u_cn,''), full:coalesce(line.u_cn,'')+' ['+coalesce(line.u_title,'')+']' })
MERGE ( m:Person { dn: line.m_dn, title:coalesce(line.m_title,''), name:coalesce(line.m_cn,''), full:coalesce(line.m_cn,'')+' ['+coalesce(line.m_title,'')+']' })
CREATE (u)-[:REPORTS_TO]->(m)

Effectively, the 'Person' label contains the following attributes:

Attribute	Description
dn	Distinguished name
name	Common Name
title	Title
full	Common name & title aggregated in the form "Common Name [Title]")

Now, the data and the relationship can be queried. For example, each user-manager relationship can be identified:

neo4j-sh (?)$ MATCH ()-[r:REPORTS_TO]->() RETURN r;
+---------------------+
| r |
+---------------------+
| :REPORTS_TO[3400]{} |
| :REPORTS_TO[3170]{} |
| :REPORTS_TO[3249]{} |
...snip...

The full reporting chain of the user that was compromised (in this case Dogsbody One) can also be identified.

neo4j-sh (?)$ MATCH (p:Person { name: 'Dogsbody One' })-[r:REPORTS_TO*1..]->(m:Person) RETURN p, r, m;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| p | r | m |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Node[3489]{name:"Dogsbody One",full:"Dogsbody One [General Dogsbody]",dn:"CN=Dogsbody One,OU=Grunts,DC=stufus,DC=lan",title:"General Dogsbody"} | [:REPORTS_TO[3476]{}] | Node[3494]{name:"Grunt Manager",full:"Grunt Manager [Head of Dogsbody Team]",dn:"CN=Grunt Manager,OU=Grunts,DC=stufus,DC=lan",title:"Head of Dogsbody Team"} |
| Node[3489]{name:"Dogsbody One",full:"Dogsbody One [General Dogsbody]",dn:"CN=Dogsbody One,OU=Grunts,DC=stufus,DC=lan",title:"General Dogsbody"} | [:REPORTS_TO[3476]{},:REPORTS_TO[3481]{}] | Node[3486]{name:"Director Bravo",full:"Director Bravo [Director of Grunts]",dn:"CN=Director Bravo,OU=Directors,DC=stufus,DC=lan",title:"Director of Grunts"} |
| Node[3489]{name:"Dogsbody One",full:"Dogsbody One [General Dogsbody]",dn:"CN=Dogsbody One,OU=Grunts,DC=stufus,DC=lan",title:"General Dogsbody"} | [:REPORTS_TO[3476]{},:REPORTS_TO[3481]{},:REPORTS_TO[3473]{}] | Node[3505]{name:"Managing Director",full:"Managing Director [Managing Director]",dn:"CN=Managing Director,OU=Directors,DC=stufus,DC=lan",title:"Managing Director"} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows
55 ms

Although accurate, the above output is not particularly helpful. However, neo4j's rendering capability will transform this. Visiting http://localhost:7474/ and running the same initial query:

MATCH ()-[r:REPORTS_TO]->() RETURN r

displays the full organisation chart; in effect, it is showing everyone who has a 'REPORTS_TO' relationship with anyone else.

Selecting the 'full' caption shows the concatenated name and title:

The point about this is is that it is dynamic; queries can be launched with the results rendered in real time. I have provided a number of examples to illustrate the different queries that can be launched, and the results of them.

Examples

Show everyone who reports to the managing director

This will search the database for everyone who reports directly to the managing director. Both queries below will provide the same output:

MATCH ()-[r:REPORTS_TO]->(Person { name: 'Managing Director' })
RETURN r;

MATCH ()-[r:REPORTS_TO]->(p:Person)
WHERE p.name = "Managing Director"
RETURN r;

Show everyone who indirectly reports to the managing director

This will search the database for everyone who either reports directly to the managing director, or who reports to someone who reports to the managing director. The maximum 'indirect reporting' chain is one level deep in this example.

MATCH ()-[r:REPORTS_TO*1..2]->(p:Person)
WHERE p.name = "Managing Director"
RETURN r

Show full reporting chain for Dogsbody One

This will show the full management chain starting with Dogsbody one

MATCH (Person { name: 'Dogsbody One' })-[r:REPORTS_TO*1..]->()
RETURN r

Find everyone who Grunt Manager cares about

The queries below will find everyone who 'Grunt Manager' cares about, either because they directly report to him or because he directly reports to them. They do differ in efficiency, which will become visible over large data sets.

MATCH (p:Person)-[r:REPORTS_TO*1..1]->(y:Person)
where p.name = 'Grunt Manager' or y.name = 'Grunt Manager'
RETURN r

MATCH r = (p:Person {name:'Grunt Manager'})--() RETURN r

MATCH (p:Person)-[r:REPORTS_TO]-() WHERE p.name='Grunt Manager' RETURN r

The query below will find everyone who 'Grunt Manager' cares about, including their full reporting chain too.

MATCH (p:Person)-[r:REPORTS_TO*1..]->(y:Person)
where p.name = 'Grunt Manager' or y.name = 'Grunt Manager'
RETURN r

Display the full reporting chain of everyone with IT in their title

Both queries below will provide the same output; one uses CONTAINS and the other uses a case-insensitive regular expression.

MATCH (p:Person)-[r:REPORTS_TO*1..]->()
where p.title CONTAINS 'IT'
RETURN r

MATCH (p:Person)-[r:REPORTS_TO*1..]->()
where p.title =~ '.*(?i)IT.*'
RETURN r

Show the direct reports for anyone who is in the 'Grunts' OU

MATCH (p:Person)-[r:REPORTS_TO]->()
WHERE p.dn CONTAINS 'OU=Grunts'
RETURN r

Show the full management chain starting with everyone who's DN matches the regular expression /OU=pay/i

MATCH (p:Person)-[r:REPORTS_TO*1..]->()
WHERE p.dn =~ '(?i).*OU=pay.*'
RETURN r

Show the direct reports to anyone who has 'Head of' somewhere in their title using a regular expression

MATCH (p:Person)-[r:REPORTS_TO]->()
WHERE p.title =~ '.*Head of.*'
RETURN r

Show the number of staff per person

This will show a table of all 'indirect' reports - i.e. for each person, look at how many others report to them either directly or by reporting to an intermediate manager.

MATCH ()-[r:REPORTS_TO*1..]->(p:Person)
RETURN p.name as Name,count(r) as NumIndirectReports ORDER BY p.name

A very similar query, shown below, will show the number of direct reports per person.

MATCH ()-[r:REPORTS_TO]->(p:Person)
RETURN p.name as Name,count(r) as NumIndirectReports ORDER BY p.name

Show the full management chain ending with 'Director Alpha'

MATCH ()-[r:REPORTS_TO*1..]->(Person { name: 'Director Alpha' }) return r

Conclusion & Further Work

Graph databases are a very powerful way of querying and visualising hierarchical data. In this case, the static organisation chart would be more than sufficient to understand the layout of the business, but would not be suitable for an organisation with thousands of employees. However, some relationships become intuitively obvious when rendered on screen which could prove very useful when looking to perform internal social engineering activities or attempting to identify high-value targets. In one recent engagement, there were a number of manual processes that needed to be followed in order to achieve a goal, some of which needed manager intervention at a certain level. An understanding of the business hierarchy was critical in demonstrating that this process could be subverted by attacking specific individuals' workstations. This principal could equally be applied to group membership or access rights; Ben Campbell has (separately) done a lot of very interesting research on this.

Like most databases, there are multiple ways of performing the same query; in some cases, these will only differ in efficiency. These example queries will not be the most efficient, but do illustrate the basic capabilities of neo4j in this context.