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!
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.
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
@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.
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)
@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!
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.
@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!
@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!
@yshen
no worries 😉