DQL parse of log not getting complete data.

[rseibert1]

The log below has multiple entries I am only getting the first set with my parse, seems I am missing a way to make it iterate through to the end?

{
"content": {
"alertText": [
{
"Id": "Workers-22",
"SpaceId": "Spaces-1",
"WorkerPoolIds": [
"WorkerPools-42"
],
"HealthStatus": "HasWarnings",
"HasLatestCalamari": true,
"StatusSummary": "This machine is running an old version of Tentacle (xxxxx).",
"OperatingSystem": "Microsoft Windows Server 2019 Standard xxxxxx"
},
{
"Id": "Workers-23",
"SpaceId": "Spaces-1",
"WorkerPoolIds": [
"WorkerPools-42"
],
"HealthStatus": "HasWarnings",
"HasLatestCalamari": false,
"StatusSummary": "This machine is running an old version of Tentacle (6.1.1403).",
"OperatingSystem": "Microsoft Windows Server 2019 Standard xxxxx"
},

DQL:

fetch logs
| filter contains(log.tag, "octopus_workers")
| fields timestamp, content
| parse content, "DATA '[{\"Id\":\"' DATA:Id '\",\"SpaceId\":\"' DATA:SpaceId '\",\"WorkerPoolIds\":[\"' DATA:WorkerPoolIds '\"],\"HealthStatus\":\"' DATA:HealthStatus '\",\"HasLatestCalamari\":' DATA:HasLatestCalamri ',\"StatusSummary\":\"' DATA:StatusSummary '\",\"OperatingSystem\":\"' DATA:OperatingSystem '\"},'"

[PacoPorro]

Is there any problem with using the JSON parser?
https://docs.dynatrace.com/docs/shortlink/dql-use-cases#parse-json-data-and-aggregate-records

[rseibert1]

I am not able to get JSON parser to do what I want either, 

[rseibert1]

Seems like I am closer to a solution with the parse content approach but it fails to loop through the whole log, only getting first instance of all the fields:

fetch logs
| filter contains(log.tag, "octopus_workers")
| parse content, """DATA '\"Id\":\"' DATA:Id '\",\"SpaceId\":\"' DATA:SpaceId '\",\"WorkerPoolIds\":[\"' DATA:WorkerPoolIds '\"],\"HealthStatus\":\"' DATA:HealthStatus '\",\"HasLatestCalamari\":' DATA:HasLatestCalamri ',\"StatusSummary\":\"' DATA:StatusSummary '\",\"OperatingSystem\":\"' DATA:OperatingSystem '\"},'"""
| fields Id, SpaceId, WorkerPoolIds, HealthStatus, StatusSummary, OperatingSystem

[PacoPorro]

For the json parser, Won't be something like json.alertText[0].SpaceId ?
Please check https://community.dynatrace.com/t5/DQL/Json-parsing-in-DQL/m-p/242105

[rseibert1]

I tried several solutions along those lines but all of them return null. I must be missing something ?

[rseibert1]

yes, I found that page, I tried a few different ways to get a single field pulled from the json and not sure what I am missing?

[Tiit_Hallas]

The main issue seems to be that the JSON object is not complete (I presume this is due to copying a partial result of the content. If you right-click on the partial content in the Security Investigator and view field details, you can see it as a raw string with line-breaks, not as a JSON object: 

If I added the proper ending to the JSON (added "]}}"), the field details looks better:

And then the JSON matcher parses it perfectly as well, see the attached screenshot from the Security Investigator record details:

To extract single elements from a JSON array to separate fields, you can use the JSON and JSON_ARRAY matchers and if you want, you can also extract only specific fields by defining them to the pattern.

The final pattern that I created looked like this: 

JSON{ 
  JSON{
    JSON_ARRAY{ 
      JSON{ 
        STRING:Id, 
        STRING:SpaceId, 
        STRING:HealthStatus
      } 
    }:alertText
  }(flat=true):content 
}(flat=true)

I used the "flat=true" to unnset the objects + defined the field. After that you can use DQL commands "expand" and "fieldsFlatten" to spread the array elements to different records and to flatten the object fields to separate columns. 

I've shared the Security Investigation case in the Playground with you as well, you can see all the steps that I took by navigating the query tree in the right. 

https://wkf10640.apps.dynatrace.com/ui/apps/dynatrace.security.investigator/share/b9709361-dd10-4739...