1

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?

Rory
  • 40,559
  • 52
  • 175
  • 261

5 Answers5

2

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.

LeCampusAzure
  • 21
  • 3
  • This does look useful and I'll give it a go. But it does involve getting to grips with and subscribing to a sophisticated online service that you then become dependent upon. Simply being able to analyze in Excel provides lots of benefits too, so it'd be nice to have an easy method to extract & import this data. – Rory May 19 '16 at 21:43
1

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.

Carl Walsh
  • 6,100
  • 2
  • 46
  • 50
1

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

Joy George Kunjikkuru
  • 1,495
  • 13
  • 27
0

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.

LeCampusAzure
  • 21
  • 3
0

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.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Yeah, it's just that if I have dozens of logfiles those small cleanup steps become more of a pain. I was wanting a way to automate that away. Since my logs are stored in azure and Power Query can retrieve directly from there it'd be best if it's a way that's part of Power Query so doesn't involve downloading the files and manipulating them. Do you know of a way to programmatically cleanup the data like you describe but within Power Query? I guess the answer lies in that pdf attached by @CarlWalsh – Rory May 23 '16 at 09:06
  • Just use PQ to get your list of Azure blobs, then hit the double-down arrow next to the Contents column header - this will add Combine Contents and Import Text steps to give you all the rows from your combined blobs. From there add the steps I described above. – Mike Honey May 24 '16 at 08:34
  • A note for anyone trying to take this approach: in IIS log files the first 3 heading rows are often repeated later in the file. This happens on some event (e.g. worker process recycle?), so you'll have to deal with this if trying to process the log files as text. – Rory Jun 28 '21 at 20:04