Splunk Search

How to configure transforms.conf for different count values while not breaking current existing regexes that are working?

Contributor

Is there a way to create a transforms for separate values while not breaking current regex instances that are working? Currently, we are capturing data, however, one of the tools that creates the reports formats them both with numeric values AND abbreviated values (ie: 2M instead of 2,000,000). I am looking toward creating a transforms in my lookups to assign each 'M' (examples below) value to a numeric value, but not sure this will break my current regex for working numeric metrics.

Existing regex for Count:

^(?:[^,\n]*,){5}(?P<Count>.+)

Again, numeric values are being passed without issue.

example of possible transforms
Count, Count
1M,1,000,000
2M,2,000,000
3M,3,000,000

Current Example of metrics coming into Splunk.
Count

"514,882"

"258,509"

"514,970"

"541,708"

"96,494"

"110,341"

1.7M

"209,075"

1.5M

"222,845"

1.8M

2M

1.1M

1M

"245,802"

2.4M

1.3M

2.8M

"187,360"

2M

1.5M

1.6M

1.6M

4.8M

1.5M

5M

3M

1.5M

1.7M

"84,007"

0 Karma
1 Solution

Path Finder

Are you wanting to change all of the values for "Count" that have an abbreviated number (e.g. 2M) into the long-form (e.g. 2,000,000)? If so, you could do this with an eval after the field extraction is performed.

EVAL-Count = case(match(Count, "M$"), tonumber(rtrim(Count, "M"))*1000000) | fieldformat Count=tostring(Count, "commas")

Add in additional params to case to handle "B"illions, etc.

View solution in original post

Path Finder

Are you wanting to change all of the values for "Count" that have an abbreviated number (e.g. 2M) into the long-form (e.g. 2,000,000)? If so, you could do this with an eval after the field extraction is performed.

EVAL-Count = case(match(Count, "M$"), tonumber(rtrim(Count, "M"))*1000000) | fieldformat Count=tostring(Count, "commas")

Add in additional params to case to handle "B"illions, etc.

View solution in original post

Contributor

Thats for the response @vasildavid, I did attempt this, however it breaks all "Count" values that are not in the million range. Pasted an example below after I added the eval to the extraction. As you can see, any value that is not 1,000,000 or above is no present. The eval works like a charm with values 1,000,000 and greater. This is the exact issue I am having. Presenting both at the same time.

Count

1,700,000

1,500,000

1,800,000
2,000,000
1,100,000
1,000,000

2,400,000
1,300,000
2,800,000

2,000,000

0 Karma

Revered Legend

I think the EVAL here is missing alternate condition (what happens when there is no M). Try something like this

 EVAL-Count = case(match(Count, "M$"), tostring(tonumber(rtrim(Count, "M"))*1000000,"commas"), 1=1 , Count ) 
0 Karma

Contributor

This worked perfectly, thank you both @somesoni2 and @vasildavid!! Now I just need to find a way to remove those quotes in some of the output. Thanks again!

Count
"514,882"
"258,509"
"514,970"
"541,708"
"96,494"
"110,341"
1,700,000
"209,075"
1,500,000
"222,845"
1,800,000
2,000,000
1,100,000
1,000,000
"245,802"
2,400,000
1,300,000
2,800,000
"187,360"

0 Karma

Revered Legend

Try this

EVAL-Count = case(match(Count, "M$"), tostring(tonumber(rtrim(Count, "M"))*1000000,"commas"), 1=1 , replace(Count,"\"","") )
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!