Avishay Ben Zvi

My feedback

  1. 199 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Power BI Ideas » Report Server  ·  Flag idea as inappropriate…  ·  Admin →
    Avishay Ben Zvi supported this idea  · 
  2. 45 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Power BI Ideas » Report Server  ·  Flag idea as inappropriate…  ·  Admin →
    Avishay Ben Zvi supported this idea  · 
  3. 153 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Power BI Ideas » Report Server  ·  Flag idea as inappropriate…  ·  Admin →
    Avishay Ben Zvi supported this idea  · 
  4. 155 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Power BI Ideas » Data Sources  ·  Flag idea as inappropriate…  ·  Admin →
    Avishay Ben Zvi commented  · 

    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

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.