Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Why do the stats table columns don't match?

dbcase
Motivator
‎05-25-2018 08:42 AM

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
Preview file
1 KB
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

somesoni2
Revered Legend
‎05-25-2018 09:37 AM

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
Reply
Solved! Jump to solution
Solution

somesoni2
Revered Legend
‎05-25-2018 09:37 AM

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
Reply
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...