replacing large numeric values with 0

[sanjeev]

Hi,

I have a dataset with column name as  WiFi_txop0  and values as 48,54,76,78,87,77,254311,65,99,65,..........

I want to replace the value of 254311 as 0 so that i could get a good average. I am using following query.

index=mmm

| stats avg(aWiFi_txop0) as WiFi_txop0

| eval WiFi_txop0_new = if(WiFi_txop0 > 100, 0, WiFi_txop0)
| eval usage_percent = round(WiFi_txop0_new,0)
| fields + usage_percent

But i am not getting result as 0.

Please help.

Thanks 

[renjith_nair]

@sanjeev,

Is that a multi value field?

If not try converting to a number using tonumber and compare

Below search is working fine

|makeresults|eval value="48,54,76,78,87,77,254311,65,99,65"|makemv value delim=","|mvexpand value
|eval newValue=if(value>100,0,value)

[sanjeev]

Yes it is muti value column  with more than million values.

sample of few values

xTemp_wl0
48
50
43
60
60
54
61
60
1161181233
43
60
49

Thanks 

[to4kawa]

| makeresults | eval _raw="xTemp_wl0
48
50
43
60
60
54
61
60
1161181233
43
60
49"
| multikv forceheader=1
| stats list(xTemp_wl0) as xTemp_wl0
| table xTemp_wl0

| eventstats avg(eval(mvmap(xTemp_wl0,if(xTemp_wl0>100,0,xTemp_wl0)))) as average

xTemp_wl0	average
48 50 43 60 60 54 61 60 1161181233 43 60 49	49

[sanjeev]

index=metrics HCTELEM AND (deviceid=B2* OR deviceid =B3*)
| fields + mac uptime deviceid payload version
| eval payload = replace(payload, "\"\"", "\"")
| spath input=payload output=Temp_wl0 path=Temp{0}
| spath input=payload output=Temp_wl1 path=Temp{1}
| spath input=payload output=Mem0 path=Mem{0}
| spath input=payload output=Mem1 path=Mem{1}
| spath input=payload output=CPU0 path=CPU{0}
| spath input=payload output=CPU1 path=CPU{1}
| spath input=payload output=CPU2 path=CPU{2}
| spath input=payload output=WiFi_txop0 path=WiFi{}.txop{0}
| spath input=payload output=WiFi_txop1 path=WiFi{}.txop{1}
| spath input=payload output=DSL_Bearer0_Up path=DSL{}.Bearer{}.0{}.UpDn{0}
| spath input=payload output=DSL_Bearer0_Dn path=DSL{}.Bearer{}.0{}.UpDn{1}
| spath input=payload output=DSL_Bearer0_RsUnCorr0 path=DSL{}.Bearer{}.0{}.RsUnCorr{0}
| spath input=payload output=DSL_Bearer0_RsUnCorr1 path=DSL{}.Bearer{}.0{}.RsUnCorr{1}
| spath input=payload output=DSL_MaxUp path=DSL{}.MaxUpDn{0}
| spath input=payload output=DSL_MaxDn path=DSL{}.MaxUpDn{1}
| spath input=payload output=DSL_Retrain path=DSL{}.Retrain
| spath input=payload output=DSL_CRC0 path=DSL{}.CRC{0}
| spath input=payload output=DSL_CRC1 path=DSL{}.CRC{1}
| spath input=payload output=DSL_ES0 path=DSL{}.ES{0}
| spath input=payload output=DSL_ES1 path=DSL{}.ES{1}
| spath input=payload output=DSL_SES0 path=DSL{}.SES{0}
| spath input=payload output=DSL_SES1 path=DSL{}.SES{1}
| spath input=payload output=Eth0_LinkUp path=Eth{}.0{}.LinkUp
| spath input=payload output=Eth0_Type path=Eth{}.0{}.Type
| spath input=payload output=Eth1_LinkUp path=Eth{}.1{}.LinkUp
| spath input=payload output=Eth1_Type path=Eth{}.1{}.Type
| stats max(_time) as max_time min(_time) as min_time max(deviceid) as deviceid latest(version) as version count as number_of_metrics latest(_time) as _time latest(uptime) as uptime
max(CPU0) as xCPU0 max(CPU1) as xCPU1 max(CPU2) as xCPU2 max(Mem0) as xMem0 max(Mem1) as xMem1 max(WiFi_txop0) as xWiFi_txop0 max(WiFi_txop1) as xWiFi_txop1 max(DSL_MaxUp) as xDSL_MaxUp max(DSL_MaxDn) as xDSL_MaxDn max(Temp_wl0) as xTemp_wl0 max(Temp_wl1) as xTemp_wl1
min(CPU0) as mCPU0 min(CPU1) as mCPU1 min(CPU2) as mCPU2 min(Mem0) as mMem0 min(Mem1) as mMem1 min(WiFi_txop0) as mWiFi_txop0 min(WiFi_txop1) as mWiFi_txop1 min(DSL_MaxUp) as mDSL_MaxUp min(DSL_MaxDn) as mDSL_MaxDn min(Temp_wl0) as mTemp_wl0 min(Temp_wl1) as mTemp_wl1
avg(CPU0) as aCPU0 avg(CPU1) as aCPU1 avg(CPU2) as aCPU2 avg(Mem0) as aMem0 avg(Mem1) as aMem1 avg(WiFi_txop0) as aWiFi_txop0 avg(WiFi_txop1) as aWiFi_txop1 avg(DSL_MaxUp) as aDSL_MaxUp avg(DSL_MaxDn) as aDSL_MaxDn avg(Temp_wl0) as aTemp_wl0 avg(Temp_wl1) as aTemp_wl1
max(Eth*) as Eth* last(DSL_Retrain) as DSL_Retrain max(DSL_Bearer0_RsUnCorr0) as xDSL_Bearer0_RsUnCorr0 min(DSL_Bearer0_RsUnCorr0) as mDSL_Bearer0_RsUnCorr0 by mac

| makeresults | eval _raw="xTemp_wl0" | multikv forceheader=1
| stats list(xTemp_wl0) as xTemp_wl0
| table xTemp_wl0
| eventstats avg(eval(mvmap(xTemp_wl0,if(xTemp_wl0>100,0,xTemp_wl0)))) as average

This is the full Query. The data is in Json format so i have parsed it. but unable to get the average. 

This is also giving error as - Error in 'eventstats' command: The eval expression for dynamic field 'eval(mvmap(xTemp_wl0,if(xTemp_wl0>100,0,xTemp_wl0)))' is invalid. Error='The 'mvmap' function is unsupported or undefined.'.

@to4kawa 

Thanks  

[to4kawa · Accepted solution]

• mvmap can work over splunk ver 8.
• I see your query. but I'm not sure why  xTemp_wl0 is multi value.
• try | rex field=xTemp_wl0 mode=sed "s/\d{3,}/0/g" and | stats avg(xTemp_wl0)

[sanjeev]

Thanks a lot @to4kawa 

This is working. 

Thanks once again.

[renjith_nair]

@sanjeev,

If it's a multi value column, you can use mvmap

|eval newValue=mvmap(xTemp_wl0,if(xTemp_wl0%100!=xTemp_wl0,0,xTemp_wl0))

Or you can mvexpand and do the comparison operation but that would be bit more resource expensive for a million records

[sanjeev]

Hi @renjith_nair 

This is giving error as Error='The 'mvmap' function is unsupported or undefined.'.