Analyzing Fortigate Firewall Logs with Excel and PowerQuery

[StephanG]

Hi anyone,

i need help getting this format (like 1000s of lines)

date=2025-02-11 time=07:54:59 id=7470051500572541161 itime="2025-02-11 07:54:59" euid=3 epid=1192 dsteuid=3 dstepid=101 logver=702101706 type="traffic" subtype="forward" level="notice" action="start" policyid=148 sessionid=824135146 srcip=172.xxxx dstip=23.201.29.80 transip=xxx srcport=57868 dstport=443 transport=57868 trandisp="snat" duration=0 proto=6 sentbyte=0 rcvdbyte=0 sentpkt=0 rcvdpkt=0 logid=0000000015 service="Akamai-CDN" app="Akamai-CDN" appcat="unscanned" srcintfrole="undefined" dstintfrole="wan" policytype="policy" centralnatid=2 eventtime=1739256898685401792 vwlid=0 poluuid="x040" srccountry="Reserved" dstcountry="Germany" srcintf="port3" dstintf="port2" dstinetsvc="Akamai-CDN" policyname="Server_Internet-Connections" dstowner="198" tz="+0100" dstcity="Frankfurt" devid="x" vd="root" dtime="2025-02-11 07:54:59" itime_t=1739256899 devname="x"

Into a table that uses the values before the = as the new header and map everything beneath into the table to better analyze log files with Excel or PowerBI.

I tried my luck and failed - also i did not find any guidance and AI could not help either:

let
Source = Text.FromBinary(File.Contents("C:\path\to\your\logfile.txt")),
SplitLines = Text.Split(Source, "#(lf)"),
SplitColumns = List.Transform(SplitLines, each Text.Split(_, " ")),
ToTable = Table.FromList(SplitColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandColumns = Table.ExpandListColumn(ToTable, "Column1"),
RenameColumns = Table.RenameColumns(ExpandColumns, List.Zip({Table.ColumnNames(ExpandColumns), {"date", "time", "id", "itime", "euid", "epid", "dsteuid", "dstepid", "logver", "type", "subtype", "level", "action", "policyid", "sessionid", "srcip", "dstip", "transip", "srcport", "dstport", "transport", "trandisp", "duration", "proto", "sentbyte", "rcvdbyte", "sentpkt", "rcvdpkt", "logid", "service", "app", "appcat", "srcintfrole", "dstintfrole", "policytype", "centralnatid", "eventtime", "vwlid", "poluuid", "srccountry", "dstcountry", "srcintf", "dstintf", "dstinetsvc", "policyname", "dstowner", "tz", "dstcity", "devid", "vd", "dtime", "itime_t", "devname"}}))
in
RenameColumns

Anyone can help me with that?

[v-karpurapud]

Hi @StephanG 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

Hello @StephanG 

Thank you @Akash_Varuna for your response.

Thank you for reaching out to the Microsoft Fabric Community Forum. We understand you're experiencing an issue while converting log data into a structured table.

I have tested your sample data and obtained the expected output. Please try using the following M code to resolve the issue:

M code:

let

    Source = "date=2025-02-11 time=07:54:59 id=7470051500572541161 itime=""2025-02-11 07:54:59"" euid=3 epid=1192 dsteuid=3 dstepid=101 logver=702101706 type=""traffic"" subtype=""forward"" level=""notice"" action=""start"" policyid=148 sessionid=824135146 srcip=172.xxxx dstip=23.201.29.80 transip=xxx srcport=57868 dstport=443 transport=57868 trandisp=""snat"" duration=0 proto=6 sentbyte=0 rcvdbyte=0 sentpkt=0 rcvdpkt=0 logid=0000000015 service=""Akamai-CDN"" app=""Akamai-CDN"" appcat=""unscanned"" srcintfrole=""undefined"" dstintfrole=""wan"" policytype=""policy"" centralnatid=2 eventtime=1739256898685401792 vwlid=0 poluuid=""x040"" srccountry=""Reserved"" dstcountry=""Germany"" srcintf=""port3"" dstintf=""port2"" dstinetsvc=""Akamai-CDN"" policyname=""Server_Internet-Connections"" dstowner=""198"" tz=""+0100"" dstcity=""Frankfurt"" devid=""x"" vd=""root"" dtime=""2025-02-11 07:54:59"" itime_t=1739256899 devname=""x""",

    #"Converted to Table" = #table(1, {{Source}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29", "Column1.30", "Column1.31", "Column1.32", "Column1.33", "Column1.34", "Column1.35", "Column1.36", "Column1.37", "Column1.38", "Column1.39", "Column1.40", "Column1.41", "Column1.42", "Column1.43", "Column1.44", "Column1.45", "Column1.46", "Column1.47", "Column1.48", "Column1.49", "Column1.50", "Column1.51", "Column1.52", "Column1.53"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}, {"Column1.17", type text}, {"Column1.18", type text}, {"Column1.19", type text}, {"Column1.20", type text}, {"Column1.21", type text}, {"Column1.22", type text}, {"Column1.23", type text}, {"Column1.24", type text}, {"Column1.25", type text}, {"Column1.26", type text}, {"Column1.27", type text}, {"Column1.28", type text}, {"Column1.29", type text}, {"Column1.30", type text}, {"Column1.31", type text}, {"Column1.32", type text}, {"Column1.33", type text}, {"Column1.34", type text}, {"Column1.35", type text}, {"Column1.36", type text}, {"Column1.37", type text}, {"Column1.38", type text}, {"Column1.39", type text}, {"Column1.40", type text}, {"Column1.41", type text}, {"Column1.42", type text}, {"Column1.43", type text}, {"Column1.44", type text}, {"Column1.45", type text}, {"Column1.46", type text}, {"Column1.47", type text}, {"Column1.48", type text}, {"Column1.49", type text}, {"Column1.50", type text}, {"Column1.51", type text}, {"Column1.52", type text}, {"Column1.53", type text}}),

    #"Transposed Table" = Table.Transpose(#"Changed Type"),

    #"Split Column by Delimiter1" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1.1", "ColumnType"}, {"Column1.2", "Values"}}),

    #"Transposed Table1" = Table.Transpose(#"Renamed Columns"),

    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),

    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"time", type time}, {"id", Int64.Type}, {"itime", type datetime}, {"euid", Int64.Type}, {"epid", Int64.Type}, {"dsteuid", Int64.Type}, {"dstepid", Int64.Type}, {"logver", Int64.Type}, {"type", type text}, {"subtype", type text}, {"level", type text}, {"action", type text}, {"policyid", Int64.Type}, {"sessionid", Int64.Type}, {"srcip", type text}, {"dstip", type text}, {"transip", type text}, {"srcport", Int64.Type}, {"dstport", Int64.Type}, {"transport", Int64.Type}, {"trandisp", type text}, {"duration", Int64.Type}, {"proto", Int64.Type}, {"sentbyte", Int64.Type}, {"rcvdbyte", Int64.Type}, {"sentpkt", Int64.Type}, {"rcvdpkt", Int64.Type}, {"logid", Int64.Type}, {"service", type text}, {"app", type text}, {"appcat", type text}, {"srcintfrole", type text}, {"dstintfrole", type text}, {"policytype", type text}, {"centralnatid", Int64.Type}, {"eventtime", Int64.Type}, {"vwlid", Int64.Type}, {"poluuid", type text}, {"srccountry", type text}, {"dstcountry", type text}, {"srcintf", type text}, {"dstintf", type text}, {"dstinetsvc", type text}, {"policyname", type text}, {"dstowner", Int64.Type}, {"tz", Int64.Type}, {"dstcity", type text}, {"devid", type text}, {"vd", type text}, {"dtime", type datetime}, {"itime_t", Int64.Type}, {"devname", type text}})

in

    #"Changed Type2"

Use the above M code in advanced editor in power query.

I hope the image above is the output you needed. I have attached a PBIX file for your reference. Please review it and let me know if you encounter any further issues.

If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.

Thank you!

[StephanG]

This solution is coming very near to the final output. I only provide one line and not the whole logfile so only the first line is working

But with search and replace i can work my way through. Thank you very much

[Akash_Varuna]

Hi @StephanG , Could you please try this 

• Load the File: Import the log file into Power Query using Get Data → Text/CSV.
• Split Rows: Split the data into rows using line feed (\n) as the delimiter.
• Extract Key-Value Pairs: Split each row by = into two columns (keys and values).
• Pivot Data: Use Transform → Pivot Column to convert keys into column headers and values into rows.
• Clean Up: Remove null or unnecessary rows and rename columns if needed.
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance