topic Re: How to get boolean fields from a stats max count (event number) in Splunk Search

How to get boolean fields from a stats max count (event number)

Seawheels51 — Mon, 06 Jan 2025 18:52:49 GMT

How do I return field values from a specific max(eventnumber)?

This was helpful but did not solve my issue Solved: How to get stats max count of a field by another f... - Splunk Community

We are ingesting logs from test devices. Each log has an event number, which I can search on to find the most recent event. When the devices disconnect from our cloud instance, they cache events which are transmitted at a lower priority (newest to oldest) than real time events. For example: event #100 connected to cloud, event 101-103 disconnected from cloud and cached, events, #104 re-connected to cloud (latest status) received, then event 103 is transmitted, then 102, so using latest/earliest or first/last does not return the most recent status

The logs consist of an event number and boolean (true/false) fields.

Searching for max(event number) and values(boolean field value) results in both true/false for any time picker period that has multiple events, for example:

| stats max(triggeredEventNumber) values(isCheckIn) values(isAntiSurveillanceViolation) BY userName

userName max(triggeredEventNumber) values(isCheckIn) latest(isAntiSurveillanceViolation)
NS2_GS22_MW 92841 false true FALSE

In the example the actual value of isCheckIn was true.

Here is a complete example event:

{
"version": 1,
"logType": "deviceStateEvent",
"deviceSerialNumber": "4234220083",
"userName": "NS2_GS22_MW",
"cloudTimestampUTC": "2025-01-06T18:17:00Z",
"deviceTimestampUTC": "2025-01-06T18:16:46Z",
"triggeredEventNumber": 92841,
"batteryPercent": 87,
"isCheckIn": true,
"isAntiSurveillanceViolation": false,
"isLowBatteryViolation": false,
"isCellularViolation": false,
"isDseDelayed": false,
"isPhonePresent": true,
"isCameraExposed": false,
"isShutterOpen": false,
"isMicExposed": false,
"isCharging": false,
"isPowerOff": false,
"isHibernation": false,
"isPhoneInfoStale": false,
"bleMacAddress": "5c:2e:c6:bc:e4:cf",
"cellIpv4Address": "0.0.0.0",
"cellIpv6Address": "::"
}

Re: How to get boolean fields from a stats max count (event number)

richgalloway — Mon, 06 Jan 2025 20:46:35 GMT

That is the way the values() function works. It will list, in lexicographical order, all of the unique values for the given field. What would you rather see?

Re: How to get boolean fields from a stats max count (event number)

Seawheels51 — Mon, 06 Jan 2025 21:41:44 GMT

Thank you for the reply @richgalloway
The results I want to see are the fields for that triggered event number only.

Re: How to get boolean fields from a stats max count (event number)

Seawheels51 — Mon, 06 Jan 2025 21:49:48 GMT

@richgalloway the problem is the cached events are delivered more recently (lower priority transmission) than the actual current state event which is why the search for max(triggeredEventNumber) instead of first/last or earliest/latest since I cannot forecast how many cached events may be delivered after current state and before the next current state event is created (as a result of a state change typically battery percent change)

Re: How to get boolean fields from a stats max count (event number)

ITWhisperer — Mon, 06 Jan 2025 23:34:46 GMT

| eventstats max(triggeredEventNumber) as userLastEvent BY userName | where triggeredEventNumber = userLastEvent

Re: How to get boolean fields from a stats max count (event number)

Seawheels51 — Tue, 07 Jan 2025 17:10:32 GMT

Thank you @ITWhisperer

In my laymans terms use eventstats search to find max(triggeredEventNumber) BY userName. Next use where to select only that max(triggeredEventNumber) for results.

Then I used stats values(field) to extract the values for the fields I am interested in.