topic Re: replacing large numeric values with 0 in Reporting

replacing large numeric values with 0

sanjeev — Thu, 02 Jul 2020 07:45:52 GMT

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

Re: replacing large numeric values with 0

renjith_nair — Thu, 02 Jul 2020 07:58:13 GMT

@sanjeev,

Is that a multi value field?

If not try converting to a number using tonumber and compare

Below search is working fine

Re: replacing large numeric values with 0

sanjeev — Thu, 02 Jul 2020 08:30:50 GMT

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

Re: replacing large numeric values with 0

renjith_nair — Thu, 02 Jul 2020 09:48:39 GMT

@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

Re: replacing large numeric values with 0

to4kawa — Thu, 02 Jul 2020 10:36:50 GMT

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

Re: replacing large numeric values with 0

sanjeev — Thu, 02 Jul 2020 10:57:42 GMT

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

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

Re: replacing large numeric values with 0

sanjeev — Thu, 02 Jul 2020 10:58:40 GMT

Hi @renjith_nair

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

Re: replacing large numeric values with 0

to4kawa — Thu, 02 Jul 2020 11:16:09 GMT

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)

Re: replacing large numeric values with 0

sanjeev — Thu, 02 Jul 2020 11:47:14 GMT

Thanks a lot @to4kawa

This is working.

Thanks once again.