topic Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1] in DQL

Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1] as value

Jack_Stuart — Tue, 08 Aug 2023 17:02:52 GMT

Hi,

I'm having a bit of trouble extracting data from a json array. Would you be able to assist me?

for example

content={

"some_array" : [

"key1:value1",

"key2:value2"

]

}

Is there a way for me to process each item in content.some_array, split on ":" and then create fields with the first item of the split being the field name/key and the second item being the field value?

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

Fin_Ubels — Wed, 09 Aug 2023 01:08:24 GMT

Hey Jack,

Is this what you were looking for?

data json:"""{"some_array":["key1:value1", "key2:value2"]}""" | expand some_array | fields some_array = splitString(some_array, ":")

Or were you looking for each record replacing "some_array" with "key1", "key2" etc and then each value being "value1", "value2" etc?

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

Jack_Stuart — Wed, 09 Aug 2023 09:39:16 GMT

Hi Fin,

That's really helpful and it is on the way to what I'm trying to do. I've included an real example below followed by a view of what I'm trying to achieve. The expand has allowed me access to the array elements but assigning them as fields after the split is proving difficult, and I'm not sure how I'd reverse the expand back into a single row

Example:

data json:"""{"log.source": "thousandeyes-123456","level": "ALERT","dt.source_entity": "CUSTOM_DEVICE-B6DDABA85A5054AF","dt.source_entity_type": "CUSTOM_DEVICE","id": 859508901,"type": 2,"alert_state": 2,"alert_id": "some-alert-id","alert_rule_alertType.id": "WebTransaction","alert_severity": "INFO","alert_url": "app.thousandeyes.com/alerts/list/?__a=some-alert-id","alert_description": "*Alert ID*: some-alert-id\n*Test Name*: Dynatrace Heartbeat - dynatraceheartbeatevent.com\n*Target/s*: dynatraceheartbeatevent.com\n*Alert Rule*: Dynatrace-HEARTBEAT - Error is present\n*Details*: 2\n*Link*: app.thousandeyes.com/alerts/list/?__a=225896&alertId=some-alert-id","alert_test_name": "Dynatrace Heartbeat - dynatraceheartbeatevent.com","alert_test_labels": ["CMDB_APPID:AL08672","Location:Turkey","City:Istanbul"]}""" | filter matchesValue(dt.source_entity, "CUSTOM_DEVICE-B6DDABA85A5054AF") AND matchesValue(log.source, "thousandeyes-123456") | expand alert_test_labels | fieldsAdd label=splitString(alert_test_labels, ":") // CMDB_APPID:AL012345 | fieldsAdd labelKey=arrayFirst(label) | fieldsAdd labelValue=arrayLast(label)

View of what I'm trying to achieve:

data json:"""{"log.source": "thousandeyes-123456","level": "ALERT","dt.source_entity": "CUSTOM_DEVICE-B6DDABA85A5054AF","dt.source_entity_type": "CUSTOM_DEVICE","id": 859508901,"type": 2,"alert_state": 2,"alert_id": "some-alert-id","alert_rule_alertType.id": "WebTransaction","alert_severity": "INFO","alert_url": "app.thousandeyes.com/alerts/list/?__a=some-alert-id","alert_description": "*Alert ID*: some-alert-id\n*Test Name*: Dynatrace Heartbeat - dynatraceheartbeatevent.com\n*Target/s*: dynatraceheartbeatevent.com\n*Alert Rule*: Dynatrace-HEARTBEAT - Error is present\n*Details*: 2\n*Link*: app.thousandeyes.com/alerts/list/?__a=225896&alertId=some-alert-id","alert_test_name": "Dynatrace Heartbeat - dynatraceheartbeatevent.com","alert_test_labels": ["CMDB_APPID:AL08672","Location:Turkey","City:Istanbul"]}""" | filter matchesValue(dt.source_entity, "CUSTOM_DEVICE-B6DDABA85A5054AF") AND matchesValue(log.source, "thousandeyes-123456") | fieldsAdd CMDB_APPID="AL08672" | fieldsAdd Location="Turkey" | fieldsAdd City="Istanbul"

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

Fin_Ubels — Wed, 09 Aug 2023 22:06:00 GMT

Hey Jack,

Unfortunately I haven't been able to figure it out, I'm not sure how we can do that unless the number and order of "alert_test_labels" are fixed in which case we could do:

| fieldsAdd CMDB_APPID=arrayLast(splitString(alert_test_labels[0], ":")) | fieldsAdd Location=arrayLast(splitString(alert_test_labels[1], ":")) | fieldsAdd City=arrayLast(splitString(alert_test_labels[2], ":")) | fieldsRemove alert_test_labels

Thats a very static solution but I'm not sure how else we could do this sorry.

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

sinisa_zubic — Thu, 10 Aug 2023 08:14:45 GMT

Hi @Jack_Stuart

I would suggest that you convert the array to string and use DPL to parse the fields.

You can do this in a more declarative way:

data json:"""{"log.source": "thousandeyes-123456","level": "ALERT","dt.source_entity": "CUSTOM_DEVICE-B6DDABA85A5054AF","dt.source_entity_type": "CUSTOM_DEVICE","id": 859508901,"type": 2,"alert_state": 2,"alert_id": "some-alert-id","alert_rule_alertType.id": "WebTransaction","alert_severity": "INFO","alert_url": "app.thousandeyes.com/alerts/list/?__a=some-alert-id","alert_description": "*Alert ID*: some-alert-id\n*Test Name*: Dynatrace Heartbeat - dynatraceheartbeatevent.com\n*Target/s*: dynatraceheartbeatevent.com\n*Alert Rule*: Dynatrace-HEARTBEAT - Error is present\n*Details*: 2\n*Link*: app.thousandeyes.com/alerts/list/?__a=225896&alertId=some-alert-id","alert_test_name": "Dynatrace Heartbeat - dynatraceheartbeatevent.com","alert_test_labels": ["CMDB_APPID:AL08672","Location:Turkey","City:Istanbul"]}""" | filter matchesValue(dt.source_entity, "CUSTOM_DEVICE-B6DDABA85A5054AF") AND matchesValue(log.source, "thousandeyes-123456") | fieldsAdd helper = toString(alert_test_labels) | parse helper, """LD 'CMDB_APPID:' LD:CMDB_APPID '"' """ | parse helper, """ LD 'Location:' LD:Location '"' """ | parse helper, """ LD 'City:' LD:City '"' """ | parse helper, """'[' KVP{'"' LD:key ':' LD:value '"' (', '|']')}:kvp""" | fieldsRemove helper

or parsing with using KVP

data json:"""{"log.source": "thousandeyes-123456","level": "ALERT","dt.source_entity": "CUSTOM_DEVICE-B6DDABA85A5054AF","dt.source_entity_type": "CUSTOM_DEVICE","id": 859508901,"type": 2,"alert_state": 2,"alert_id": "some-alert-id","alert_rule_alertType.id": "WebTransaction","alert_severity": "INFO","alert_url": "app.thousandeyes.com/alerts/list/?__a=some-alert-id","alert_description": "*Alert ID*: some-alert-id\n*Test Name*: Dynatrace Heartbeat - dynatraceheartbeatevent.com\n*Target/s*: dynatraceheartbeatevent.com\n*Alert Rule*: Dynatrace-HEARTBEAT - Error is present\n*Details*: 2\n*Link*: app.thousandeyes.com/alerts/list/?__a=225896&alertId=some-alert-id","alert_test_name": "Dynatrace Heartbeat - dynatraceheartbeatevent.com","alert_test_labels": ["CMDB_APPID:AL08672","Location:Turkey","City:Istanbul"]}""" | filter matchesValue(dt.source_entity, "CUSTOM_DEVICE-B6DDABA85A5054AF") AND matchesValue(log.source, "thousandeyes-123456") | fieldsAdd helper = toString(alert_test_labels) | parse helper, """'[' KVP{'"' LD:key ':' LD:value '"' (', '|']')}:kvp""" | fieldsAdd City = kvp[City], Location = kvp[Location], CMDB_APPID = kvp[CMDB_APPID] | fieldsRemove helper

Best,
Sini

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

Jack_Stuart — Mon, 14 Aug 2023 12:03:47 GMT

Thanks for you help but to get around my problem I have decided to forward the data as events and trigger a workflow to process the JSON using a type script step. The problem is that the label values are dynamic so I can't add them as a kvp and access the label value without knowing the key up front

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

peter_zahrer — Mon, 30 Oct 2023 16:25:57 GMT

Maybe this presents a way forward using the fieldsFlatten command:

data json:"""{"log.source": "thousandeyes-123456","level": "ALERT","dt.source_entity": "CUSTOM_DEVICE-B6DDABA85A5054AF","dt.source_entity_type": "CUSTOM_DEVICE","id": 859508901,"type": 2,"alert_state": 2,"alert_id": "some-alert-id","alert_rule_alertType.id": "WebTransaction","alert_severity": "INFO","alert_url": "app.thousandeyes.com/alerts/list/?__a=some-alert-id","alert_description": "*Alert ID*: some-alert-id\n*Test Name*: Dynatrace Heartbeat - dynatraceheartbeatevent.com\n*Target/s*: dynatraceheartbeatevent.com\n*Alert Rule*: Dynatrace-HEARTBEAT - Error is present\n*Details*: 2\n*Link*: app.thousandeyes.com/alerts/list/?__a=225896&alertId=some-alert-id","alert_test_name": "Dynatrace Heartbeat - dynatraceheartbeatevent.com","alert_test_labels": ["CMDB_APPID:AL08672","Location:Turkey","City:Istanbul"]}""" | filter matchesValue(dt.source_entity, "CUSTOM_DEVICE-B6DDABA85A5054AF") AND matchesValue(log.source, "thousandeyes-123456") | fieldsAdd helper = toString(alert_test_labels) | parse helper, """'[' KVP{'"' LD:key ':' LD:value '"' (', '|']')}:kvp""" | fieldsFlatten kvp

Re: Log processing rule - for each item in json array split on ":" then create fields using split[0] as key and split[1]

mosharref_hossn — Mon, 11 Dec 2023 15:34:49 GMT

@peter_zahrer

I'm currently facing similar issue with dynamic key value pairs. The Log Processor Rule can parse the data using KVP parsing and store the data into an array. However, I want to extract those key:value into fields (key) so they can be used in DQL search or data explorer.

However, the "fieldsFlatten" command is not supported in the Log processor definition (i.e. DPL). It works in Notebook DLP Architect, but not in the rule definition.

Any other solution available?