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?
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")
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")
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
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.