Splunk Search

How to edit my search to prevent duplicate values when using replace command

Path Finder

Hello fellow Splunkers

Not sure the best way to approach the following problem. I use replace to update values within a fields. I'm running into an issue when I replace the 2 different strings values with the same string which ends up resulting in duplicate values for each of the renamed strings

| searchHere
| stats count(product_id) as total_product_id by assest_tag, product_version
| replace "storeX" with "local" in asset_tag 
| replace "storeY_NZ" with "local" in asset_tag 
| where asset_tag == local
| eventstats sum(total_product_id) as total_in_inventory
| eval perc = round( total_product_id * 100 / total_in_inventory, 1 ) 
| table product_version, total_in_inventory, perc

Part of the issue that I'm having is that I'm getting duplicate production versions. My guess is because of the replace. How can I resolve this?

Tags (2)
0 Karma
1 Solution

Motivator

Howdy Splunker,

Could you provide some sample data and explain where you see the duplicates?

Based on the information given, my guess is one or more of the following:
- Move the two replace lines to above the first stats
- Add by asset_tag, product_version to the end of your eventstats
- Change count(product_id) to just count

Here's a run-anywhere search that I was using to try to understand your data. The output of it looks accurate to me.

| makeresults count=50
| eval asset_tag       = random()%10 * 1000 + 1000
| eval product_version = random()%10 * 10   + 10
| replace "2000" with "1000" in asset_tag
| replace "3000" with "1000" in asset_tag
| stats count as total_product_id by asset_tag, product_version
| eventstats sum(total_product_id) as total_in_inventory 
| eval perc = round( 100 * (total_product_id / total_in_inventory), 1 )
| sort asset_tag product_version
| addcoltotals labelfield=asset_tag total_product_id perc
Cheers,
Jacob

View solution in original post

Motivator

Howdy Splunker,

Could you provide some sample data and explain where you see the duplicates?

Based on the information given, my guess is one or more of the following:
- Move the two replace lines to above the first stats
- Add by asset_tag, product_version to the end of your eventstats
- Change count(product_id) to just count

Here's a run-anywhere search that I was using to try to understand your data. The output of it looks accurate to me.

| makeresults count=50
| eval asset_tag       = random()%10 * 1000 + 1000
| eval product_version = random()%10 * 10   + 10
| replace "2000" with "1000" in asset_tag
| replace "3000" with "1000" in asset_tag
| stats count as total_product_id by asset_tag, product_version
| eventstats sum(total_product_id) as total_in_inventory 
| eval perc = round( 100 * (total_product_id / total_in_inventory), 1 )
| sort asset_tag product_version
| addcoltotals labelfield=asset_tag total_product_id perc
Cheers,
Jacob

View solution in original post

Esteemed Legend

@jacobevans nailed it.