Layering Functions to create Normalization

%3CLINGO-SUB%20id%3D%22lingo-sub-1728219%22%20slang%3D%22en-US%22%3ELayering%20Functions%20to%20create%20Normalization%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728219%22%20slang%3D%22en-US%22%3E%3CP%3EI%20currently%20have%20a%20customer%20who%20has%20many%20different%20firewall%20types.%20We%20use%20functions%20to%20try%20and%20normalize%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Cisco%20Meraki%20comes%20in%20as%20a%20_CL%20table%2C%20and%20we%20have%20a%20function%20which%20runs%20%22extract%22%20with%20some%20regex%20to%20get%20common%20fields%20like%20Dst_IP%2C%20Src_IP%2C%20etc.%20called%20CiscoMerakiFW%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20We%20have%20a%20Sophos%20XG%20firewall%20which%20comes%20in%20via%20syslog%20and%20we're%20using%20the%20supplied%20Function%20table%20which%20creates%20a%20%22SophosXGFirewall%22%3C%2FP%3E%3CP%3E3)%20We%20also%20have%20a%20Sophos%20SG%20firewall%20which%20comes%20in%20via%20syslog%20and%20I%20used%20the%20XG%20firewall%20function%20as%20a%20template%20and%20created%20a%20SophosSGFirewall%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E4)%20I%20created%20a%20function%20called%20%22AllFW%22%20which%20is%20a%20%22union%20CiscoMerakiFW%20%7C%20union%20SophosXGFirewall%20%7C%20union%20SophosSGFirewall%22%20which%20works%3B%20I%20run%20queries%20against%20it%2C%20and%20I%20kept%20the%20column%20names%20consistent%20(Dst_IP%2C%20Dst_Port%2C%20etc)%20so%20we%20can%20get%20alerts%2C%20metrics%2C%20etc.%3C%2FP%3E%3CP%3E5)%20We've%20now%20integrated%20a%20Fortinet%20firewall%20which%20sends%20in%20CEF%20and%20throws%20data%20into%20the%20CommonSecurityLog%20table.%20That%20part%20works%20OK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20add%20the%20Fortinet%20FW%20to%20the%20'AllFW'%20function%20I%20created%20so%20we%20can%20run%20queries%20against%20ALL%20the%20firewall%20data%20at%20once.%20This%20is%20where%20I%20run%20into%20an%20issue.%20I%20update%20the%20%22AllFW%22%20function%20to%20look%20like%20the%20following%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20CiscoMerakiFW%20%0A%7C%20union%20SophosSGFirewall%20%0A%7C%20union%20SophosXGFirewall%20%0A%7C%20union%20(CommonSecurityLog%20%7C%20where%20DeviceVendor%20%3D%3D%20%22Fortinet%22%20%7C%0Aproject-rename%20Dst_Port%20%3D%20DestinationPort%2C%20Dst_IP%20%3D%20DestinationIP%2C%20Src_Port%20%3D%20SourcePort%2C%20Src_IP%20%3D%20SourceIP)%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20breaks%20the%20%22AllFW%22%20function%20when%20I%20try%20to%20run%20queries%20against%20it%20as%20it%20claims%20Dst_IP%20no%20longer%20exists.%3C%2FP%3E%3CP%3EI've%20tried%20both%20%22extend%22%20and%20%22project-rename%22%20within%20the%20()%20of%20the%20CommonSecurityLog%20%22union%22%20and%20it%20doesn't%20work.%20Is%20this%20expected%3F%20How%20can%20I%20accomplish%20what%20I'm%20looking%20for%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1728219%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFunctions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728254%22%20slang%3D%22en-US%22%3ERe%3A%20Layering%20Functions%20to%20create%20Normalization%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728254%22%20slang%3D%22en-US%22%3E%3CP%3EA%20quick%20clarification%3B%20the%20%22code%22%20formatting%20functionality%20of%20the%20post%20isn't%20happy%3B%20it%20makes%20it%20look%20like%20I'm%20leaving%20stuff%20off.%20This%20is%20what%20the%20AllFW%20function%20really%20looks%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20CiscoMerakiFW%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20SophosSGFirewall%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20SophosXGFirewall%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%20%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ECommonSecurityLog%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20DeviceVendor%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Fortinet%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eproject-rename%3C%2FSPAN%3E%3CSPAN%3E%20Dst_Port%20%3D%20DestinationPort%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20Dst_IP%20%3D%20DestinationIP%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20Src_Port%20%3D%20SourcePort%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20Src_IP%20%3D%20SourceIP%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728439%22%20slang%3D%22en-US%22%3ERe%3A%20Layering%20Functions%20to%20create%20Normalization%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728439%22%20slang%3D%22en-US%22%3E%3CP%3EI%20did%20get%20it%20working.%20It's%20just%20making%20sure%20the%20syntax%20is%20exact.%20So%20this%20is%20how%20my%20%22AllFW%22%20function%20works%20now%3A%3CBR%20%2F%3E%3CBR%20%2F%3Eunion%20isfuzzy%3Dtrue%20CiscoMerakiFW%3C%2FP%3E%3CP%3E%7C%20union%20isfuzzy%3Dtrue%20SophosSGFirewall%3C%2FP%3E%3CP%3E%7C%20union%20isfuzzy%3Dtrue%20SophosXGFirewall%3C%2FP%3E%3CP%3E%7C%20union%20isfuzzy%3Dtrue%20(CommonSecurityLog%20%7C%20where%20DeviceVendor%20%3D%3D%20%22Fortinet%22%3C%2FP%3E%3CP%3E%7C%20extend%20Dst_Port%20%3D%20tostring(DestinationPort)%2C%20Dst_IP%20%3D%20DestinationIP%2C%20Src_Port%20%3D%20tostring(SourcePort)%2C%20Src_IP%20%3D%20SourceIP)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20playing%20with%20this%20I%20noticed%20a%20'new'%20column%2C%20%22Dst_Port_Int%22%20which%20I%20wasn't%20expecting%2C%20so%20I%20picked-up%20on%20some%20potential%20data-type%20issues.%20Once%20I%20remembered%20about%20%22isfuzzy%22%20it%20all%20came%20together.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 Replies

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)

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.