Upcoming changes to the CommonSecurityLog table
Published Oct 25 2022 04:10 AM 2,839 Views
Microsoft

On February 28th 2023 we will introduce changes to the CommonSecurityLog table schema. This means that custom queries will require being reviewed and updated. Out-of-the-box contents (detections, hunting queries, workbooks, parsers, etc.) will be updated by Microsoft Sentinel.

Data that has been streamed and ingested before the change will still be available in their former columns and formats. Old columns will therefore remain in the schema.

Why are we making this change

  • Several fields, previously defined to be of type Integer, were updated in the ArcSight CEF standard revision, to be of other types. By changing the column data types from System.Int32 to System.Int64, we will allow larger values to populate the column that would have been with the previous data type when their length exceeded the column limit.
  • We are fixing escaped characters: CEF requires escaping for special characters such as the equal sign and the backslash to be valid. Up to this maintenance work, the backslash escaping character was not removed from events streaming into the CommonSecurityLog table. This escaping character will now be removed before the event is ingested and stored in the table. 

EventOutcome, Reason and DeviceEventCategory previously were key-value pairs located in column. Now, as they will have their own column, it will be easier to query on those fields and this will reduce text by removing those key values.

Summary of changes

Current Column Name

Column Data Type

New Column Name 

New Column Data Type

Notes

ExternalID

System.Int32

ExtID

System.String

 

DeviceCustomNumber1

System.Int32

FieldDeviceCustomNumber1 

System.Int64

 

DeviceCustomNumber2

System.Int32

FieldDeviceCustomNumber2 

System.Int64

 

DeviceCustomNumber3

System.Int32

FieldDeviceCustomNumber3 

System.Int64

 
   

EventOutcome

System.String

Formerly located in AdditionalExtensions

   

Reason

System.String

Formerly located in AdditionalExtensions

   

DeviceEventCategory

System.String

Formerly located in AdditionalExtensions

 

New columns change will start rolling out on October 25 and will be fully deployed by Nov 1 meaning that after this date it will be possible to test and update custom queries. Updates must be completed between Nov 1 and February 27 2023.

 

Samples of existing versus new queries

 

 

Example for Reason and EventOutcome (new columns, this information was in AdditionalExtensions before):

 

BEFORE

CommonSecurityLog

| where DeviceVendor == "Zscaler" and DeviceProduct == "NSSWeblog"

| extend reason = extract(@"reason=(.*?);", 1, AdditionalExtensions),

     outcome = extract(@"outcome=(.*?);", 1, AdditionalExtensions)

 

AFTER

CommonSecurityLog

| where DeviceVendor == "Zscaler" and DeviceProduct == "NSSWeblog"

| extend reason = coalesce(

                            extract(@"reason=(.*?)(;|$)", 1, AdditionalExtensions),                            

                            column_ifexists("Reason", "")

                        ),

   outcome = coalesce(

                        extract(@"outcome=(.*?)(;|$)", 1, AdditionalExtensions),                           

                        column_ifexists("EventOutcome", "")

              )

 

Example for fieldDeviceCustomNumber1, fieldDeviceCustomNumber2 and fieldDeviceCustomNumber3:

 

BEFORE

CommonSecurityLog

    | where DeviceVendor == "Palo Alto Networks" and DeviceProduct == "PAN-OS" and Activity == "TRAFFIC"

    | extend

   NetworkPackets=tolong(DeviceCustomNumber2)  

, NetworkSessionId=tostring(DeviceCustomNumber1)

, NetworkDuration=toint(1000*DeviceCustomNumber3)

 

 

AFTER

CommonSecurityLog

    | where DeviceVendor == "Palo Alto Networks" and DeviceProduct == "PAN-OS" and Activity == "TRAFFIC"

    | extend

NetworkPackets = coalesce(

          tolong(column_ifexists("fieldDeviceCustomNumber2", long(null))),

          tolong(column_ifexists("DeviceCustomNumber2",long(null)))

        )

      , NetworkSessionId = coalesce(

          tostring(column_ifexists("fieldDeviceCustomNumber1", long(null))),

          tostring(column_ifexists("DeviceCustomNumber1",long(null)))

        )

      , NetworkDuration= coalesce(

          toint(1000*column_ifexists("fieldDeviceCustomNumber3", 0)),

          toint(1000*column_ifexists("DeviceCustomNumber3",0)),

          int(null)

        )

 

If you want to test your queries, remember you can use the datatable operator to create dummy data.

Recommended Actions

If you don’t have the CommonSecurityLog table in your workspace, this schema change will not impact your workspace and SOC operations.

If you do, you should:

  • Use the latest version of our out-of-the-box artifacts. Make sure you update your workbooks, analytics, parsers and solutions to the latest version.
  • Review your custom queries to check if the affected columns are present. You’ll need to look for custom content and queries which are affected by the schema changes listed in the table above.
  • We recommend to review: Custom Analytics Rules, Workbooks, Playbooks, Custom functions, custom hunting queries.

How to test the changes in the custom content?

As soon as the schema change will be deployed in the workspace, custom content can be converted to include the changes as shown in the above examples.

The challenge is the fact that the new columns will remain empty until the change that will be deployed on February 28th, 23. To enable testing, in non-Prod Sentinel workspaces, after the schema change we suggest to use Data Collection Rule to mimic data flow into the new columns in the CommonSecurityLog schema.

This will enable the updated custom queries to fetch data both from the previous and new columns simulating the table after the full schema change.

Following is the KQL for the DCR creation:

source

| extend ExtID = tostring(ExternalID)

| extend FieldDeviceNumber1 = tolong(DeviceCustomNumber1)

| extend FieldDeviceNumber2 = tolong(DeviceCustomNumber2)

| extend FieldDeviceNumber3 = tolong(DeviceCustomNumber3)

| extend Reason = (extract(@"reason=(.*?)(;|$)", 1, AdditionalExtensions))

| extend EventOutcome = (extract(@"outcome=(.*?)(;|$)", 1, AdditionalExtensions))| extend DeviceEventCategory = (extract(@"cat=(.*?)(;|$)", 1, AdditionalExtensions))

 

For further information on how to use create and deploy Data Collection Rules please review the following documentation for Microsoft Sentinel pipeline transformation and KQL transformation guidance

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Co-Authors
Version history
Last update:
‎Oct 25 2022 03:44 AM
Updated by: