Hi,
I have this query that filters the results to a single Premise (8773). It then extracts out the premiseid, macid and rssiid. Each mac has a corresponding rssi value. So far so good but the last mac in the below table does not reflect a rssi value even though one is there. Makes my head hurt to figure out why. Thoughts?
index="camera_status" sourcetype=access_combined_camerastatus 8773|rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\:]+)"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|where rssiid!="dB"|stats values(premiseid) as PREMISE values(macid) as MAC, values(rssiid) as RSSI
The actual data looks like the below
Premise= 8773
Name= Front Room Camera
IP= 172.16.12.106
ID= 6
Mac= 94:4A:0C:11:92:C6
FW Ver= 3.0.01.32
Manufacturer= iControl
Model= RC8026
Video Size= LARGE
Verified= true
RSSI= -23 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.106:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.106:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= true
MotionSensitivy= 1 (LOW)
Local Video Aspect Ratio= 4:3
Local Video Resolution= 640:480
Remote Video Aspect Ratio= 4:3
Remote Video Resolution= 640:480
Name= Gate Camera
IP= 172.16.12.104
ID= 7
Mac= 78:94:B4:E8:2E:92
FW Ver= 3.0.02.51
Manufacturer= iControl
Model= iCamera2-C
Video Size= MEDIUM
Verified= true
RSSI= -56 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.104:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.104:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= false
Local Video Aspect Ratio= 16:9
Local Video Resolution= 1280:720
Remote Video Aspect Ratio= 16:9
Remote Video Resolution= 1280:720
Name= Back Of House
IP= 172.16.12.143
ID= 9
Mac= B4:A5:EF:F8:53:27
FW Ver= 3.0.02.51
Manufacturer= iControl
Model= iCamera2-C
Video Size= MEDIUM
Verified= true
RSSI= -45 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.143:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.143:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= false
Local Video Aspect Ratio= 16:9
Local Video Resolution= 1280:720
Remote Video Aspect Ratio= 16:9
Remote Video Resolution= 1280:720
Name= Back Patio Camera
IP= 172.16.12.105
ID= 5
Mac= 94:4A:0C:19:B2:58
FW Ver= 3.0.02.51
Manufacturer= iControl
Model= iCamera2-C
Video Size= LARGE
Verified= true
RSSI= -46 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.105:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.105:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= false
Local Video Aspect Ratio= 16:9
Local Video Resolution= 1280:720
Remote Video Aspect Ratio= 16:9
Remote Video Resolution= 1280:720
Name= Driveway Camera
IP= 172.16.12.103
ID= 4
Mac= 94:4A:0C:19:B2:CB
FW Ver= 3.0.02.51
Manufacturer= iControl
Model= iCamera2-C
Video Size= LARGE
Verified= true
RSSI= -58 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.103:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.103:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= false
Local Video Aspect Ratio= 16:9
Local Video Resolution= 1280:720
Remote Video Aspect Ratio= 16:9
Remote Video Resolution= 1280:720
Name= Front Porch Camera
IP= 172.16.12.102
ID= 8
Mac= E0:60:66:11:9C:31
FW Ver= 3.0.02.51
Manufacturer= iControl
Model= iCamera2-C
Video Size= MEDIUM
Verified= true
RSSI= -45 dB
Supported Video Formats= [MJPEG, FLV, RTSP]
Supported Video Codecs= [H264, MPEG4]
FLV URL= https://172.16.12.102:80/openhome/streaming/channels/0/flv
MJPEG URL= https://172.16.12.102:80/openhome/streaming/channels/2/mjpeg
API Version= 3.3
MotionTurnedOn= false
Local Video Aspect Ratio= 16:9
Local Video Resolution= 1280:720
Remote Video Aspect Ratio= 16:9
Remote Video Resolution= 1280:720
The problem here is that you're using values
in stats command. The values
function dedups and sorts the corresponding values. Your data has two RSSI with -45
as value and they are getting removed. Since values
sorts the data, the mapping is/can be off. You should be using list
function instead of values
function in stats like this,
index="camera_status" sourcetype=access_combined_camerastatus 8773|rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\:]+)"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|where rssiid!="dB"|stats values(list) as PREMISE list(macid) as MAC, list(rssiid) as RSSI
OR (assuming there is only one PREMISE value per event
index="camera_status" sourcetype=access_combined_camerastatus 8773|rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\:]+)"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|where rssiid!="dB"|stats list(macid) as MAC, list(rssiid) as RSSI by PREMISE
The problem here is that you're using values
in stats command. The values
function dedups and sorts the corresponding values. Your data has two RSSI with -45
as value and they are getting removed. Since values
sorts the data, the mapping is/can be off. You should be using list
function instead of values
function in stats like this,
index="camera_status" sourcetype=access_combined_camerastatus 8773|rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\:]+)"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|where rssiid!="dB"|stats values(list) as PREMISE list(macid) as MAC, list(rssiid) as RSSI
OR (assuming there is only one PREMISE value per event
index="camera_status" sourcetype=access_combined_camerastatus 8773|rex max_match=0 "Premise=\s+(?<premiseid>\d+)"|rex max_match=0 "Mac=\s+(?<macid>[a-fA-F0-9\:]+)"|rex max_match=0 "RSSI=\s+(?<rssiid>[^\s]+)"|where rssiid!="dB"|stats list(macid) as MAC, list(rssiid) as RSSI by PREMISE