Visualizing BloodHound Data with PowerBI — Part 2

Intro

In the previous blog post, we looked at how to export data from the Neo4j web console into a CSV, format that CSV and save it as an XLSX, import that XLSX table data into PowerBI, then create a chart using PowerBI. That process is great for creating one-off, great-looking charts, but going through that process every time something in the database updates quickly becomes very tedious.

In this blog post, I’ll show you how to use the beta Neo4j connector for PowerBI created by Chris Skardon to create charts that can be easily updated by simply hitting the “refresh” button in the PowerBI GUI.

Step 1: Get the PowerBI Neo4j Connector

Grab the PowerBI Neo4j connector from:

• https://github.com/cskardon/Neo4jDataConnectorForPowerBi/releases/latest

Next, scroll down and download “Neo4j.mez”:

Place this file into the following location:

C:\Users\<your user>\Documents\Power BI Desktop\Custom Connectors

If that folder location doesn’t exist, create it, as Power BI will look in this location for custom connectors. When this step is successfully completed, it should look what is shown in the following screenshot:

Open PowerBI. It will warn you about “Unverified Connectors”. I imagine this is because Neo4j’s connector is still in beta and hasn’t been certified by Microsoft yet. Hopefully that happens soon, because this connector makes this process SO easy compared to your other options. Click “OK” on this window:

To get rid of this warning, go to File -> Options. Click options, then go to the Security tab. Select the radio button for the option that says “(Not Recommended) Allow any extension to load without validation or warning”, then click “OK”:

At this point the Neo4j data connector is now ready to use. You’ll want to periodically check the GitHub repo to see about updates to the connector as well. Now you’re ready for Step 2.

Step 2: Get Data from BloodHound

For this step, you want to already have your cypher query completed and verified by checking the output in the Neo4j web console. Click “Get Data”, then under the “All” tab, find the “Neo4j (beta)” connector. Select the Neo4j connector and click “OK”:

The connector will warn you about “connecting to a third party service”. Click “Continue”:

Now you’ll see the box that takes your cypher query as input. We’re going to use the same query we used in the first blog post:

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

The Neo4j connector’s query field will not accept line breaks, so you need to put your query all into one line. Don’t worry though, cypher works just fine with or without line breaks:

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

Copy the snippet of code shown above and paste it into the “Cypher” field as shown in the next screenshot. For “scheme”, select “http”. You’ll then need to enter your Neo4j server’s IP address in the “Address” field. For this example you can use our public-facing example database, which is hosted at 206.189.85.93. Enter 7474 for the port, then click OK:

Next, enter the username and password for your Neo4j instance as shown in the following screenshot. For the example database, the username is “neo4j” and the password is “BloodHound”. Then click “Connect”:

The connector will connect to Neo4j, run the query, parse the JSON output for you (thank you so much Chris!), and then present you a preview window of the parsed data. At this point you want to verify you’re getting output that looks somewhat similar to what you see in the Neo4j web console. If it looks good, you’re ready to proceed, but don’t hit OK yet, we need to do some quick formatting.

Step 3: Format the Data

In the first blog post I mentioned that PowerBI expect your data to be properly formatted. It also expects your data types to be defined. It seems as if Excel, or the import process from Excel tables into PowerBI automatically defines integers, but unfortunately the beta Neo4j connector does not detect and define integers, or PowerBI doesn’t do that the same way it does when getting data from an Excel table. Either way, this step is very easy. In the preview pane showing what your parsed data looks like, click the “Edit” button as shown in the following screenshot:

This brings you to the query editor. In this example, our column of integers is called “AdminRightCount”. Immediately to the right of the column title is an icon that reads “ABC 123”. Click the “ABC 123” button as shown in the next screenshot, then select “123 Whole Number”. This will force PowerBI to recognize this data as integers, which is necessary when building the chart:

Hit “Close & Apply” as shown in the next screenshot. This will also save the query:

Now the query is saved, and if you want to get fresh data you only have to hit the “Refresh” button in the PowerBI GUI. Our next and final step is to build the actual chart.

Step 4: Build the Chart

This process is the exact same as in the previous blog post, but I’ll share it here as well. To the right you should notice your query and resulting data titles under the “Fields” column. Under “Visualizations”, click the type of visualization you want to create. In this example we’ll create a “Stacked bar chart”:

Clicking that will create a blank placeholder chart. Resize the chart to a decent size, and click the placeholder chart so that it has focus:

Next, click the check boxes next to your data fields. This will automatically create the bar chart, which at first will not look 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

Now your chart will look pretty good:

And you’re done! At this point you can now quite simply hit the “Refresh” button at the top of the PowerBI window, which will rerun the query and update the chart, without needing to format data or edit the visual aspects of the chart beyond any minor tweaking you may want to do.

Using the PowerBI workbook can be really great for creating nice looking charts for your reports in a very easy way, and it’s good for doing a before and after analysis when using the adversary resilience methodology. It’s served us well for our assessments, and we hope it’ll help you too.

Once you get up to speed, you can create more meaningful workbooks that give you lots of good data in one easy space:

Questions? Email me at andy@specterops.io or find us in the BloodHound Slack, we’re happy to help however we can. Get your invite for the BloodHound Slack here: https://bloodhoundgang.herokuapp.com/