Excel for Security Analysts

  • [アーティクル]

The demo I will share with you today is something that I was itching to try for a while.

Before I joined Microsoft and Excel team, I had spent 14 years in developing Network & cyber Security products. Whether we had been developing secure web gateway, innovating in the realm of Security Information & Event Management (SIEM), or battling cybercrime with Big Data & Machine Learning, one thing was always in common – You start with logs. Lots of logs, and sometimes you end up with Excel. 

Detecting cybercrime and advance threats is not an easy task, but the adversaries will eventually leave their traces, and these traces can be found in logs. There are plenty of great products and technologies out there that help Security Analysts to defend their organizations. But here is an over-simplification of all these advanced detection technology:

 

Excel is mainly used in these solutions to start a forensic investigation with small portion of the log data, or prepare customized reports to the chief security officer.

But Excel can do so much more, and here is an over-simplification of it:

 

With the help of Power Query and Power Pivot you can import millions of rows, shape the data, and load it into the Data Model for PivotTable and PivotChart analytics.

In this series of blog posts dedicated for Security Analysts – we will unleash the power of Excel to battle cybercrime and save the day for the Security analyst. For those of you who are BI professionals, and know all the tricks, get ready to make new friends in your Information Security team.

 

Episode 1: From web traffic logs to powerful dashboard

In today’s post we will show you how to turn web traffic logs (W3C Extended Log File Format) into a powerful Excel workbook.

Web traffic logs are generated by web proxies and application firewalls to track inbound and outbound traffic.

Each line in the log represents a web request and its response along with their attributes. 

We will use a ~60MB log sample here that was shared by Dr. Anton Chuvakin for learning purposes.

 

 

Using Excel 2016 Preview, we will import the file, shape it with Power Query, load it to the Data Model and build powerful analytics tools with PivotTables and PivotCharts.

Start the walkthrough

 

 

 

Web Logs - Security Analysis.xlsx

Comments

  • Anonymous
    July 18, 2015
    In this walkthrough we will show step-by-step how to turn web traffic logs ( W3C Extended Log File Format
  • Anonymous
    July 22, 2015
    You can now watch this demo in SQLPass webcast: http://youtu.be/XRWq6AAyzbQ
  • Anonymous
    July 26, 2015
    In today's post, there is a nice Power Query trick to transform multiline record data into a table
  • Anonymous
    July 30, 2015
    Excel for Security Analysts is a series of blog posts for Security, Data and BI analysts. In this series