Excel for Infosec

When you think about software for information security you probably think of NMAP, Mimikatz, maybe a SIEM or Burp. But I find I spend quite a lot of time taking data from various sources in varying formats and wanting to do some analysis, I'm no data scientist so if I can't achieve it with grep then Excel is probably the next thing in my toolbox.

Most people think of Excel as primarily a numerical or financial tool with the occasional chart or macro for good measure, but recently the functionality built into Excel for querying, parsing and manipulating data has improved a lot and I wanted to share some of the tips/techniques that I've come across in case they are useful to anyone else.

Example

I'm going to take a JSON log of security events, a CSV of login events and an excel spreadsheet inventory. What we're aiming to do is link an event to a host and the relevant user then present this in a clear way that summarizes the security events to a manager or customer. You can download the example files if you want to follow along.

Firstly we need to open up the Power Query Editor, this is in the Data tab under "Get Data"

To get started, I added all 3 data sources to the query editor and did some basic clean up such as removing empty columns, making case consistent and ensured data types were correct.

You can see in the pane on the left each action that you take as well as those taken automatically when the data is ingested such as promoting first row to headers. You can click any of these to go back to that point in the process and you can even make changes to previous actions.

The logins data contains numerous logins per user/computer so I selected the username and hostname fields and then grouped the dataset to remove the repeats.

I then converted the JSON records to a table and expanded it out to columns

Once the 3 data sources are consistent then we're read to merge, click the Merge Queries button at the top and then you can select which columns the join should be based on as well as the join method.

Once the 3 sources are merged you should have a single table showing each event with the relevant hostname and username. Hit the Close & Load button to import this table into your workbook.

The next feature I love in Excel for dealing with data like these is Pivot Tables, people who haven't used them before often assume they add complexity but they actually do the opposite, they take a large set of data and produce a simple overview.

In the table in the workbook hit the "Summarize with Pivot Table" button to start the process, I always choose to locate the new pivot table in a new sheet to make things cleaner.

Your new pivot table will be empty so you need to choose how you want to summarize your data, a good starting point will be to show how many events have occurred per user, per workstation and per event type.

Drag the Username column into the Rows box and the eventID column into the values. Immediately you'll notice that the values column is rather larger than we'd expect because Excel has chosen to Sum the numerical eventIDs rather than counting them. You can Right click and use the Value Field Settings, to change this behavior

Add a couple more tables and tweak a few settings and labels and you'll have something like this.

This gives a much more useful at-a-glance summary of the data. If you add a couple of charts and a slicer you'll be left with a nice report that you can send over to management or a customer.

Best of all this entire report has been compiled without writing a single formula or macro. It's easy to import next weeks data and refresh to output another report.

Although my example dataset is small for convenience, this techniques scales to much larger files. I recently processed ~750,000 lines of Office 365 audit log using similar techniques and then used pivot tables to filter out interesting anomalies. It's easy to pivot from a known compromised account to which IPs have logged in to it, which countries they're from, what other logins have been observed from those IPs and what other activity was observed on those accounts within a 10 minute window.

I really enjoying working with Excel so there will probably be a part 2 of this post soon. Let me know if you've got any tips or tricks for Infosec Excel usage.