CODEWITHSUNDEEP
azurepowerquerylogparserexcel-2016iis-logs
Rory
Rory

Reputation: 41827

How to transform IIS logs using Log Parser (or other) within Power Query?

Power Query / Excel 2016 has a great ability to pull data from places like Azure Storage and can even expand blob files into text data rows. But IIS log files aren't trivial to parse (e.g. have header records and comments) so I'd like to use something else like Log Parser to do the parsing and convert my blobs with iis logs into data rows with columns I can filter and pivot on etc. How would I go about doing this, or is there any library I can use that's already done it?

Upvotes: 1

Views: 2633

Answers (5)

Joy George Kunjikkuru
Joy George Kunjikkuru

Reputation: 1528

If you don't have the luxury of Azure or other paid log analytics services, you may try PowerBI desktop dashboards for IIS log analysis. We can convert the IIS logs into CSV using LogParser then load into PowerBI. More details can be found below.

Power BI Desktop Dashboard for IIS Log Analysis - [Video] - Step by step instructions to prepare IIS Logs files and loading into PBI

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15027

IIS logs aren't too much of a challenge for Power Query. My approach is to get the IIS log file to load as a single text column (PQ tends to want to automatically split it for you). Edit your generated Source step down to something like this:

= Csv.Document(File.Contents("C:\inetpub\logs\LogFiles\W3SVC1\u_ex160523.log"),[Encoding=1252, QuoteStyle=QuoteStyle.None])

From there I would remove the first 3 heading rows, replace "#Fields: " with nothing to remove it, then split by Spaces and Use First Row as Headers.

A bit more filtering to get rid of any further heading rows and you are probably there.

Upvotes: 0

Carl Walsh
Carl Walsh

Reputation: 6959

Power Query can read any binary log format... if you write the code to define the binary format.

There's an example in the M library refrence http://pqreference.azurewebsites.net/PowerQueryFormulaReferenceAugust2015.pdf of how to use the BinaryFormat library:

17.1 Example Consider a hypothetical file format which contains a 32-bit unsigned integer, followed by that number of points. Each point

is a record consisting of two 16-bit signed integer values (x and y). All integers in the file use Big Endian byte order. When viewed in hex, a sample file might look like this:

00 00 00 02 // number of points (2)
00 03 00 04 // point (x=3, y=4)
00 05 00 06 // point (x=5, y=6)

This file format can be read using the binary format functions, as follows:

let
    fileContents = #binary({
        0x00, 0x00, 0x00, 0x02,
        0x00, 0x03, 0x00, 0x04,
        0x00, 0x05, 0x00, 0x06
    }),
    pointFormat = BinaryFormat.Record([
        x = BinaryFormat.SignedInteger16,
        y = BinaryFormat.SignedInteger16
    ]),
    fileFormat = BinaryFormat.Choice(
        BinaryFormat.UnsignedInteger32,
        (count) => BinaryFormat.List(pointFormat, count))
in
    fileFormat(fileContents) 
// {
// [x = 3, y = 4],
// [x = 5, y = 6]
// }

You might agree that using a custom-built library would be better.

Upvotes: 1

LeCampusAzure
LeCampusAzure

Reputation: 21

OK, here is the answer you may have expected: Azure Data Factory (https://azure.microsoft.com/en-us/documentation/services/data-factory/) is a powerful ETL in the cloud. You will be able - in a pretty simple way - to transform your IIS logs from Azure Storage to your own format in Azure Storage Tables. Then you can easily access the tables directly from Power Query.

Upvotes: 0

LeCampusAzure
LeCampusAzure

Reputation: 21

I am actually think of another solution than Power Query for analyzing IIS logs : Azure Log Analytics - https://azure.microsoft.com/fr-fr/services/log-analytics/ It is more powerful and it has native handling for IIS logs.

Upvotes: 2

Related Questions