SecurityEvent Table Transformation DCR not working

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)

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.

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

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.

Logic operators are not specifically listed as supported. https://video2.skills-academy.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 

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.

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.

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