How to get latest events by specific field?

[sjs]

Hey folks,

I have a query as such 

.. | ID="*" AND STATUS="*" | table _time ID STATUS

Here is the result which I got

.. | ID="*" AND STATUS="*" | table _time ID STATUS

If you notice here that for the same ID, I got all the status which logged, but I would like to have the latest status here

I did try few things like

... | ID="*" AND STATUS="*" | stats latest(STATUS) by ID | table _time ID STATUS

Here is what I got

I did get the unique ID's but rest of the fields are getting as null.

Could you please help me here

[richgalloway · Accepted solution]

The _time and STATUS fields are null because they were discarded by the stats command.  Try this

... | ID="*" AND STATUS="*" 
| eventstats latest(STATUS) as latestStatus by ID 
| where STATUS==latestStatus
| table _time ID STATUS

[gcusello]

Hi @sjs,

after a stats command you have only the fields that you used in the command, so you have to declare all the field you want, something like this:

| stats 
   latest(_time) AS _time 
   last(STATUS) AS STATUS 
   BY ID 
| table _time ID STATUS

Ciao.

Giuseppe