Power BI Desktop Dashboard for IIS Log Analysis - Improved performance of PowerShell data prep script

This is the third post in the "Power BI Desktop Dashboard for IIS Log Analysis" series.

Power BI Desktop Dashboard for IIS Log Analysis
Power BI Desktop Dashboard for IIS Log Analysis - C# for IIS log parsing instead of PowerShell
Power BI Desktop Dashboard for IIS Log Analysis - Improved performance of PowerShell data prep script

Here we will be discussing how the data preparation process is enhanced by just using the LogParser command. In the initial versions, the time to prepare CSV from the .log file was high if the cs-byes and sc-bytes columns were missing. That is because the approach taken was to load the CSV file into PowerShell and add additional columns with a default value.

This time the approach changed a little bit and completely avoided the need to load CSV files into PowerShell. Logic is as follows

1. Try to convert the input log files as-is on the assumption that the log contains all the required fields including cs-bytes and sc-bytes. This uses LogParser.exe
2. In case of error, use the same LogParser.exe file with a slightly different query which selects '0 as cs-bytes, 0 as sc-bytes'. 

Click here to see the code changes in GitHub repo.

The fix is not rocket science. It is an obvious thing everybody knows but unfortunately missed in the earlier phase. Thanks, Wade Mascia for pointing out.

Happy Analyzing...

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

Today lets see how to prepare IIS Log files to load into Power BI Dashboard. The 'software-performance' GitHub repo contains sample IIS Log files along with the tools. We can use the same for the demo.

Prerequisites

Below are the prerequisites to complete the steps which help us to analyze IIS Logs via Power BI.

1. A machine with Windows OS as all the tools used are compatible with Windows only.
2. Basic understanding of Windows PowerShell
3. LogParser utility
4. Of course the Power BI Desktop application.

Steps

Let's see the steps

1. The first, step is to clone the 'software-performance' GitHub repo into a local directory. How to clone a repo is not in the scope of this tutorial. Please refer to the link provided. Let's consider is it cloned into c:\source\repos folder
2. Second step is to convert .log files into .csv. Open Windows PowerShell ISE better "run as administrator". Then navigate to the cloned folder where the IIS to CSV.ps1 file is available.
3. Once we open the IIS to CSV.ps1 file, make sure the variable $pathToLogParserExe has the correct path to the installed logparser.exe file
4. Change the value of $inputPath to the folder where IIS Log files are located. Let's use the sample logs provided in the software-performance repository.
5. Run the script by clicking the button of press F5
6. It will convert the .log files to .csv files. We can see the CSV files created in the same folder of input log files.
7. Now open the Power BI sample file present in the repo
8. Click on the Edit Queries drop-down then- > Data source settings
9. We can see the current folder used. Click on the 'Change Source' button in the bottom left. Enter the path to the folder where CSV files created
10. Press Ok then Close buttons.
11. Finally, click on the 'Refresh' button. It will load the data from files into Power BI and render reports.

It may take more time depending on the size of CSV files. Normally the speed is observed as 1MB/second.

The same steps along with a short description of dashboards available in the below video

Power BI Desktop Dashboard for IIS Log Analysis - C# for IIS log parsing instead of PowerShell

Introduction

The last post in this blog was about Power BI to analyze IIS logs. It used Log Parser and PowerShell to prepare the W3C IIS .log files into CSV. That conversion is needed as Power BI don't understand the W3C log file format produced by IIS. Though it works for small to medium load, it takes more time to prepare larger files. Let see another way to prepare IIS logs to CSV for log analysis.

Limits of log parser

One of the limit is it cannot output a default value if the columns are not available in the source log file. At the other side Power BI needs all the columns in CSV to load data. This mismatch happens frequently as the default setting of IIS web server doesn't include the cs-bytes and sc-bytes columns and the PowerBI needs that to analyze the payload.

The solution was to load the CSV produced by Log Parser into PowerShell and add missing columns and output the same into CSV file. PowerShell loading the CSV and adding columns is time consuming out of the box. We can tune the PowerShell script to optimize CSV handling. But still, there will be 2 tools IISLogParser and PowerShell.

Enter C#

What if we use C# to convert the IIS Logs to the desired CSV with default values? It is not advisable to rewrite the IIS log parsing logic in C# and maintain the same. It is better to use a library from nuget. Fortunately, there is one library called IISLogParser. We can use the same to parse the logs and use CSV libraries such as ServiceStack to output into CSV.

It is easy to set up such utility in C#. Source code can be found at below GitHub location.

https://github.com/joymon/software-performance/tree/master/web/iis/log-analysis/Power%20BI/prep-cs/IIS2CSV

Please note that this uses .Net Core 2.1 target. Ideally, there should be no issues in adding full .Net framework version as target as those libraries support multi-targeting. Add an issue in GitHub repo in case of issue better with PR. Comments in this post also welcome. 

References

https://stackoverflow.com/questions/32120528/parse-iis-log-file-is-there-an-alternative-to-logparser

Power BI Desktop Dashboard for IIS Log Analysis

Introduction

As developers, we all might have encountered situations of analyzing IIS web server logs. During the development time, the file is small and easy to analyze in Notepad or Excel. But when it grows to GBs in production servers we use other tools. One such popular tool to query IIS logs is LogParser. It is a free command-line tool from Microsoft. There are graphical applications around it to generate even charts. One such free tool is the Log Parser Studio. It is also from Microsoft.

Once we move up in career and had to deal with managers, product stakeholders or even to CXO to show what the IIS logs say, we need more visuals or a dashboard reflecting the IIS logs. Though we can create visual using Log Parser Studio, it is tedious creating reports and charts one by one. 

One easier solution is to get in Azure and use Azure AppInsights which has built-in dashboards. We can do customizations easily. There are other services like Application Insight also such as Dynatrace, Retrace, etc...But the problem is that not all of us gets the luxury to buy the software and services we want. Sometimes our organization or clients may not be willing to buy. So let's look for other free solutions.

Enter Power BI

Power BI Desktop is a free visualization tool from Microsoft. It can easily produce great-looking visuals with data insights.
Fortunately, this is not the first post on PowerBI for IIS W3C log analysis. There is already one out. We are using it as a base and adding some more features.

https://blogs.msdn.microsoft.com/kennethteo/2018/04/27/analyzing-w3ciis-log-with-powerbi/

Prerequisites

Tools 

• LogParser
• Power BI Desktop - Free version is enough

Experience

• Basics of PowerShell
• LogParser queries.

Data preparation

The post by Kenneth uses .bat commands to trigger LogParser to convert .log files from IIS to csv. Those queries have hardcoded column names. The problem with that is we cannot just add more columns from IIS logs. Example cs-bytes and sc-bytes are the columns which are not enabled in IIS by default. Ideally, the solution is to use 'select * ' in the LogParser query. But the problem is that Power BI doesn't support (or more research needed) a flexible number of columns in CSV mapped properly to fixed columns. 

Hence using PowerShell to invoke LogParser which first try to convert .log to .csv including cs-bytes and sc-bytes. If those columns are not available, it tries without those columns which are the default setting in IIS. If those columns are not available, 0 / default value will be used.

PowerShell can be downloaded from the below location.

https://github.com/joymon/software-performance/tree/master/web/iis/log-analysis/Power%20BI/prep-powershell

How to run PowerShell

• Use the IIS to CSV.ps1 
• Change the $inputPath to the folder where .log files are located
• Run the ps1 file
• The output .csv files will be generated in the same folder location.

Power BI template provided by Kenneth in his post has to be updated to reflect this change. Let's see those changes

Separation of concerns in Power BI dashboards

The Power BI template used by Kenneth has fewer reports and visuals. Obviously, that seems towards proving a concept. Let's add more visuals such as a dedicated page for errors, errors by server-ip to identify faulty servers.

Sample

Step by step instructions to add more visuals is not necessary as the PBI template itself can be shared and anyone can load their own data to it. Sample Power BI having more visuals has been uploaded to below location.

https://github.com/joymon/software-performance/tree/master/web/iis/log-analysis/Power%20BI/PBI-Template

Just prepare the CSV from .log files and update data source in PBI to reflect the changes in visuals.

Feel free to download and try it out. Any suggestions are welcome in Github as enhancements.

References

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6752494-web-servers-on-premise-for-iis-log-data
https://community.powerbi.com/t5/Desktop/IIS-log-files-convert-queries-to-PowerBI/td-p/166160
https://ittechnews.net/microsoft/official-microsoft-news/analyzing-w3c-iis-log-with-powerbi/