Transformation on Table ASimDnsActivityLogs not working

[Blutsh]

Describe the bug
Creating a Transformation on table ASimDnsActivityLogs to filter out logs based on a simple condition :

source
| where DstIpAddr !startswith "192."

Does not work (Event are not being filtered even after few hours).

To Reproduce
Steps to reproduce the behavior:

1. Configure the "Windows DNS Events via AMA" from sentinel portal.
2. Check that logs are flowing by checking content of table ASimDnsActivityLogs
3. Test your KQL against the logs
4. Go to your log Analytics workspace > Tables
5. Select ASimDnsActivityLogs > Edit Transformation
6. Assign the transformation to a WorkspaceWide DCR, insert the transformation (given above)
7. Wait for 30min (so the changes are effective)
8. Check that events are not filtered out as they should

Expected behavior
I should not see logs in Table ASimDnsActivityLogs where DstIpAddr starts with "192."

ARM Dcrs

DCR generated by DataConnector "Windows DNS Events via AMA" :

{
    "properties": {
        "immutableId": "dcr-3768b0cace0c4122b2b256db67d4e709",
        "dataSources": {
            "extensions": [
                {
                    "streams": [
                        "Microsoft-ASimDnsActivityLogs"
                    ],
                    "extensionName": "MicrosoftDnsAgent",
                    "extensionSettings": {
                        "Filters": []
                    },
                    "name": "ASimDnsActivityLogsTypeExtension"
                }
            ]
        },
        "destinations": {
            "logAnalytics": [
                {
                    "workspaceResourceId": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/rg_sentinel/providers/Microsoft.OperationalInsights/workspaces/lab-esxi",
                    "workspaceId": "xxxxxxxxxxxxxx",
                    "name": "DataCollectionEvent"
                }
            ]
        },
        "dataFlows": [
            {
                "streams": [
                    "Microsoft-ASimDnsActivityLogs"
                ],
                "destinations": [
                    "DataCollectionEvent"
                ]
            }
        ],
        "provisioningState": "Succeeded"
    },
    "location": "somewhere",
    "tags": {},
    "kind": "Windows",
    "id": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/RG_Sentinel/providers/Microsoft.Insights/dataCollectionRules/xxxxxxxxxxxxxx-ms-sentinel-asimdnsactivity",
    "name": "xxxxxxxxxxxxxx-ms-sentinel-asimdnsactivity",
    "type": "Microsoft.Insights/dataCollectionRules",
    "etag": "\"0a00f893-0000-1700-0000-65cf14f00000\"",
    "systemData": {
        "createdBy": "johndoe@doe.com",
        "createdByType": "User",
        "createdAt": "2024-02-16T07:55:26.3377829Z",
        "lastModifiedBy": "johndoe@doe.com",
        "lastModifiedByType": "User",
        "lastModifiedAt": "2024-02-16T07:55:26.3377829Z"
    }
}

WorkspaceWide DCR created trough the portal :

{
    "properties": {
        "immutableId": "dcr-e899d9e9e6a94f999bbf6e03a5e33df1",
        "dataSources": {},
        "destinations": {
            "logAnalytics": [
                {
                    "workspaceResourceId": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourcegroups/rg_sentinel/providers/microsoft.operationalinsights/workspaces/lab-esxi",
                    "workspaceId": "xxxxxxxxxxxxxx",
                    "name": "2a8df5c5fc844fdcbca44b214cdbc33c"
                }
            ]
        },
        "dataFlows": [
            {
                "streams": [
                    "Microsoft-Table-ASimDnsActivityLogs"
                ],
                "destinations": [
                    "2a8df5c5fc844fdcbca44b214cdbc33c"
                ],
                "transformKql": "source\n| where DstIpAddr !startswith \"192.\"\n"
            }
        ],
        "provisioningState": "Succeeded"
    },
    "location": "somewhere",
    "kind": "WorkspaceTransforms",
    "id": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/RG_Sentinel/providers/Microsoft.Insights/dataCollectionRules/DCR_WorkspaceWide_esxi",
    "name": "DCR_WorkspaceWide_esxi",
    "type": "Microsoft.Insights/dataCollectionRules",
    "etag": "\"0a00a2c1-0000-1700-0000-65cf1b1f0000\"",
    "systemData": {
        "createdBy": "johndoe@doe.com",
        "createdByType": "User",
        "createdAt": "2024-02-16T08:21:48.5212847Z",
        "lastModifiedBy": "johndoe@doe.com",
        "lastModifiedByType": "User",
        "lastModifiedAt": "2024-02-16T08:21:48.5212847Z"
    }
}

Desktop (please complete the following information):

• OS: Windows 11
• Browser Firefox / Edge
• Version [e.g. 22]

Additional context
I did test in multiple environments, I also did try to create a TransformKql in the DCR created by the dataconnector -> doesnt work aswell.

[samet-ibis]

Hi @Blutsh, Can you please check on Tables page of Log Analytics Workspace? Do you have this error above of page?

I'm facing same issue with Syslog table for transforming CEF messages but its not working and writes CEF messages to my Syslog table as well. I just wonder are we facing same issue.

[Blutsh]

Hi @Blutsh, Can you please check on Tables page of Log Analytics Workspace? Do you have this error above of page? I'm facing same issue with Syslog table for transforming CEF messages but its not working and writes CEF messages to my Syslog table as well. I just wonder are we facing same issue.

Hello @t0neex, no I don't have this warning message on the table view, nor on the DCR view. I do have a blue informational message :

(By the way the link explicitly list the table "ASimDnsActivityLogs" as supported for ingestion time transformations)

[samet-ibis]

Hi @Blutsh, Can you please check on Tables page of Log Analytics Workspace? Do you have this error above of page? I'm facing same issue with Syslog table for transforming CEF messages but its not working and writes CEF messages to my Syslog table as well. I just wonder are we facing same issue.

Hello @t0neex, no I don't have this warning message on the table view, nor on the DCR view. I do have a blue informational message :

(By the way the link explicitly list the table "ASimDnsActivityLogs" as supported for ingestion time transformations)

Uhh okey, we're facing different issues. Thank you for your response, have a nice day :)

[v-sudkharat]

Hi @Blutsh, Thanks for flagging this issue, we will investigate this issue and get back to you with some updates by 29-02-2024. Thanks!

[v-sudkharat]

Hey @Blutsh, just want to check, while creating a DCR have you check the result get projected with your mentioned condition?
Could you please check it and let us know the result.
Sharing screenshot for your reference, here we have taken table which we have available into workspace-

And could you please share the sample data with us, so we can reproduce this issue from our end.
Thanks

[Blutsh]

@v-sudkharat Thanks for your update.

Here is my query showing results (as expected) in the Transformation Editor of the table "ASimDnsActivityLogs" :

So it works as intended.

Regarding the data sample, it is just some usual DNS queries (captured from a DC), I mean there is no special thing.

I don't really want to share a dataset of my entreprise lab activity here on GH, so if you really need it, please give me a way to do so "privately".

As described, I was able to reproduce this problem in two completely different environment (different machine/workspace/tenant).

[v-sudkharat]

@Blutsh, thank you for your response, we are investigating this issue and will share our mail to with you for sharing the data privately with us. Thanks!

[v-sudkharat]

Hi @Blutsh, Could you please follow below steps from MS documentation, it looks the table schema has been created but the data is not written into it. So could you please check it and let us know if still facing the issue. -
MS Doc link - https://learn.microsoft.com/en-us/azure/azure-monitor/logs/tutorial-logs-ingestion-portal

[Blutsh]

@v-sudkharat Thank you for your response,

I've gone through the documentation you provided, but unfortunately, I'm having trouble connecting the dots between the issue I'm experiencing and the suggested readings/actions.

Specifically, data is being written to the "ASimDnsActivityLogs" table, but it doesn't seem to be filtered by the DCR as expected.

I've successfully implemented similar DCR-based ingestion time log filtering on other tables without needing to set up permissions through IAM for an app before.

[v-sudkharat]

Hi @Blutsh, As per documentation you need the Microsoft.Insights/Telemetry/Write access to write the data into the tables. To address it could you please verify/follow the below steps and let us know if it's already compliant it in your end -
1.Go to your created DCR rule-

2. Click on Access Control (IAM) and select Add role assignment-
   

3. Select Monitoring Metrics Publisher > Next. You could instead create a custom action with the Microsoft.Insights/Telemetry/Write data action.
   

4.Select User, group, or service principal for Assign access to and choose Select members. Select the application that you created and then choose Select.

5. Select Review + assign and verify the details before you save your role assignment.
   

Once the roll gets added to DCR, please check the transformation/data filter is done for the table.
If issue is still persisted in your end, please share your mail id with us so we can connect with you via a call and troubleshoot the issue further.

Thanks!

[v-sudkharat]

Hi @Blutsh, Anything for us on above comment. Thanks!

[Blutsh]

Thank you for the detailed explanation provided earlier.

To summarize my ongoing issue:

I've been utilizing the official DataConnector "Windows DNS Events via AMA" installed via the Content Hub. Upon installation, I followed the instructions meticulously, as depicted in the screenshots below:

After clicking the "Apply changes" button, I received three notifications indicating the creation of a DCR and a DCRa, along with the deployment of a DNS agent through Arc on my selected resource (DC-lab in my case), as illustrated here:

Following the setup, I began receiving all the DNS logs from the DC in the table:

My objective then was to filter out unnecessary DNS events based on the "DstIpAddr" field, which wasn't feasible using the filter provided in the data connector itself.

Subsequently, I proceeded to my LAW > tables, selected the table "ASimDnsActivityLogs", ensuring that this specific table does support transformation.

Then, in the "basics" tab, I selected my WorkspaceWide DCR and tested my KQL intended to filter the desired events:

As evident in the screenshot, the KQL worked as expected, displaying only the events that adhere to the statement.

I verified the creation of the transformation by inspecting the JSON view of my Workspace DCR:

Despite waiting several hours (more than the recommended 30 minutes), I still observed logs where the DstIpAddr field begins with "192.", which is unexpected.

Throughout this process, I didn't encounter any prompts or requirements to create an Application, hence I am perplexed by the links/screen captures of API ingestion-related materials you provided.

Once again, I have successfully created multiple other ingestion time rules regarding other tables using the same process, and they function effectively in filtering events.

Here is my E-mail details : lasso.polymere.0j@icloud.com

[v-sudkharat]

Hi @Blutsh, Thank you for provide detailed info with us. We will replicate this issue from our end and will update you. Thanks!

[v-sudkharat]

Hi @Blutsh, we are reaching out to the respective DCR concerned team for this issue, once we receive an update on this, we will share with you. Thanks!

[Blutsh]

Hello @v-sudkharat, any update regarding this issue from your team ?
Regards,

[v-sudkharat]

Hi @Blutsh, Sorry for delay in response. Still, we are waiting response from our DCR team about the transformation, once we get any info from team, we will share with you via your shared mail ID or GitHub comment itself.
Thanks!

[dvag-oliver-kretz]

Hi,
we had a similar issue with our transformation. In our case we had 2 other dcrs, writing in the same log analytics workspace with the same stream (in our case Contsainer).
Do you have other DCRs which using Microsoft-ASimDnsActivityLogs as stream?
Microsoft advised us to use the transformation in all DCRs. (Or it is also possible to use one DCR also for the other sources)
I´m not sure but maybe it can help you...
Best Regards

[Blutsh]

@dvag-oliver-kretz
Hello,
Thanks for your input, AFAIK I did setup a lab only dedicated to this case where there was only one DCR for the workspace.

I'll take a look though.

[v-sudkharat]

Hey @Blutsh, Could you please verify is there any other DCR rules has been already configured, which is writing/sending the data into same table?
Please let us know once you verify, so we can share this update with team.
Thanks!

[Blutsh]

@v-sudkharat

TL:DR;
I reproduce my problem in a full new environment where I have only one DCR created by the data connector itself,
and another DCR created trough the transformation editor (like any other transformation works).

1. Set up a new LAW.
2. Set up a new Sentinel over the new LAW.
3. Deployed Arc through deployment script on my DC.
4. Deployed AMA agent through Arc Extension on my DC.
5. Added the two data connectors through Content Hub (Windows Server DNS).
6. Created a Data collection rule from the connector "Windows DNS Events via AMA" (Without any filters).
7. Added the DC as a resource to the DCR.
8. Checked incoming flow of logs (OK).
9. Created a Transformation on Table "ASimDnsActivityLogs" from the GUI in the same RG.
   a. Added my KQL in the transformation editor (which is: source | where DstIpAddr !startswith "192.").
   b. Verified in the Transformation Editor that my KQL works and does what I want it to do (OK).
   c. Actually created the Transformation.
10. Validated that my transformation has been taken into account by checking the JSON view of the DCR (WorkspaceTransforms) through the portal (OK).
11. Waited a 2 hours in order to let the change take effect.
12. Checked that there are no more logs where DstIpAddr does start with "192." -> NOK.

[v-sudkharat]

@Blutsh, thanks for sharing the info with us. We have forwarded these details to respective team and will update you once receive any information from team. Thanks!

[v-sudkharat]

Hi @Blutsh, We received the response from our concern team, in above mentioned scenario there are 2 DCR has been configured for a source :

1. The DCR created while configuring the (AMA) data connector.
   

2. The DCR created for table transformation.
   

And both the transformation points to the same table. So, the transformation will not work to the table ASimDnsActivityLogs as you have the dedicated DCR configured, the second workspace DCR will not apply.

Thanks!

[v-sudkharat]

Hi @Blutsh, Waiting for your response on above comment and please let us know if any questions for us. Or can we close this issue? Thanks!

[v-sudkharat]

Hi @Blutsh, Gentle Reminder: We are waiting for your response on this issue. If you still need to keep this issue active, please respond to it in the next 2 days. If we don't receive a response by 27-05-2024 date, we will be closing this issue.
Thanks!

[v-sudkharat]

Hi @Blutsh, since we have not received a response in the last 5 days, we are closing your issue as per our standard operating procedures. If you still need support for this issue, feel free to re-open at any time. Thank you for your co-operation.