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

Image

Pasted in to Excel

Image

Open a Power Query from a blank Excel doc.

Image

Loaded in query editor

Image

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

Image

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

Image

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s