Reporting

replacing large numeric values with 0

sanjeev
Explorer

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 

Labels (3)
0 Karma
1 Solution

to4kawa
Ultra Champion
  • 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)

View solution in original post

0 Karma

renjith_nair
Legend

@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)

 

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

sanjeev
Explorer

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 

0 Karma

to4kawa
Ultra Champion

 

 

| 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

 

 

0 Karma

sanjeev
Explorer

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  

0 Karma

to4kawa
Ultra Champion
  • 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)
0 Karma

sanjeev
Explorer

Thanks a lot @to4kawa 

This is working. 

 

Thanks once again.

 

0 Karma

renjith_nair
Legend

@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

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

sanjeev
Explorer

Hi @renjith_nair 

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

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...