topic Re: DQL parse of log not getting complete data. in DQL

DQL parse of log not getting complete data.

rseibert1 — Tue, 05 Nov 2024 12:58:19 GMT

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 '\"},'"

Re: DQL parse of log not getting complete data.

PacoPorro — Tue, 05 Nov 2024 13:34:49 GMT

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

Re: DQL parse of log not getting complete data.

rseibert1 — Tue, 05 Nov 2024 14:45:53 GMT

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

Re: DQL parse of log not getting complete data.

rseibert1 — Thu, 07 Nov 2024 14:21:23 GMT

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

Re: DQL parse of log not getting complete data.

PacoPorro — Fri, 08 Nov 2024 07:35:46 GMT

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

Re: DQL parse of log not getting complete data.

rseibert1 — Fri, 08 Nov 2024 17:20:41 GMT

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?

Re: DQL parse of log not getting complete data.

rseibert1 — Fri, 08 Nov 2024 18:15:30 GMT

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

Re: DQL parse of log not getting complete data.

Tiit_Hallas — Tue, 12 Nov 2024 10:12:13 GMT

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-820a-145d89c7d4de