How to split the Fortinet firewall logs into two different table

[Artham_Harish]

We are collecting Fortinet firewall logs via CEF connector Via AMA, we have duplicated common security log table into analytical and basic table. Also created the DCR but unsure what filter need to be applied in Transform KQL to move the data to what table. which are the essential column data that needs to be moved to analytical table and what to be moved to basic table.

@JairusOSI 

[#2]

https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/split-microsoft-sentinel-tables-with-...

https://www.managedsentinel.com/log-splitting-with-data-collection-rules/

This last one (I prefer) and is Forigate specific

https://www.linkedin.com/pulse/filter-split-firewallcef-logs-multiple-sentinel-tables-marko-lauren/

As for columns, you need to decide based on what you plan to actually monitor and alert on.

[#3]

@Clive_Watson Many Thanks for the response.

Question 1.  I have followed the article to create a table using the below script, however I am not able to see any logs under basic table, we see utm, event, traffic logs being received by sentinel (provided the screenshots for the same).

Question 2.   Is there any best practice to choose what columns need to monitor and alert on.

Question 3. Do we need to create table for VPN logs as well or this is only specific to Firewall logs ?

Please help us how we can move forward.

Below is the Script to move the logs into analytical and basic table.

 "dataFlows": [

                    {

                        "streams": [

                            "Microsoft-CommonSecurityLog"

                        ],

                        "destinations": [

                            "DataCollectionEvent"

                        ],

                        "transformKql": "source | where DeviceVendor == 'Fortinet' and  DeviceEventCategory has 'traffic'",

                        "outputStream": "Microsoft-CommonSecurityLog"

                    },

                    {

                        "streams": [

                            "Microsoft-CommonSecurityLog"

                        ],

                        "destinations": [

                            "DataCollectionEvent"

                        ],

                        "transformKql": "source | where DeviceVendor == 'Fortinet' and  DeviceEventCategory !has 'traffic'",

                        "outputStream": "Custom-BasicCommonSecLog_CL"

                    }

Screenshot 1 : We are able to see utm, event and traffic logs in commonsecuritylog table.

Screenshot 2 : No logs are found in Basic table.

[#4]

@Clive Watson
removed devicevendor filter and now we are able to get logs under the basic table, however under commonsecuritylog still we can see all types of category logs like utm,event and traffic

"dataFlows": [

{

"streams": [

"Microsoft-CommonSecurityLog"

],

"destinations": [

"DataCollectionEvent"

],

"transformKql": "source | where DeviceEventCategory has 'traffic'",

"outputStream": "Microsoft-CommonSecurityLog"

},

{

"streams": [

"Microsoft-CommonSecurityLog"

],

"destinations": [

"DataCollectionEvent"

],

"transformKql": "source | where DeviceEventCategory !has 'traffic'",

"outputStream": "Custom-BasicCommonSecLog_CL"

}