Analyzing Fortigate Firewall Logs with Excel and PowerQuery

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!