SecurityEvent Table Transformation DCR not working

Greg Sneed 20

I'm having an issue with ingestion on to a Workspace that is connected to Microsoft Sentinel. I have created a Transformation DCR / Ingestion Time Filter on the SecurityEvents table, but am still seeing events in the logs that should have been filtered out.

I ran into almost the exact same issue with the Syslog table a few months ago and got a workaround from Microsoft support. However, I don't see how to use the same workaround here.

Ultimately, the SecurityEvent transformation is in place, and I can see that it filters out logs in the wizard. But I also still get all of the logs showing up in the SecurityEvents table.

The current transformation rule is this:

source
| where EventID != 4663 or (EventID == 4663 and (ObjectName !startswith "C:\\" and ObjectName !startswith "\\Device"))

But again, I continue to get all instances of event ID 4663, even where ObjectName does start with "C:"

James Hamil 24,136 Reputation points Microsoft Employee

2024-08-09T19:57:04.5033333+00:00

Hi @Greg Sneed , thanks for the question. I'll investigate this and get back to you as soon as possible.

Best,

James
Andrew Blumhardt 9,856 Reputation points Microsoft Employee

2024-08-12T11:39:56.91+00:00

I can try to look at this closer later today. I suspect it the nested and statements might be tripping up the filter. Maybe the same result can be achieved with less nesting.
Greg Sneed 20 Reputation points

2024-08-12T18:28:18.13+00:00

@Andrew Blumhardt Thanks for checking into this. I will say that when I run the query in the Transformation rule wizard, the 10 sample records returned do get filtered down if any of those 10 match the query criteria.

Any insight is appreciated. Right now my bill is on track to be about 25% higher than what it used to be.

Another note that may be relevant, is that this seems to have become an issue when I migrated from the OMS agent to AMA agent.

2 answers

James Hamil 24,136 Reputation points Microsoft Employee

2024-08-13T22:41:24.8133333+00:00

Hi @Greg Sneed , if you are trying to use Event collection using DCR - XPath could be used to filter the events - XPath queries for Windows Event Collection

For Microsoft Sentinel based security event collection XPath queries are used too as shown below:

You may have to use the XPath queries and not KQL to filter in events which need to be ingested.

Please let me know if you have any questions and I can help you further.

If this answer helps you please mark "Accept Answer" so other users can reference it.

Thank you,

James
Please sign in to rate this answer.
Greg Sneed 20 Reputation points

2024-08-14T11:50:35.5766667+00:00

Hello,

Thank you for the reply. Does that mean that the KQL ingestion filters are broken?

Currently, I'm using AMA deployed via ARC to ingest the "Common" subset of Security Event logs. Here's a screenshot of that setting:

I believe setting the Data Collection Rule to Common creates XPath filters to only ingest specific events.

It would be non-trivial to have to edit that DCR and make sure it works as desired. I'd be much easier if I could set the DCR to Common (as shown) and also use the SecurityEvent table ingestion time filter to further filter events.

It sounds like I have to pick one or the other? Is this a documented limitation? If I can only use one of the two, I'd rather use the table transformation.

The transformation wizard appears to effectively filter the events (show below)

Greg Sneed 20 Reputation points

2024-08-14T12:49:58.95+00:00

Does this mean that the Kusto Transformation filters just don't work?

I'm already setting the SecurityEvents Data Collection Rule to "common", and would rather not have to recreate a custom XPath filter that includes all of those events.

I've spent an hour or so looking through XPath filters (which are very limited), and I don't think they can filter to the level I need. I really need the Kusto Transformation filters to work as advertised.

Greg Sneed 20 Reputation points

2024-08-15T20:27:02.3033333+00:00

@James Hamil Please see my last few comments. XPath filters do not provide the granularity I need.

Givary-MSFT 32,291 Reputation points Microsoft Employee

2024-08-20T15:30:45.8533333+00:00

@Greg Sneed Please send me an email to 'AzCommunity@microsoft.com' with Sub - Attn: Givary and following details in the email body:

Link to this thread/post

We can connect offline and discuss further on this.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Andrew Blumhardt 9,856 Reputation points Microsoft Employee

2024-08-22T13:07:35.4866667+00:00
I think we are getting a bit off track.

You are using the default DCR rule "Common". The associated events can be seen in the Azrue Monitor documentation. Technically you could modify the DCR rule to filter out unwanted events. Though in this case, your filter would require a complex XPATH query. This sees overly complicated.

You could also look at your local audit policy. That determines the volume and type of security events. If you are seeing too many 4663, maybe your audit policy is too aggressive.

Your ingestion filter should work. The problem is either a bug or a limitation of the filter. You may consider opening a support case. You can also try alterative queries to produce the same or similar results. See the example below.

| where EventID != 4663 or (EventID == 4663 and ObjectName !startswith "C:\\") or (EventID == 4663 and ObjectName !startswith "\\Device")
Please sign in to rate this answer.
Greg Sneed 20 Reputation points

2024-08-22T18:48:56.1233333+00:00

Thanks for the update. I work for an MSSP, so in most cases I don't have direct access to the servers, or the permissions to make logging configuration changes directly on those servers. I'm pretty much limited to what I can do from the Azure portal.

I'm testing now with the proposed changes. However, after waiting a few minutes, I still see the logs that should be filtered out hitting the table. As a precaution, I deleted the transformation filter on the table, then created it new again.

Andrew Blumhardt 9,856 Reputation points Microsoft Employee

2024-08-27T13:00:28.31+00:00

Logic operators are not specifically listed as supported. https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/data-collection-transformations-structure#kql-limitations

Chatting with some peers, it seems like logic operators are known by others as an issue. It may not be possible to accomplish this using a transform. The only way without a logic operator is something like this but I assume Union is not supported either.

let Clean443 = SecurityEvent | where EventID == 4663 | extend FilterX = case(ObjectName !startswith "C:\\", 1, ObjectName !startswith "\\Device", 1, 0); SecurityEvent | where EventData != 4663 | union Clean443 | where FilterX != 0 | project-away FilterX

Greg Sneed 20 Reputation points

2024-08-29T13:31:00.13+00:00

Ah, I was not aware of the lack of support for logic operators. I tried adding your query, but the Kusto query window won't even let me use it. However, going through the documentation you provided, I think I have something that should work.

Take a look at this and let me know if you see any issues.

source // Check to see if this is an event we want to filter based on EventID | extend filteredEventID = iif(EventID == 4663,1,0) // Next check to see if it's also one too filter by path | extend filteredPath = case(ObjectName startswith "C:\\", 1, ObjectName startswith "\\Device", 1, 0) // get rows to filter | extend FilterMe = iif(filteredEventID == 1,iif(filteredPath==1,true,false),false) // Filter out the rows in question | where FilterMe == false // Remove the filter fields | project-away FilterMe, filteredPath, filteredEventID

I'm wondering if there could be issues with nested iif statements. Another option that doesn't use nesting would be to use a binary_and operator and replace the iif rows with this: |extend FilterMe = binary_and(filteredEventID,filteredPath) == 1

However, I'd have to start nesting again if I had to add any other filter criteria. For now, I'll try testing with the nested iff statement and see how it goes.

Greg Sneed 20 Reputation points

2024-08-29T16:10:12.69+00:00

I've updated the filter with both variations testing for about an hour each way. So far, all of the events are still hitting the SecurityEvent table. So the previous updates do not appear to work.

I'll start working on getting a ticket going but would like to keep working here as well. Open to suggestions on where to go next.

Givary-MSFT 32,291 Reputation points Microsoft Employee

2024-08-29T16:14:20.65+00:00

@Greg Sneed Please help me with the case number to track it internally.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SecurityEvent Table Transformation DCR not working

2 answers

Your answer