Visualizing BloodHound Data with PowerBI — Part 1

Intro

Data visualization is a powerful tool for communicating ideas and information — an effective chart, graph, or diagram is worth a thousand words. In the world of attacking and securing Active Directory domains and forests, the right graph visualization can be worth much more: easy attack path discovery and execution for attackers, and much simpler privilege and permission auditing for defenders. But while graphs are hugely powerful and we are obviously big fans of graphs, a graph is not the right tool for every job.

In this blog post, I’ll show you how you can use BloodHound data, the Cypher query language, and Microsoft’s PowerBI to create elegant data visualizations that will help reveal and communicate security-related insights about your Active Directory domains.

Let’s see if we can answer a very simple question: which are the most privileged security groups in my Active Directory domain? A few obvious contenders probably come to mind: Domain Admins, Enterprise Admins, Administrators, etc. And if you’ve read this excellent post by Sean Metcalf, you know to watch out for Account Operators, Server Operators, Backup Operators, and more. These are great groups to audit, but they’re almost never the most privileged groups in the Active Directory domains we’ve assessed. Sometimes they’re not even in the top 10.

Here’s the chart we’re going to build in this blog post. The numbers here are real, but the names of the groups are changed:

Above: The most highly privileged groups in our Active Directory, based on the number of computers each group has local admin rights to. Highlighted in red is the Domain Admins group, the only built-in group we’d expect to see with this level of privilege.

A few things jump out immediately from this chart:

1. Our Domain Admins group isn’t the most privileged group in AD. It’s barely even in the top five.
2. For some reason, a SOC group has local admin rights on nearly twice as many computers as the Domain Admins group does.
3. Despite having Helpdesk groups split into different tiers (usually done to limit privileges for lower level helpdesk staff), each tier essentially has the same level of privilege in the domain.

This chart gives us some cool insights, and serves as a jumping off point for further analysis (what’s going on with those helpdesk groups?). The BloodHound data uniquely lets us quantify this information, and in three steps we can go from populated database to the chart you see above:

Step 1: Query the BloodHound Database

BloodHound’s data lives in a Neo4j database, and the language you use to query that database is called Cypher. Rohan has a great Intro to Cypher blog post that explains the basic moving parts of Cypher. There are several ways to interface with Neo4j using various official drivers, including drivers in .NET, Java, Javascript, Python, and Go. But for this example, we are going to use the simplest method: the Neo4j web console.

If you’re running Neo4j locally, you can access the web console in a browser at http://localhost:7474/. Authenticate to the database, and then you’ll be ready to run queries:

Now we’ll construct the Cypher query that tells us the name of each security group in Active Directory and the number of computers that group has local admin rights on. Here’s our full query:

MATCH (g:Group)
OPTIONAL MATCH (g)-[:AdminTo]->(c1:Computer)
OPTIONAL MATCH (g)-[:MemberOf*1..]->(:Group)-[:AdminTo]->(c2:Computer)
WITH g, COLLECT(c1) + COLLECT(c2) AS tempVar
UNWIND tempVar AS computers
RETURN g.name AS GroupName,COUNT(DISTINCT(computers)) AS AdminRightCount
ORDER BY AdminRightCount DESC

Let’s explain this a bit with in-line comments:

MATCH (g:Group)
// Match any “Group” labeled node in the database. In BloodHound databases, this means any Active Directory security group. Assign the matching nodes to the variable g.
OPTIONAL MATCH (g)-[:AdminTo]->(c1:Computer)
// Optionally match on any instance of a group being connected to a “Computer” labeled node via an “AdminTo” edge. For each group in variable g, assign the matching computer to the variable c1. We use OPTIONAL MATCH because if the group has no explicit local admin rights, the query will bail out at this point for the current group and not continue.
OPTIONAL MATCH (g)-[:MemberOf*1..]->(:Group)-[:AdminTo]->(c2:Computer)
// Optionally match on any instance of a group belonging to another group, regardless of the level of group nesting, and find computers where the group in variable g has admin rights via this security group delegation. Assign those computers to the variable c2.
WITH g, COLLECT(c1) + COLLECT(c2) AS tempVar
// Carry over our variable g, and create a new variable called tempVar. tempVar is the full list of each Computer node that matched in the above lines.
UNWIND tempVar AS computers
// Transform our list of computers into rows in preparation for the next step
RETURN g.name AS GroupName,COUNT(DISTINCT(computers)) AS AdminRightCount
// Return two columns. One called GroupName, which is the “name” of each group stored in variable g. The second column called “AdminRightCount”, which is the number of unique computers in the tempVar variable. Cypher is smart enough to keep track of which rows are associated with which group in variable g.
ORDER BY AdminRightCount DESC
// Sort the output in descending order by the number of computers each group has admin rights on, which is the AdminRightCount column.

This query may take a long time to finish, as it’s extremely memory and processor intensive. If you find yourself staring at the spinning Cypher circle for too long, go with this simpler, but not nearly as accurate query instead:

MATCH (g:Group)
OPTIONAL MATCH (g)-[:AdminTo]->(c:Computer)
RETURN g.name AS GroupName,COUNT(DISTINCT(c)) AS AdminRightCount
ORDER BY AdminRightCount DESC

Your output should look something like this:

That leads us to step 2.

Step 2: Format the Data

PowerBI is great as a “what you see is what you get” data visualization tool, and it expects your data to be properly formatted. This is a pretty simple process. First, export the data from your query by mousing over the “export” icon, then clicking “Export CSV”:

Open the CSV in Excel, highlight the data, click “Format as Table”, and choose a color style (doesn’t matter what colors you choose):

In this popup, make sure you select “My table has headers”:

Now your spreadsheet should look like this:

Save this file as an XLSX, and you’re ready for step 3.

Step 3: Build the Chart with PowerBI

If you haven’t already, download and install PowerBI Desktop. Open PowerBI, then get past the logon prompts it puts you through. Click “Get Data”, then “Excel”:

Find the XLSX file you created in step 2 in the common dialogue window, then click “Open”. PowerBI will do some processing to find and validate the data in your spreadsheet. On the next window, you need to select one of the options that contains your data. Typically I’m able to just choose an option that matches the XLSX filename. Verify your data is rendering in the preview pane, then click “Load”:

Now you should see this, where the data you imported is available as a resource in the “Fields” column to the right:

Next, click on the type of chart you want to make with this data. I like to go with the “Stacked bar chart”, which creates a nice horizontal bar chart. Click that button, then a blank placeholder representing the chart will appear in the PowerBI drawing area:

Click the placeholder chart, then in the “Fields” column, click the checkbox next to “GroupName”, followed by the checkbox next to “AdminRightCount”. PowerBI will render your chart, which at first will not look quite perfect:

In the “Visualizations” tab, click the “Format” button, which looks like a paint roller. In this section, you can modify almost every visual aspect of the chart. I usually do the following:

Under Y-Axis:

• Change color to black
• Increase font size as much as possible

Under X-Axis:

• Change color to black
• Increase font size as much as possible
• Change gridline colors to black
• Increase stroke width to 2

Under data colors:

• If there is one row I want to highlight, enable “Show all”, then choose a good color for that row

Enable data labels, then under data labels:

• Change color to black
• Increase font size as much as possible

Turn title off

Doing all of that will then make the chart look like this:

This looks much better, but you of course have many options available to make it look even better. In the next blog post, I’ll show you how to connect PowerBI to your Neo4j database for automatic chart updates.