Sankar Gowthavaram on 22 Nov 2014 03:46:02
Read the raw data out IIS log files in IISW3C format and generate the reports with trends quickly
Administrator on 26 Nov 2014 09:34:29
Thanks for the suggestion! We'll consider it along with the others here and prioritize it based on the votes. If you want to help raise the priority, keep voting!
- Comments (6)
RE: Web servers on-premise for IIS log data
Evaluating since 4 and a half years? Any news here?
RE: Web servers on-premise for IIS log data
This task is not so complicated to achieve using M function.
First step - analyze the structure of the log file. It appears lines with comments start with "#" sign. Sometimes they repeat it within the file (maybe as result of IISRESET?). So removing just the first four lines is not enough. It is better to remove all lines that start with "#". I was also interested only in successes so I filtered by status 200.
Next - setting the date and time columns type and renaming the columns.
So a function that does that looks like that:
// A function that will handle an IIS Log files
let
Source = (FileName) => let
SingleFile = Csv.Document(FileName,[Delimiter=" ", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
// Remove all lines that start with "#" (usually 4 rows on the header, sometimes other exist in the file)
RemoveCommentRows = Table.SelectRows(SingleFile , each not Text.StartsWith([Column1], "#")),
// Filter the rows that only contain status 200
IncludeStatus200 = Table.SelectRows(RemoveCommentRows, each ([Column12] = "200")),
// Change the date and time types
ChangeColumnTypes = Table.TransformColumnTypes(IncludeStatus200 ,{{"Column1", type date}, {"Column2", type time}}),
// Rename the columns by their content
RenamedColumns = Table.RenameColumns(ChangeColumnTypes ,{{"Column1", "date"}, {"Column2", "time"}, {"Column3", "s-ip"}, {"Column4", "cs-method"}, {"Column5", "cs-uri-stem"}, {"Column6", "cs-uri-query"}, {"Column7", "s-port"}, {"Column8", "cs-username"}, {"Column9", "c-ip"}, {"Column10", "cs(User-Agent)"}, {"Column11", "cs(Referer)"}, {"Column12", "sc-status"}, {"Column13", "sc-substatus"}, {"Column14", "sc-win32-status"}, {"Column15", "sc-bytes"}, {"Column16", "cs-bytes"}})
in
RenamedColumns
in
Source
Next - deal with the log files. Since I was interested only in activity in last 4 months, I filtered by the file names. Each file I sent
RE: Web servers on-premise for IIS log data
This would be invaluable... We use WebLog Expert for this, but I can imagine the benefits of consolidating all the infrastructure logs in Power BI. It does look promising though, this request has been sitting here for almost 2 years.
Maybe we can begin with importing Azure WebApp logs?
RE: Web servers on-premise for IIS log data
Agreed, IIS logs... SQL Server Error logs... Event logs in XML formats... Query Extended event output logs from SQL Server. All of these would be helpful in troubleshooting.
RE: Web servers on-premise for IIS log data
we can use this instead log parser...please
RE: Web servers on-premise for IIS log data
This tool would be invaluable for everything from trend analysis, peak analysis and performance metrics if we could import IIS logs