Table keep last event by criteria

[erwanlebaron]

Hi

I've a question regarding stat or eventstat option last.
I would like to keep the last "event" in a table with several informations and I don't succeed.

index=dynatrace
| streamstats last("consumedHostUnits") by "vmwareName"
| eval FirstTime=strftime(firstSeenTimestamp/1000,"%d-%m-%Y %H:%M"), LastTime=strftime(lastSeenTimestamp/1000,"%d-%m-%Y %H:%M")
| table "vmwareName" "ipAddresses{}" "consumedHostUnits" "managementZones{}.name" monitoringMode FirstTime LastTime _time
| sort "consumedHostUnits" desc

And I've 3 times a row for a server

And if I use "stats" instead of "eventstats" I lost data for the others column

index=dynatrace
| stats last("consumedHostUnits") as HU by "vmwareName"
| eval FirstTime=strftime(firstSeenTimestamp/1000,"%d-%m-%Y %H:%M"), LastTime=strftime(lastSeenTimestamp/1000,"%d-%m-%Y %H:%M")
| sort HU desc
| table "vmwareName" "ipAddresses{}" HU "managementZones{}.name" monitoringMode FirstTime LastTime _time

What is the solution to have the first screenshot with only a single line as the second screenshot ?

Regards

[erwanlebaron]

I was exactly what I was looking for.
I hadn't understood the "values" while using stats.

Thanks a lot

[mayurr98 · Accepted solution]

try this:

index=dynatrace 
| stats last("consumedHostUnits") as HU values(firstSeenTimestamp) as firstSeenTimestamp values(lastSeenTimestamp) as lastSeenTimestamp values("ipAddresses{}") as "ipAddresses{}" values("managementZones{}.name") as "managementZones{}.name" values(monitoringMode) as monitoringMode values(_time) as time by "vmwareName" 
| eval FirstTime=strftime(firstSeenTimestamp/1000,"%d-%m-%Y %H:%M"), LastTime=strftime(lastSeenTimestamp/1000,"%d-%m-%Y %H:%M") 
| convert ctime(time) as time 
| sort HU desc 
| table "vmwareName" "ipAddresses{}" HU "managementZones{}.name" monitoringMode FirstTime LastTime time