JSON data search stats count(field) by (field) result 5x count from single event

Seawheels51 — Wed, 14 Jun 2023 22:40:57 GMT

Greetings experts

Big picture: using Bash script and curl to download Rest API/JSON from an AWS instance. The beginning of each download is unstructured followed by the structured JSON.

Four different "logType", two logTypes (deviceConnectivityUpdate and deviceStateEvent) shown in a five event example below

_raw example

{"total":{"value":5,"relation":"eq"},"max_score":1,"hits":[{"_index":"index_44444444-4444-4444-4444-444444444444","_id":"zbIdu4gBwP_vIV4KexH0","_score":1,"_source":{"version":1,"logType":"deviceConnectivityUpdate","deviceSerialNumber":"4931390007","userName":"gary.whitlocks22","cloudTimestampUTC":"2023-06-14T18:14:11Z","isDeviceOffline":false}},{"_index":"index_44444444-4444-4444-4444-444444444444","_id":"z7Ieu4gBwP_vIV4KARGG","_score":1,"_source":{"version":1,"logType":"deviceConnectivityUpdate","deviceSerialNumber":"4931390007","userName":"gary.whitlocks22","cloudTimestampUTC":"2023-06-14T18:14:45Z","isDeviceOffline":true}},{"_index":"index_44444444-4444-4444-4444-444444444444","_id":"0LIeu4gBwP_vIV4KHxHn","_score":1,"_source":{"version":1,"logType":"deviceStateEvent","deviceSerialNumber":"4931490086","userName":"NSSS","cloudTimestampUTC":"2023-06-14T18:14:53Z","deviceTimestampUTC":"2023-06-14T18:14:55Z","batteryPercent":49,"isCheckIn":false,"isAntiSurveillanceViolation":false,"isLowBatteryViolation":false,"isCellularViolation":false,"isDseDelayed":false,"bleMacAddress":"7d:8e:1a:be:92:5a","cellIpv4Address":"0.0.0.0","cellIpv6Address":"::"}},{"_index":"index_44444444-4444-4444-4444-444444444444","_id":"zrIdu4gBwP_vIV4KsxFQ","_score":1,"_source":{"version":1,"logType":"deviceConnectivityUpdate","deviceSerialNumber":"4931390006","userName":"PennyAndroid","cloudTimestampUTC":"2023-06-14T18:14:25Z","isDeviceOffline":true}},{"_index":"index_44444444-4444-4444-4444-444444444444","_id":"0bIeu4gBwP_vIV4KhBGr","_score":1,"_source":{"version":1,"logType":"deviceConnectivityUpdate","deviceSerialNumber":"4931390006","userName":"PennyAndroid","cloudTimestampUTC":"2023-06-14T18:15:19Z","isDeviceOffline":false}}]}

JSON

{
"total": {
"value": 5,
"relation": "eq"
},
"max_score": 1,
"hits": [
{
"_index": "index_44444444-4444-4444-4444-444444444444",
"_id": "zbIdu4gBwP_vIV4KexH0",
"_score": 1,
"_source": {
"version": 1,
"logType": "deviceConnectivityUpdate",
"deviceSerialNumber": "4931390007",
"userName": "gary.whitlocks22",
"cloudTimestampUTC": "2023-06-14T18:14:11Z",
"isDeviceOffline": false
}
},
{
"_index": "index_44444444-4444-4444-4444-444444444444",
"_id": "z7Ieu4gBwP_vIV4KARGG",
"_score": 1,
"_source": {
"version": 1,
"logType": "deviceConnectivityUpdate",
"deviceSerialNumber": "4931390007",
"userName": "gary.whitlocks22",
"cloudTimestampUTC": "2023-06-14T18:14:45Z",
"isDeviceOffline": true
}
},
{
"_index": "index_44444444-4444-4444-4444-444444444444",
"_id": "0LIeu4gBwP_vIV4KHxHn",
"_score": 1,
"_source": {
"version": 1,
"logType": "deviceStateEvent",
"deviceSerialNumber": "4931490086",
"userName": "NSSS",
"cloudTimestampUTC": "2023-06-14T18:14:53Z",
"deviceTimestampUTC": "2023-06-14T18:14:55Z",
"batteryPercent": 49,
"isCheckIn": false,
"isAntiSurveillanceViolation": false,
"isLowBatteryViolation": false,
"isCellularViolation": false,
"isDseDelayed": false,
"bleMacAddress": "7d:8e:1a:be:92:5a",
"cellIpv4Address": "0.0.0.0",
"cellIpv6Address": "::"
}
},
{
"_index": "index_44444444-4444-4444-4444-444444444444",
"_id": "zrIdu4gBwP_vIV4KsxFQ",
"_score": 1,
"_source": {
"version": 1,
"logType": "deviceConnectivityUpdate",
"deviceSerialNumber": "4931390006",
"userName": "PennyAndroid",
"cloudTimestampUTC": "2023-06-14T18:14:25Z",
"isDeviceOffline": true
}
},
{
"_index": "index_44444444-4444-4444-4444-444444444444",
"_id": "0bIeu4gBwP_vIV4KhBGr",
"_score": 1,
"_source": {
"version": 1,
"logType": "deviceConnectivityUpdate",
"deviceSerialNumber": "4931390006",
"userName": "PennyAndroid",
"cloudTimestampUTC": "2023-06-14T18:15:19Z",
"isDeviceOffline": false
}
}
]
}

CSV

version

logType

deviceSerialNumber

userName

cloudTimestampUTC

isDeviceOffline

deviceTimestampUTC

batteryPercent

isCheckIn

isAntiSurveillanceViolation

isLowBatteryViolation

isCellularViolation

isDseDelayed

bleMacAddress

cellIpv4Address

cellIpv6Address

deviceConnectivityUpdate

4931390007

gary.whitlocks22

6/14/2023 18:14

FALSE

deviceConnectivityUpdate

4931390007

gary.whitlocks22

6/14/2023 18:14

TRUE

deviceStateEvent

4931490086

NSSS

6/14/2023 18:14

FALSE

7d:8e:1a:be:92:5a

0.0.0.0

deviceConnectivityUpdate

4931390006

PennyAndroid

6/14/2023 18:14

TRUE

deviceConnectivityUpdate

4931390006

PennyAndroid

6/14/2023 18:15

FALSE

Indexed using sourcetype _JSON

Looking at hits{}._source.logType Splunk reports 4 deviceConnectivityUpdate events and 1 deviceStateEvent which agrees with the data. However when I run stats count by("hits{}._source.logType") by "hits{}._source.userName I get 5x count of events (see attachment)

Help please, what am I missing or doing wrong?

topic JSON data search stats count(field) by (field) result 5x count from single event in Getting Data In

JSON data search stats count(field) by (field) result 5x count from single event