I already touched on creating bags in KQL here: How to create an empty bag in KQL?

However, this does not work for KQL transform queries:#

{
  "error": {
    "code": "InvalidPayload",
    "message": "Data collection rule is invalid",
    "details": [
      {
        "code": "InvalidTransformQuery",
        "target": "properties.dataFlows[0]",
        "message": "Error occurred while compiling query in query: SyntaxError:0x00000001 at 7:118 : extraneous input 'dynamic' expecting {'(', '*', '[', '+', '-', 'access', 'accounts', 'add', 'admin', 'admins', 'alias', 'all', 'alter', 'alter-merge', 'append', 'async', 'attach', 'basicauth', 'bin', 'cache', 'caching', 'capacity', 'case', 'categorize', 'cluster', 'column', 'columns', 'columnifexists', 'commands', 'commpools', 'completed', 'compressed', 'count', 'crash', 'create', 'createdon', 'csl', 'cslschema', 'csv', 'data', 'database', 'databasecreators', 'databases', 'datatable', 'declare', 'decryption-certificate-thumbprint', 'define', 'delete', 'detach', 'details', 'diagnostics', 'disable', 'docstring', 'drop', 'drop-pretend', 'dup-next-failed-ingest', 'dup-next-ingest', 'echo', 'effective', 'enable', 'encoding', 'entity', 'ephemeral', 'evaluate', 'except', 'execute', 'export', 'extend', 'extent', 'extentcontainers', 'extents', 'extentsmerge', 'fabric', 'fabriccache', 'fabricclocks', 'fabriclocks', 'facet', 'failures', 'filter', 'folder', 'force', 'fork', 'freshness', 'from', 'function', 'functions', 'groups', 'hash', 'hot', 'hotdata', 'hotindex', 'hours', 'id', 'if_later_than', 'ifexists', 'iff', 'ifnotexists', 'iif', 'ingest', 'ingestion', 'ingestions', 'ingestiontime', 'inline', 'into', 'join', 'json', 'keys', 'let', 'limit', 'load', 'local', 'mapping', 'mappings', 'memory', 'merge', 'metadata', 'monitoring', 'move', 'mvexpand', 'nan', 'none', 'not', 'null', 'nulls', 'on', 'older', 'operations', 'order', 'pack', 'parse', 'password', 'pattern', 'persist', 'plugin', 'plugins', 'policies', 'policy', 'pretend', 'prettyname', 'principal', 'principals', 'print', 'project', 'project-away', 'project-rename', 'purge', 'query', 'query_parameters', 'queryexecution', 'queryplan', 'querythrottling', 'quick', 'range', 'readonly', 'readwrite', 'rebalance', 'rebalance-pretend', 'rebuild', 'recycle', 'reduce', 'rename', 'render', 'replace', 'reset', 'restrict', 'retention', 'roles', 'roworder', 'roworderpolicy', 'running', 'save', 'schema', 'script', 'set-or-append', 'set-or-replace', 'show', 'sort', 'state', 'step', 'stream', 'summarize', 'storage', 'table', 'tables', 'tags', 'take', 'tempstorage', 'threshold', 'throw', 'toscalar', 'totable', 'trace', 'traceresults', 'tsv', 'type', 'typeof', 'undo', 'union', 'update', 'user', 'users', 'uuid', 'version', 'view', 'volatile', 'warm', 'warming', 'whatif', 'where', 'with', 'bool', 'guid', LONGLITERAL, INTLITERAL, REALLITERAL, STRINGLITERALX, BOOLEANLITERAL, DATETIMELITERAL, TIMESPANLITERAL, TYPELITERAL, GUIDLITERAL, IDENTIFIER}\r\nSyntaxError:0x00000004 at 7:126 : no viable alternative at input '({'\r\nSyntaxError:0x00000003 at 7:130 : mismatched input ')' expecting <EOF>"
      }
    ]
  }
}

This is the line it complains about:

| extend Context = todynamic(iif(ContextStr startswith_cs "{" and ContextStr endswith_cs "}", parse_json(ContextStr), dynamic({})))

It looks like dynamic({}) is not supported in transform queries for Azure Monitor Data Collection Rules, even though it did work when querying Log Analytics (I copy-pasted the transform query and replaced source by the table that has the RawData column)

How would I solve this without using parse_json("{}")?

I already touched on creating bags in KQL here: How to create an empty bag in KQL?

However, this does not work for KQL transform queries:#

{
  "error": {
    "code": "InvalidPayload",
    "message": "Data collection rule is invalid",
    "details": [
      {
        "code": "InvalidTransformQuery",
        "target": "properties.dataFlows[0]",
        "message": "Error occurred while compiling query in query: SyntaxError:0x00000001 at 7:118 : extraneous input 'dynamic' expecting {'(', '*', '[', '+', '-', 'access', 'accounts', 'add', 'admin', 'admins', 'alias', 'all', 'alter', 'alter-merge', 'append', 'async', 'attach', 'basicauth', 'bin', 'cache', 'caching', 'capacity', 'case', 'categorize', 'cluster', 'column', 'columns', 'columnifexists', 'commands', 'commpools', 'completed', 'compressed', 'count', 'crash', 'create', 'createdon', 'csl', 'cslschema', 'csv', 'data', 'database', 'databasecreators', 'databases', 'datatable', 'declare', 'decryption-certificate-thumbprint', 'define', 'delete', 'detach', 'details', 'diagnostics', 'disable', 'docstring', 'drop', 'drop-pretend', 'dup-next-failed-ingest', 'dup-next-ingest', 'echo', 'effective', 'enable', 'encoding', 'entity', 'ephemeral', 'evaluate', 'except', 'execute', 'export', 'extend', 'extent', 'extentcontainers', 'extents', 'extentsmerge', 'fabric', 'fabriccache', 'fabricclocks', 'fabriclocks', 'facet', 'failures', 'filter', 'folder', 'force', 'fork', 'freshness', 'from', 'function', 'functions', 'groups', 'hash', 'hot', 'hotdata', 'hotindex', 'hours', 'id', 'if_later_than', 'ifexists', 'iff', 'ifnotexists', 'iif', 'ingest', 'ingestion', 'ingestions', 'ingestiontime', 'inline', 'into', 'join', 'json', 'keys', 'let', 'limit', 'load', 'local', 'mapping', 'mappings', 'memory', 'merge', 'metadata', 'monitoring', 'move', 'mvexpand', 'nan', 'none', 'not', 'null', 'nulls', 'on', 'older', 'operations', 'order', 'pack', 'parse', 'password', 'pattern', 'persist', 'plugin', 'plugins', 'policies', 'policy', 'pretend', 'prettyname', 'principal', 'principals', 'print', 'project', 'project-away', 'project-rename', 'purge', 'query', 'query_parameters', 'queryexecution', 'queryplan', 'querythrottling', 'quick', 'range', 'readonly', 'readwrite', 'rebalance', 'rebalance-pretend', 'rebuild', 'recycle', 'reduce', 'rename', 'render', 'replace', 'reset', 'restrict', 'retention', 'roles', 'roworder', 'roworderpolicy', 'running', 'save', 'schema', 'script', 'set-or-append', 'set-or-replace', 'show', 'sort', 'state', 'step', 'stream', 'summarize', 'storage', 'table', 'tables', 'tags', 'take', 'tempstorage', 'threshold', 'throw', 'toscalar', 'totable', 'trace', 'traceresults', 'tsv', 'type', 'typeof', 'undo', 'union', 'update', 'user', 'users', 'uuid', 'version', 'view', 'volatile', 'warm', 'warming', 'whatif', 'where', 'with', 'bool', 'guid', LONGLITERAL, INTLITERAL, REALLITERAL, STRINGLITERALX, BOOLEANLITERAL, DATETIMELITERAL, TIMESPANLITERAL, TYPELITERAL, GUIDLITERAL, IDENTIFIER}\r\nSyntaxError:0x00000004 at 7:126 : no viable alternative at input '({'\r\nSyntaxError:0x00000003 at 7:130 : mismatched input ')' expecting <EOF>"
      }
    ]
  }
}

This is the line it complains about:

| extend Context = todynamic(iif(ContextStr startswith_cs "{" and ContextStr endswith_cs "}", parse_json(ContextStr), dynamic({})))

It looks like dynamic({}) is not supported in transform queries for Azure Monitor Data Collection Rules, even though it did work when querying Log Analytics (I copy-pasted the transform query and replaced source by the table that has the RawData column)

How would I solve this without using parse_json("{}")?

• kql
• azure-log-analytics
• azure-monitoring

1 Answer 1

In place of dynamic({})) , you can use todynamic('{}') as below:

let rithTable = datatable(ContextStr:string)
[
    '{"rith": "8", "cho": "7"}',
    '{"test": "tu", "tt": "wik"}',
    'invalid json string',
    '',
    '{"hi": "hello"}'
];
rithTable
| extend Context = todynamic(iif(ContextStr startswith_cs "{" and ContextStr endswith_cs "}", parse_json(ContextStr), todynamic('{}')))

Output:

Fiddle.

So, you can use todynamic('{}') and parse_json("{}") instead of dynamic({})).