Splunk Search

Why do the stats table columns don't match?

dbcase
Motivator

alt textHi,

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

alt text

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
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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 -45as value and they are getting removed. Since values sorts the data, the mapping is/can be off. You should be using listfunction 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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 -45as value and they are getting removed. Since values sorts the data, the mapping is/can be off. You should be using listfunction 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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...