05 Nov 2024 12:58 PM
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 '\"},'"
05 Nov 2024 01:34 PM
Is there any problem with using the JSON parser?
https://docs.dynatrace.com/docs/shortlink/dql-use-cases#parse-json-data-and-aggregate-records
05 Nov 2024 02:45 PM
I am not able to get JSON parser to do what I want either,
07 Nov 2024 02:21 PM
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
08 Nov 2024 07:35 AM
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
08 Nov 2024 06:15 PM
I tried several solutions along those lines but all of them return null. I must be missing something ?
08 Nov 2024 05:20 PM
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?
12 Nov 2024 10:07 AM - edited 12 Nov 2024 10:12 AM
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.