How to collect the JSON log file to Log analytics workspace custom table using DCR?

Question

I will collect the log file (format JSON) to Log analytics workspace custom table based Azure monitor DCR, follow this doc :https://learn.microsoft.com/en-us/azure/azure-monitor/agents/data-collection-text-log?tabs=portal,but the custom table some column is null.The JSON file is:

---

[
  {
    "TimeGenerated": "2023-11-13T08:42:50Z",
    "fields": {
      "Buffer pool": "15.90",
      "Cache (objects)": "0.60",
      "Cache (sql plans)": "35.60",
      "Other": "48.10"
    },
    "name": "Memory breakdown (%)",
    "tags": {
      "host": "telegraf",
      "servername": "WIN-MVE00BSRF5S",
      "type": "Memory clerk"
    }
  },
  {
    "TimeGenerated": "2023-11-13T08:42:50Z",
    "fields": {
      "Buffer pool": "72744960.00",
      "Cache (objects)": "2842624.00",
      "Cache (sql plans)": "162807808.00",
      "Other": "220069888.00"
    },
    "name": "Memory breakdown (bytes)",
    "tags": {
      "host": "telegraf",
      "servername": "WIN-MVE00BSRF5S",
      "type": "Memory clerk"
    }
  }
]

1.I have create the custom table in LA,The table have TimeGenerated, fields, tags, name, RawData column.

2.I have create the Custom JSON logs DCR and transform is :source, but the custom table only TimeGenerated data.

3.change the transdform is :source | extend d=parse_json(RawData), but the custom table only RawData column have data(JSON format), other column is null.

4.chagnge the transform is:source | extend d=todynamic(RawData) | project TimeGenerated = TimeGenerated,name = tostring(d.name),fields = todynamic(d.fields),tags = todynamic(d.tags), same as source | extend d=parse_json(RawData).

5.change the transform is

source | extend TimeGenerated = TimeGenerated | extend fields = parse_json(fields) | extend Name = name | extend tags = parse_json(tags) | project TimeGenerated, fields, Name, tags

but save DCR error: Undefined symbol:fields

The table have fields, tags, name column, but always no data.

How to write the right DCR transform to store the JSON log to custom table in LA?

Answer 1

Jie Yin, thank you for posting this query here on Microsoft Q&A.

I used the JSON logs shared in question and was able to get the logs in new columns created:

I used the following transform query:

source | extend d=todynamic(RawData) | project fields = tostring(d.fields), tags = tostring(d.tags), name = tostring(d.name), TimeGenerated = todatetime(d.TimeGenerated)

The following are things which needs to be take care in this scenario:

1. json RawData needs to be converted to dynamic type for it to be available for querying and populating new columns.
2. Because the TimeGenerated column is being overwritten, ensure that the value being passed on your local machine are indeed correct UTC time. Otherwise, it might create confusion when you try to query Log Analytics workspace using order by clause.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.