Chants of big data & bigger insights


Cyber Security

Excel for Security Analysts – Episode 2 – Import Multiline Logs

(By: Gil Raviv. Originally published here).

In today’s episode on Excel for Security Analysts, we will import SSH logs to Excel. To read more episodes of Excel for Security Analysts, go here.

The idea for this post came from a TechNet forum thread that started by a Data Analyst here (Thank you Rajender for sharing the challenge and the logs).

You start with a log file like this:

And you should end up with a table in Excel:

Continue reading “Excel for Security Analysts – Episode 2 – Import Multiline Logs”


Excel for Security Analysts – Intro

(Originally published here)
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


Blog at

Up ↑