{"id":73,"date":"2022-12-23T14:49:54","date_gmt":"2022-12-23T14:49:54","guid":{"rendered":"https:\/\/mortenknudsen.net\/?p=73"},"modified":"2023-04-10T11:35:25","modified_gmt":"2023-04-10T10:35:25","slug":"data-transformation-in-azure-loganalytics","status":"publish","type":"post","link":"https:\/\/mortenknudsen.net\/?p=73","title":{"rendered":"How to do data transformation with Azure LogAnalytics – to enrich information, optimize cost, remove sensitive data?"},"content":{"rendered":"\n

One of the cool features in Azure LogAnalytics is the capability to do data-transformation before the data enters your LogAnalytics workspaces<\/strong>. <\/p>\n\n\n\n

If you want to see my latest blog-series of articles about ‘mastering Azure logging’, please check the overview here<\/a> <\/p>\n\n\n\n

If you want to see my latest tutorial on setting up data transformation, please check it out here<\/a><\/p>\n\n\n\n

I use this feature to normalize data and enrich data for Microsoft Sentinel environments and optimize costs by removing “noise” in logs. I have also used it to temporary stop millions of unnecessary log events caused by application errors or misconfigurations. Then I could investigate and fix the root cause of the issue.<\/p>\n\n\n\n

To summarize, this feature can help your with:<\/p>\n\n\n\n

Category<\/strong><\/td>Details<\/strong><\/td><\/tr>
Remove sensitive data<\/td>You may have a data source that sends information you don’t want stored for privacy or compliancy reasons.

Filter sensitive information.<\/strong> Filter out entire rows or just particular columns that contain sensitive information.

Obfuscate sensitive information<\/strong>. For example, you might replace digits with a common character in an IP address or telephone number.<\/td><\/tr>
Enrich data with additional or calculated information<\/td>Use a transformation to add information to data that provides business context or simplifies querying the data later.

Add a column with additional information.<\/strong> For example, you might add a column identifying whether an IP address in another column is internal or external.

Add business specific information.<\/strong> For example, you might add a column indicating a company division based on location information in other columns.<\/td><\/tr>
Reduce data costs<\/td>Since you’re charged ingestion cost for any data sent to a Log Analytics workspace, you want to filter out any data that you don’t require to reduce your costs.

Remove entire rows.<\/strong> For example, you might have a diagnostic setting to collect resource logs from a particular resource but not require all of the log entries that it generates. Create a transformation that filters out records that match a certain criteria.

Remove a column from each row.<\/strong> For example, your data may include columns with data that’s redundant or has minimal value. Create a transformation that filters out columns that aren’t required.

Parse important data from a column.<\/strong> You may have a table with valuable data buried in a particular column. Use a transformation to parse the valuable data into a new column and remove the original.   Examples of where data-transformation is useful: We want to remove specific security-events from a server, which are making lots of \u201dnoise\u201d in our logs due to a misconfiguration or error and it is impossible to fix itWe want to remove security events, which we might show with a high amount, but we want to filter it out like kerberos computer-logon traffic.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Disclaimer about security & data-transformation<\/strong>
From a security hunting perspective, it is important to note, that you should be very careful with this, as you might remove data which will make security hunting harder \u2013 or even impossible if vital data is removed or table structure is altered.

Please do proper testing and be aware of the consequences.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Supported ways of doing data-transformation<\/h2>\n\n\n\n

Currently, there are support for doing data-transformation using 4 methods:<\/p>\n\n\n\n

    \n
  1. XPath filtering using Azure Monitor Agent<\/li>\n\n\n\n
  2. Azure Monitor Agent (AMA) using TransformKql in Data Collection Rule (DCR)<\/li>\n\n\n\n
  3. Custom application sending data using logs ingestion API via DCR<\/li>\n\n\n\n
  4. Workspace transformation DCR<\/li>\n<\/ol>\n\n\n\n

    This blog will go deep-dive on method 2; \u2019Azure Monitor Agent (AMA) using TransformKql in Data Collection Rule (DCR)\u2019. I will make another post on method 4 soon.<\/p>\n\n\n\n

    Before going into configurering data-transformation, I want to touch on the \u201dold\u201d method using XPath (XML Path Language).<\/p>\n\n\n\n

    Transformation using XPath<\/strong><\/h2>\n\n\n\n

    Since AMA was launched in June 2021, it has supported doing transformation with XPath. This methods works fine and has helped my customers to save money.<\/p>\n\n\n\n

    \n

    Microsoft asked me to provide a statement about this in the officiel go-live announcement, when Azure Monitor Agent went GA in June 2021 (https:\/\/techcommunity.microsoft.com\/t5\/azure-observability-blog\/a-powerful-agent-for-azure-monitor-and-a-simpler-world-of-data\/ba-p\/2443285<\/a>).<\/p>\n<\/blockquote>\n\n\n\n

    Here is an example of using XPath where I am collecting all security events, excluding any computer-related security-events like Computer Kerberos logon-traffic<\/p>\n\n\n\n

    \"Security!*[System[(band(Keywords,13510798882111488))]] and *[(EventData[Data[@Name='TargetUserName'] != 'EXCHANGE01$']) and (EventData[Data[@Name='TargetUserName'] != 'EXCHANGE02$']) and (EventData[Data[@Name='TargetUserName'] != 'EXCHANGE03$']) and (EventData[Data[@Name='TargetUserName'] != 'EXCHANGE04$']) and (EventData[Data[@Name='TargetUserName'] != 'EXCHANGE05$']) ]\"<\/code><\/pre>\n\n\n\n
    AMA is still supporting XPath, but I recommend to use data transformation using DCR-rules, as it solves some of the limitations in the XPath implementation in AMA. <\/p>\n\n\n\n
    XPath is a standard, currently at 3.1 (2017), but Microsoft chose to implement XPath v1 in AMA from 1999. V1 doesn\u2019t support filtering with like<\/strong> or contains<\/strong>. Therefore it is hard to scale using xpath v1, if you need advanced features. <\/p>\n\n\n\n
    In the sample above, I had to explicitly define the server-names where I wanted to remove computer-related security events.<\/p>\n\n\n\n
    How does transformation work using AMA & DCR ?<\/h2>\n\n\n\n
    The main difference between XPath and data-transformation using DCR is where it runs, as XPath filtering is done on the endpoint, whereas data-transformation using a DCR runs in the Azure backend in a dedicated pipeline per DCR – being applied on each row of data coming in – before data is sent into Loganalytics.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Before going into the details on how to create a data-transformation in data collection rules, we will recap the principles about Data Collection Rules (DCRs) and data transformations.<\/p>\n\n\n\n
    \n
    Any resource using AMA is always collecting the union of all DCRs.<\/em><\/p>\n\n\n\n
    This way multiple teams \/ roles can enable what they need, without impacting other teams. If Microsoft switched the behavior, one team could disable other collection by accident.<\/em> <\/em><\/p>\n\n\n\n
    Therefore a transformation will only impact that particular workspace where the transformation applies.<\/em><\/p>\nIf you – by mistake – have 2 DCRs for SecurityEvent collection and use one of them for transformation, then you will end up having double of the events (except for the filtered away events). <\/cite><\/blockquote>\n\n\n\n
    As mentioned earlier, AMA will union all DCRs, so it is important, that you have a good overview of the current associated DCRs associated with the endpoints. Which data is being collected into which tables ? <\/p>\n\n\n\n
    Implementation of Data Collection using Data Collection Rules (DCRs)<\/h2>\n\n\n\n
    This blog will not go into details on how to configure a data collection rule, but for reading purpose I will touch on this high-level.<\/p>\n\n\n\n
    Normally, I\u2019m configuring a set of default DCRs that are being applied to all servers, both native Azure VMs and Azure Arc-enabled servers \u2013 for example:<\/p>\n\n\n\n