extra data from particular filed and show in chart

[dyapasrikanth]

I have logs like 

{"message": "Submitted amount category1: 213, category2: 543.56, category3: 4343.00", "specialCustomer": "true"}

I am trying to extract sum of amount by each category and customerType. My query is like this

| search message="Submitted amount *"
| rex field=message "(?<category>\w+): (?<amount>\d*\.?\d*)"
| eval userType=if(isnotnull(specialCustomer), "Special", "Normal")
| chart sum(amount) as Amount by userType, category
| addtotals
| eval category=case(
category="category1", "Category 1", 
category="category2", "Category 2",
category="category3", "Category 3")

However I am getting only one category data, what is wrong with my regex? do I need to use extract for this?

[scelikok · Accepted solution]

Hi @dyapasrikanth,

rex command stops on the first match as default. You should use max_match option;

| search message="Submitted amount *" 
| rex field=message max_match=0 "(?<category>\w+): (?<amount>\d*\.?\d*)" 
| eval userType=if(isnotnull(specialCustomer), "Special", "Normal") 
| chart sum(amount) as Amount by userType, category 
| addtotals 
| eval category=case(
    category="category1", "Category 1", 
    category="category2", "Category 2",
    category="category3", "Category 3")

[dyapasrikanth]

I got it finally with mvzip & mvexpand, not sure it is a best solution

| rex field=message max_match=0 "(?<category>\w+):\s(?<amount>\d*\.?\d*)"
| eval temp = mvzip(category,amount, "#")
| mvexpand temp
| rex field=temp "(?<category>.+)#(?<amount>.+)"
| eval category=case(
    category="category1", "Category 1", 
    category="category2", "Category 2",
    category="category3", "Category 3")
| chart sum(amount) as Amount by userType, category
| addtotals

[dyapasrikanth]

Thanks for your quick reply, yes that max_match=0 did the trick. But why my sum is going wrong ? 
For all categories it is giving first category amount only.

If I table it, they are coming as group instead in each row.