How to find the latest events for different values of a field but showing the associated fields in those latest events

[yshen]

Following up with my previous questions context (https://community.splunk.com/t5/Splunk-Search/How-to-make-the-time-readable-in-stats-latest-time/m-p...)

Thanks for solving my previous problem!

I have events with the the following format:

Agent_Hostname, _time, alertStatus_1, temperature

I want to find the latest events for each Agent_Hostname value, but report all fields of those latest event. 

The following query provides close semantics, but it does not provide the field of temperature:

index=snmptrapd 
| stats latest(_time) as latestTime by Agent_Hostname alertStatus_1 
| eval latestTime = strftime(latestTime, "%F %T")

What I want actually is the latest status of the Agent_Hostname  with the full events' value. It is more like the following with some mechanism to show all fields:

index=snmptrapd 
| stats latest(_time) as latestTime by Agent_Hostname 
| eval latestTime = strftime(latestTime, "%F %T")

In terms of outcome, I want to have an additional field of temperature from the following:

Agent_Hostname	Agent_Hostname	latestTime	Temperature
l20-tempmon	normal	2020-08-20 03:30:09	?
l18-tempmon	critical	2020-08-19 21:07:50	?
l16-tempmon	critical	2020-08-19 12:41:00	?

Thanks again!

[thambisetty]

index=snmptrapd
| fields Agent_Hostname,Temperature,alertStatus_1 
| eventstats latest(_time) as latest_time by Agent_Hostname 
| where latest_time=_time
| stats latest(*) as * by Agent_Hostname

[yshen]

@thambisetty Thanks for helping!

Here are the results by executing your suggestions:

Agent_Hostname alertStatus_1 latest_time
l16-tempmon critical 1597939392
l18-tempmon critical 1597896470
l20-tempmon normal 1597919409

It still misses the temperature.

[thambisetty]

index=snmptrapd | fields Agent_Hostname,Temperature,alertStatus_1 | eventstats latest(_time) as latest_time by Agent_Hostname | where latest_time=_time | stats latest(*) as * by Agent_Hostname

check Temperature fields in fields command, if its mismatching to your field, change it accordingly.

and to convert time, add below end of the search

| convert ctime(latest_time) 

[yshen]

@thambisetty  You're right that I misspelled the temperature field, with the correction, the query becomes:

index=snmptrapd sourcetype=trapParsed 
| fields Agent_Hostname,alertStatus_1, temperatureVlaue 
| eventstats latest(_time) as latest_time by Agent_Hostname 
| where latest_time=_time 
| stats latest(*) as * by Agent_Hostname 
| convert ctime(latest_time)

Here is the resulted outcome:

Agent_Hostname	latest_time	temperatureVlaue
l16-tempmon	08/20/2020 11:42:29	85.0

 There is temperatureValue field, the time format is correct, but I only got one event about 16-tempmon.

I expect to the latest event for 18-tempmon and 20-tempmon

I do appreciate your great help, and see the hope now! Thanks again!

[thambisetty · Accepted solution]

can you expand your time range and see. Or just try to run base search before fields command and see in hosts whether you are seeing others or not.

the reason could be for not seeing other may not be available in the time you chosen.

[yshen]

@thambisetty  I further studied your example, 

I experimented line by line, 

Here is my annotation of your example:

index=snmptrapd sourcetype=trapParsed critical # filter the events contain "critical"
| fields Agent_Hostname,alertStatus_1,status, temperatureVlaue # select the fields
| rename Agent_Hostname as Location # rename the field
| eventstats latest(_time) as latest_time by Location # compute the lasets(_time) and add latest_time to the events
| where latest_time=_time # select the events' whose _time equals to latest_time
| stats latest(*) as * by Location # what's the purpose? Seems redundant?
| convert ctime(latest_time) # convert the format of lastest_time to be readable

It seems to me that line of 

stats latest(*) as * by Location

basically for each Location value, get the latest event for all the fields selected above. By the state above it, 

where latest_time=_time

effectively for each value of Location there will be only  events whose _time value equals to the lastes_time for the Location value, unless there are multiple events for the same value of Location with the same _time, usually there will be only one event for the Location value.

Even if there were multiple events for the same Location value, and the same _time equaling to the laste_time, it seems 

stats latest(*) as * by Location

will select the latest for the value combinations of all the selected fields for each Location value?

So it sounds the purpose of this statement is to remove duplicate events for each Location value?

Thanks again!

[yshen]

@thambisetty  you're right again! I forgot to have long enough time window. With the time window as last 24 hours. It works as expected, resulting the following:

Agent_Hostname alertStatus_1 latest_time temperatureVlaue

l16-tempmon		08/20/2020 12:42:29	83.3
l18-tempmon	critical	08/19/2020 21:07:50	74.0
l20-tempmon	normal	08/20/2020 03:30:09	79.8

Thanks again for teaching me valuable lessons!

[thambisetty]

@yshen 
no worries 😉