Sep 30 2020 02:28 AM
Sep 30 2020 02:28 AM
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?
Sep 30 2020 02:31 AM
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:
Sep 30 2020 03:40 AM - edited Sep 30 2020 03:42 AM
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.