How do I parse an Agent Log in PowerBI?

Here is a step-by-step example of how to transform a raw Spirion log line into a structured table.

1. The Raw Data

A typical Spirion IDF or EPS log line looks like this:
[2024-05-14 16:49:05 INF] Processing: searchresult_00002 => EndpointId: dd5ecc16-d9e4-44f1-9fdc-1f34769e0235

2. The Power Query (M) Transformation Steps

If you were to do this in the Power BI UI, these are the steps you would take:

1. Load the File: Use Get Data > Text/CSV and select your .log file.
2. Split by Delimiter (Timestamp):
3. • Select the column.
   • Choose Split Column by Delimiter.
   • Select Custom and enter ] (the closing bracket).
   • This separates the [Date Time Level from the Message.
3. Clean the Timestamp:
4. • On the new first column, use Replace Values to replace the opening [ with nothing.
   • Split by Delimiter again using a Space (at the right-most occurrence) to separate the Date Time from the Level (e.g., INF, ERR).
4. Extract IDs (Optional):
5. • If you need the EndpointId, select the Message column.
   • Go to Add Column > Extract > Text Between Delimiters.
   • Start delimiter: EndpointId:
   • End delimiter: (a space) or the end of the line.

3. The Resulting Table

After these steps, your Power BI table will look like this:

4. Example M-Code (Advanced)

If you want to see the actual code Power BI uses in the "Advanced Editor," it looks like this:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Logs\IDF.log"))}),
    // Split the [Timestamp Level] from the Message
    SplitByBracket = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("] ", QuoteStyle.None), {"Header", "Message"}),
    // Remove the leading [
    CleanHeader = Table.TransformColumns(SplitByBracket, {{"Header", each Text.AfterDelimiter(_, "["), type text}}),
    // Split Header into Timestamp and Level
    SplitHeader = Table.SplitColumn(CleanHeader, "Header", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"Timestamp", "Level"}),
    // Change Timestamp to actual Date/Time type
    FinalType = Table.TransformColumnTypes(SplitHeader,{{"Timestamp", type datetime}, {"Level", type text}, {"Message", type text}})
in
    FinalType

Why do this?

Once the data is in this format, you can create a Pie Chart of "Errors vs. Successes" or a Line Chart showing "Log Volume over Time" to identify when a system started failing.

Tip: If you are doing this for a Gather Data zip, remember that the zip contains multiple log files. You should use the "Folder" connector in Power BI, which will enable you to combine all .log files in the IDFLogFiles folder into one single master table automatically.

How do I parse Multiple Log Files?

To parse multiple log files at once in Power BI (such as all the files in the IDFLogFiles folder from a Gather Data zip), you should use the "Folder" Connector. This enables Power BI to treat an entire directory as a single data source, automatically "stacking" the logs on top of each other.

Here is the workflow to set this up:

1. Connect to the Folder

1. In Power BI Desktop, click Get Data > Folder.
2. Browse to the unzipped folder containing your logs (e.g., ...\GatherData\IDFLogFiles).
3. A window will appear listing all files in that folder. Click Transform Data (do not click Combine yet, as raw logs need cleaning first).

2. Filter and Prepare the Files

1. You will see a list of files with columns like Content, Name, and Extension.
2. Filter by Extension: Click the dropdown on the Extension column and ensure only .log is selected.
3. Keep Necessary Columns: Select the Content column (which holds the actual log data) and the Name column (so you know which file each log line came from). Right-click and select Remove Other Columns.

3. Combine and Parse the Content

1. Click the "Combine Files" icon (two downward arrows) in the header of the Content column.
2. Power BI will ask how to open the files. Ensure it is set to Text or CSV and click OK.
3. Power BI will now create a "Helper Query" and expand all log lines into a single table. You will now have a column called Source.Name and a column with the raw log text.

4. Apply the Parsing Logic (The "Magic" Step)

Now that all your logs are in one long list, apply the parsing steps we discussed earlier to the column containing the log text:

1. Split by Delimiter: Split by the closing bracket ] to separate the [Timestamp Level] from the Message.
2. Clean the Header: Use Replace Values to remove the leading [ from the timestamp column.
3. Split Header: Split the timestamp column by Space (using the right-most occurrence) to separate the Date/Time from the Level (INF, ERR, etc.).
4. Set Data Types: Change the Timestamp column to Date/Time and the Level/Message columns to Text.

5. Why this is powerful

• Automatic Updates: If you drop a new .log file into that folder and click Refresh in Power BI, the new data is automatically parsed and added to your charts.
• Cross-File Correlation: You can now create a chart that shows errors across all log files simultaneously.
• Source Tracking: Because you kept the Source.Name column, you can see if a specific log file (like an older, rolled-over log) contains the root cause of an issue that isn't appearing in the current log.

Best Practice: Handling "Rolled" Logs

Spirion Agents often "roll" logs (for example, IDF.log, IDF.log.1, IDF.log.2). When using the Folder Connector, Power BI will read all of them.

• Sort by Timestamp: Always ensure your final table is sorted by the Timestamp column, not the Source.Name column, to ensure the events appear in the correct chronological order across the different files.

Summary: Use the Folder Connector to ingest the directory, click Combine Files, and then apply your Split Column transformations to the resulting master list.