Traffic analysis with Power Query

I have become quite the fan of the Power Query, the add-in for Excel from Microsoft. Over the last few days we have had concerns about the number of DNS queries our event log collection system was making, Our ArcSight connector appliance tries to enrich events it collects by trying to resolve IP address it finds in the security events. The two ConnApps we focused on were making 600 DNS queries per second. The problem was that the traffic had to go through a firewall to get to the DNS server and all these queries were creating a lot of entries in the firewall connection table.

To analyze the problem we got a packet capture on one of the  ArcSight ConnApps, I loaded the pcap file into netmon and then did a select all in the Frame Summary pane and pasted this into an Excel file. I then created a new Excel file and created a Power Query using the previous Excel file as the data source. Using the split function you can easily get the field you need in its own column (the DNS question). What we are after in this case was what DNS question is being asked and how often. Save the query to the data model (install the Power Pivot add-in too) and create a PowerPivot pivot table. Now you can easily summarize. In our case we had two IPs that were queried for 4000 times in the two minute capture.

What was the fix? Easy. We enabled DNS negative caching. They are now running at 20 queries per second.

Here are some screen shot of the process. I flushed the DNS cache on my laptop to get some data in my capture.

The capture


Pasted in to Excel


Open a Power Query from a blank Excel doc.


Loaded in query editor


After a bunch of splits, filter and column renames got it down to this.


Now we have what we need in the PowerPivot data model. Create your PivotTable (in PowerPivot) and you get this.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s