Summarize terms in a record

[wellpplava]

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?

[dannemca]

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-command...

[wellpplava]

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

[dannemca]

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

[wellpplava]

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

[dannemca · Accepted solution]

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.

[wellpplava]

@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]]}

[krzysztof_hoja · Accepted solution]

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,",")[])

or

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

Kris

[wellpplava]

| 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?

[krzysztof_hoja]

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