How can we improve Power BI?

Web servers on-premise for IIS log data

Read the raw data out IIS log files in IISW3C format and generate the reports with trends quickly

154 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Sankar Gowthavaram shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

6 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Avishay Ben Zvi commented  ·   ·  Flag as inappropriate

    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 to the function and lastly appended them all together using the "Table.Combine" function.

    let
    Source = Folder.Files("\\server\LogFiles\W3SVC1"),

    // Filter only the files from last 4 months by the file name
    FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "ex1708") or Text.StartsWith([Name], "ex1709") or Text.StartsWith([Name], "ex1710") or Text.StartsWith([Name], "ex1711")),

    // Calling the function that will manipulate the data (remove # lines, rename columns etc.) on each of the files in the directory
    CallFunction = Table.AddColumn(FilteredFiles , "LogContent", each TrasformFile([Content])),

    // Union all tables into one ("Append")
    AllLogs = Table.Combine (CallFunction [LogContent]),

    // Adding an index column
    AddedIndex = Table.AddIndexColumn(AllLogs, "HitId", 1, 1),

    // Setting the Index column (HitId) to appear first
    ReorderedColumns = Table.ReorderColumns(AddedIndex,{"HitId", "date", "time", "s-ip", "cs-method", "cs-uri-stem", "cs-uri-query", "s-port", "cs-username", "c-ip", "cs(User-Agent)", "cs(Referer)", "sc-status", "sc-substatus", "sc-win32-status", "sc-bytes", "cs-bytes"})
    in
    ReorderedColumns

  • Anonymous commented  ·   ·  Flag as inappropriate

    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?

  • Cary McDonald commented  ·   ·  Flag as inappropriate

    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.

  • David Taylor commented  ·   ·  Flag as inappropriate

    This tool would be invaluable for everything from trend analysis, peak analysis and performance metrics if we could import IIS logs

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.