Splunk Search

extra data from particular filed and show in chart

dyapasrikanth
Explorer

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?

Labels (3)
0 Karma
1 Solution

scelikok
Champion

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")
If this reply helps you an upvote is appreciated.

View solution in original post

0 Karma

scelikok
Champion

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")
If this reply helps you an upvote is appreciated.

View solution in original post

0 Karma

dyapasrikanth
Explorer

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

 

  

 
0 Karma

dyapasrikanth
Explorer

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.

Tags (1)
0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!