Average on a value over time

[dbcase]

Hi,

I have this query (yes I know its ugly but it works 🙂 ) . What I need to do is present the current RSSI value (this works) and the average RSSI value over whatever time period is selected by hour (I have the time period working). I've tried a few things but sadly no luck. Any thoughts?

index="blah_camera_status" sourcetype=access_combined_blah_camerastatus   |rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Name=\s+(?<cname>.+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\.:-]{12,17})"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|rex max_match=0 "Model=\s+(?<cmodel>.+)"|dedup macid|eval temp=mvzip(mvzip(mvzip(macid,rssiid,"###"),cmodel,"###"),cname,"###")|mvexpand temp | rex field=temp "(?<macid>.+)###(?<rssiid>.+)###(?<cmodel>.+)###(?<cname>.+)"|eval rssiid=if(cmodel="iCamera2-C" AND rssiid!=0, rssiid+-20,rssiid)|eval rssiid=if(rssiid="dB",0,rssiid)|dedup macid premiseid|stats list(premiseid) as Premise list(cmodel) as "Camera Model"  list(cname) as "Camera Name" list(rssiid) as RSSI avg(rssiid) as "Average RSSI"  by macid|sort  RSSI|rename RSSI as "Current RSSI"|

[somesoni2]

Give this a try

index="Cox_camera_status" sourcetype=access_combined_cox_camerastatus   |rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Name=\s+(?<cname>.+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\.:-]{12,17})"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|rex max_match=0 "Model=\s+(?<cmodel>.+)"
|eval temp=mvzip(mvzip(mvzip(macid,rssiid,"###"),cmodel,"###"),cname,"###")|mvexpand temp | rex field=temp "(?<macid>.+)###(?<rssiid>.+)###(?<cmodel>.+)###(?<cname>.+)"
|eval rssiid=if(cmodel="iCamera2-C" AND rssiid!=0, rssiid+-20,rssiid)|eval rssiid=if(rssiid="dB",0,rssiid)
| eventstats avg(rssiid) as avgrssi by macid|dedup macid premiseid
| dedup macid premiseid
|stats list(premiseid) as Premise list(cmodel) as "Camera Model"  list(cname) as "Camera Name" list(rssiid) as RSSI list(avgrssi) as "Average RSSI"  by macid|sort  RSSI|rename RSSI as "Current RSSI"

[dbcase]

Niiiiiiiiccccccceeeee! Worked like a charm, thanks Somesoni2!!

[cpetterborg]

If this has worked for you, please accept @somesoni2's answer (I have converted is comment to an answer for that purpose) so that future readers can know that this has been answered.

Thanks!

[somesoni2]

When you're doing dedup macid, you're just selecting the latest record for a macid, so you'll lose all historical values of rssiid for that macid. What should your average by hour calculate?

[dbcase]

ahhhh, ok so I need to calc the average before that happens. The average is on the RSSI value, this is on an hourly basis (that is when the log is collected). The user will select a time period in the dashboard lets say 2 weeks... span=1hr over a 2 week duration, and if the RSSI value is 0 exclude it.

[dbcase]

Tried this but no luck. The avgrssi is the same as the current one in all cases. 😞

index="Cox_camera_status" sourcetype=access_combined_cox_camerastatus   |rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Name=\s+(?<cname>.+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\.:-]{12,17})"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|rex max_match=0 "Model=\s+(?<cmodel>.+)"|dedup macid|eval temp=mvzip(mvzip(mvzip(macid,rssiid,"###"),cmodel,"###"),cname,"###")|mvexpand temp | rex field=temp "(?<macid>.+)###(?<rssiid>.+)###(?<cmodel>.+)###(?<cname>.+)"|eval rssiid=if(cmodel="iCamera2-C" AND rssiid!=0, rssiid+-20,rssiid)|eval rssiid=if(rssiid="dB",0,rssiid)| eventstats avg(rssiid) as avgrssi by macid|dedup macid premiseid|stats list(premiseid) as Premise list(cmodel) as "Camera Model"  list(cname) as "Camera Name" list(rssiid) as RSSI list(avgrssi) as "Average RSSI"  by macid|sort  RSSI|rename RSSI as "Current RSSI"|