DCR transformation querry not applying due to mismatch with column type in outputstream

Rubeste 106 Reputation points
2023-11-16T10:15:03.99+00:00

Recently I have encountered when creating a new custom table in Log analytics Workspace. Eventhough I am creating a new table I get the error as described below:

{
  "code": "InvalidPayload",
  "message": "Data collection rule is invalid",
  "details": [
    {
      "code": "InvalidTransformOutput",
      "target": "properties.dataFlows[0]",
      "message": "Types of transform output columns do not match the ones defined by the output stream: DvcGeoLat [String], DvcGeoLong [String]"
    },
    {
      "code": "InvalidTransformOutput",
      "target": "properties.dataFlows[2]",
      "message": "Types of transform output columns do not match the ones defined by the output stream: DvcGeoLat [String], DvcGeoLong [String]"
    }
  ]
}

I did change the table definitions a while ago for other tables that also use this DCR, but as far as I know other tables should not interfere with other tables.

SampleLog:

[
    {
        "t-pot_ip_int": "REDACTED",
        "src_ip": "REDACTED",
        "geoip_ext": {
            "as_org": "REDACTED",
            "timezone": "REDACTED",
            "country_code3": "REDACTED",
            "country_name": "REDACTED",
            "continent_code": "REDACTED",
            "country_code2": "REDACTED",
            "region_name": "REDACTED",
            "ip": "REDACTED",
            "postal_code": "REDACTED",
            "asn": 0,
            "region_code": "REDACTED",
            "location": {
                "lon": 0.0,
                "lat": 0.0
            },
            "city_name": "REDACTED",
            "latitude": 0.0,
            "longitude": 0.0
        },
        "path": "/data/mailoney/log/commands.log",
        "data": "EHLO [REDACTED]",
        "host": "REDACTED",
        "type": "Mailoney",
        "smtp_input": [],
        "t-pot_ip_ext": "REDACTED",
        "geoip": {
            "as_org": "REDACTED",
            "timezone": "REDACTED",
            "country_code3": "REDACTED",
            "country_name": "REDACTED",
            "continent_code": "REDACTED",
            "country_code2": "REDACTED",
            "region_name": "REDACTED",
            "ip": "REDACTED",
            "postal_code": "REDACTED",
            "asn": 0,
            "region_code": "REDACTED",
            "location": {
                "lon": 0.0,
                "lat": 0.0
            },
            "city_name": "REDACTED",
            "latitude": 0.0,
            "longitude": 0.0
        },
        "dest_port": 25,
        "src_port": 36689,
        "timestamp": "2023-06-16T03:01:29.000000Z",
        "t-pot_hostname": "REDACTED",
        "ls_timestamp": "2023-06-16T03:01:29.000Z",
        "ls_version": "1"
    }
]

Transformation query

source
| project-rename 
    TimeGenerated = timestamp,
    DvcExtIp = ['t-pot_ip_ext'],
    DvcIntIp = ['t-pot_ip_int'],
    DvcHost = ['t-pot_hostname'],
    SrcIp = src_ip,
    SrcPort = src_port,
    TargetPort = dest_port,
    TPotType = ['type'],
    LsVersion = ls_version,
    RawData = data,
    SmtpInput=smtp_input
| extend 
    SrcGeoCountry = tostring(geoip.country_name),
    SrcGeoCode = tostring(geoip.country_code2),
    SrcGeoLat = toreal(geoip.latitude),
    SrcGeoLong = toreal(geoip.longitude),
    SrcGeoCity = tostring(geoip.city_name),
    SrcGeoRegion = tostring(geoip.region_name),
    DvcGeoCountry = tostring(geoip_ext.country_name),
    DvcGeoCode = tostring(geoip_ext.country_code2),
    DvcGeoLat = toreal(geoip_ext.latitude),
    DvcGeoLong = toreal(geoip_ext.longitude),
    DvcGeoCity = tostring(geoip_ext.city_name),
    DvcGeoRegion = tostring(geoip_ext.region_name)
| project-away host, headers, path, geoip, geoip_ext, ls_timestamp
| extend EventType= case(
                        RawData startswith 'ehlo',
                        'EHLO',
                        RawData startswith 'mail from',
                        'MAIL FROM',
                        RawData startswith 'rcpt to',
                        'RCPT TO',
                        RawData startswith 'data',
                        'DATA',
                        RawData startswith 'noop',
                        'NOOP',
                        RawData startswith 'help',
                        'HELP',
                        RawData startswith 'vrfy',
                        'VRFY',
                        RawData startswith 'expn',
                        'EXPN',
                        RawData startswith 'quit',
                        'QUIT',
                        RawData startswith 'starttls',
                        'STARTTLS',
                        RawData startswith 'auth',
                        'AUTH',
                        RawData startswith 'atrn',
                        'ATRN',
                        RawData startswith 'bdat',
                        'BDAT',
                        RawData startswith 'etrn',
                        'ETRN',
                        'UNKNOWN'
                    )


UPDATE

I think I discovered the flaw. It has nothing to do with the new table I want to add. It has to do with two other tables that use this DCR. The tables in the LAW were made with a string type for the LAT en LONG. When I discovered this flaw I changed them to the real type and modified the transformation queries. But something must have gone wrong. The sample logs and transformation queries are basically the same as the one described above. The only difference being that the LAT & LONG are converted to string instead of real. I tried to update this transformation query by redeploying an ARM template but got the same error as stated above.

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,926 questions
Azure
Azure
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
1,035 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Rubeste 106 Reputation points
    2023-11-16T13:21:46.82+00:00

    As it turns out the problem was not the creation of a new Table. It had to do with two existing tables that somehow still had the old configuration. This was verry strange as when I look at the table schema it had the propper type denoted as real instead of 'string'. To fix this I did the following:

    1. Search for the affected tables.
    2. Edit schema.
    3. Remove the affected columns.
    4. Save.
    5. Deploy Transformation query update (changed the converted type from string to real)
    6. Edit schema.
    7. Add the affected columns with the correct type.
    2 people found this answer helpful.