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.
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.
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.
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-datahttps://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/
6 comments:
Hello! When I try to update the report to my own, converted logfiles there is a message in PBI Desktop "The column "LogFileName" of the table was not found". Can you please help me? Thank you!
Ist me again. I dont know, where the Problem was, but now it works perfectly! Thank you very much!!
@Jürgen Mnich,
The first version of Power BI expects the LogFileName column. Later I had removed and pushed the changes to repo. You may get the latest from there.
If it is still not resolving, is it possible to share a stripped-down / scrubbed version of your IIS logs so that I can try to load?
I would like to thank the author for his such useful and informative blog post about Power BI.
Powerbi Read Rest
Thanks for blogging on this, this is precisely what we needed. Now we can build on it. We uncovered some things which we didn't expect.
Power BI Visualizations I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much.
Post a Comment