Layering Functions to create Normalization

[JKatzmandu]

I currently have a customer who has many different firewall types. We use functions to try and normalize the data.

1) Cisco Meraki comes in as a _CL table, and we have a function which runs "extract" with some regex to get common fields like Dst_IP, Src_IP, etc. called CiscoMerakiFW 

2) We have a Sophos XG firewall which comes in via syslog and we're using the supplied Function table which creates a "SophosXGFirewall"

3) We also have a Sophos SG firewall which comes in via syslog and I used the XG firewall function as a template and created a SophosSGFirewall function. 

4) I created a function called "AllFW" which is a "union CiscoMerakiFW | union SophosXGFirewall | union SophosSGFirewall" which works; I run queries against it, and I kept the column names consistent (Dst_IP, Dst_Port, etc) so we can get alerts, metrics, etc.

5) We've now integrated a Fortinet firewall which sends in CEF and throws data into the CommonSecurityLog table. That part works OK.

I want to add the Fortinet FW to the 'AllFW' function I created so we can run queries against ALL the firewall data at once. This is where I run into an issue. I update the "AllFW" function to look like the following:

union CiscoMerakiFW 
| union SophosSGFirewall 
| union SophosXGFirewall 
| union (CommonSecurityLog | where DeviceVendor == "Fortinet" |
project-rename Dst_Port = DestinationPort, Dst_IP = DestinationIP, Src_Port = SourcePort, Src_IP = SourceIP) 

This breaks the "AllFW" function when I try to run queries against it as it claims Dst_IP no longer exists.

I've tried both "extend" and "project-rename" within the () of the CommonSecurityLog "union" and it doesn't work. Is this expected? How can I accomplish what I'm looking for?

Thanks!

[#2]

A quick clarification; the "code" formatting functionality of the post isn't happy; it makes it look like I'm leaving stuff off. This is what the AllFW function really looks like:

union CiscoMerakiFW

| union SophosSGFirewall

| union SophosXGFirewall

| union (CommonSecurityLog | where DeviceVendor == "Fortinet" |

project-rename Dst_Port = DestinationPort, Dst_IP = DestinationIP, Src_Port = SourcePort, Src_IP = SourceIP)

[#3]

I did get it working. It's just making sure the syntax is exact. So this is how my "AllFW" function works now:

union isfuzzy=true CiscoMerakiFW

| union isfuzzy=true SophosSGFirewall

| union isfuzzy=true SophosXGFirewall

| union isfuzzy=true (CommonSecurityLog | where DeviceVendor == "Fortinet"

| extend Dst_Port = tostring(DestinationPort), Dst_IP = DestinationIP, Src_Port = tostring(SourcePort), Src_IP = SourceIP)

When playing with this I noticed a 'new' column, "Dst_Port_Int" which I wasn't expecting, so I picked-up on some potential data-type issues. Once I remembered about "isfuzzy" it all came together.