Splunk Search
×

Are you a member of the Splunk Community?

Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | 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
70 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!

Automatic Discovery Part 1: What is Automatic Discovery in Splunk Observability Cloud ...

If you’ve ever deployed a new database cluster, spun up a caching layer, or added a load balancer, you know it ...

Real-Time Fraud Detection: How Splunk Dashboards Protect Financial Institutions

Financial fraud isn't slowing down. If anything, it's getting more sophisticated. Account takeovers, credit ...

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...