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 FieldDeviceCustomNumber1 = tolong(DeviceCustomNumber1)
| extend FieldDeviceCustomNumber2 = tolong(DeviceCustomNumber2)
| extend FieldDeviceCustomNumber3 = 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
