topic Re: Summarize terms in a record in DQL

Summarize terms in a record

wellpplava — Wed, 27 Mar 2024 14:40:39 GMT

Hello!

I have log lines when a customer makes a purchase. This log contains the ID of the purchased products. I would like to be able to quantify the best-selling products of the day.

Example:

message: TUP15451, TUP41234, TUP52351152.

It's possible?

Re: Summarize terms in a record

dannemca — Wed, 27 Mar 2024 16:17:34 GMT

Considering the productID is a field, you can do "| summarize count(), by: {productID} " at the end of your query.

https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/commands/aggregation-commands#summarize

Re: Summarize terms in a record

wellpplava — Wed, 27 Mar 2024 17:23:35 GMT

but I have multiple products, something like: "PRODUCTS: TPU 123, TPU 1942, TPU9724"

Re: Summarize terms in a record

dannemca — Wed, 27 Mar 2024 17:26:20 GMT

You mean, in the same line you have multiple values for the productID field?

Re: Summarize terms in a record

wellpplava — Wed, 27 Mar 2024 17:33:34 GMT

Yes!!

I'm almost there!What's left is to refine my parseAll because it's not getting the last value

@dannemca wrote:
You mean, in the same line you have multiple values for the productID field?

data record(timestamp = "12/24/2022", content = "Products: SKU1234, SKU1234, SKU12345"),
record(timestamp = "12/25/2022", content = "Products: SKU123, SKU1234, SKU12345")

| fieldsAdd ProductID = parseAll(content, "BLANK LD:pid PUNCT")
| expand ProductID
| summarize count(), alias: Occurrences, by:ProductID

Re: Summarize terms in a record

dannemca — Wed, 27 Mar 2024 17:47:54 GMT

Got it now, try to replace the LD by WORD in your parseAll:

data record(timestamp = "12/24/2022", content = "Products: SKU1234, SKU1234, SKU12345"), record(timestamp = "12/25/2022", content = "Products: SKU123, SKU1234, SKU12345") | fieldsAdd ProductID = parseAll(content, "BLANK WORD:pid") | expand ProductID | summarize count(), alias: Occurrences, by:ProductID

Regards.

Re: Summarize terms in a record

wellpplava — Thu, 28 Mar 2024 13:32:46 GMT

@dannemca

We received the official registration format. Perhaps it would be possible to adapt parse to capture what is just between {[[ and ]]}?

I tried with DATA "{[[" but I was unsuccessful

PARITY_CATEGORY and fields: {[[TKU12345, TKU123456, TKU123451, TKU59156]]}

Re: Summarize terms in a record

krzysztof_hoja — Thu, 28 Mar 2024 18:22:32 GMT

If you have comma separated array of words it quite easy to do using just DPL or DPL + splitString:

data record(content="blabla blabla Products: {[[TKU12345, TKU123456, TKU123451, TKU59156]]} bla bla") | parse content, "DATA '{[[' LD:products ']]}' DATA" | fieldsAdd product=trim(splitString(products,",")[])

data record(content="blabla blabla Products: {[[TKU12345, TKU123456, TKU123451, TKU59156]]} bla bla") | parse content, "DATA '{[[' ARRAY{WORD*:i ', '*}{1,100}:product ']]}' DATA"

Kris

Re: Summarize terms in a record

wellpplava — Thu, 28 Mar 2024 20:05:11 GMT

| parse content, "DATA '{[[' ARRAY{WORD*:i ', '*}{1,100}:product ']]}' DATA"

this worked great! Thank you very much!

Could you explain to me how it works?

Re: Summarize terms in a record

krzysztof_hoja — Fri, 29 Mar 2024 06:31:23 GMT

Doc page for this: https://docs.dynatrace.com/docs/platform/grail/dynatrace-pattern-language/log-processing-array