How to find the latest event per device

[JandrevdM]

Good day,

I am trying to find the latest event for my virtual machines to determine if they are still active or decommissioned. The object is the hostname and the command is where I can see if a device was deleted or just started. I will then afterwards add the command!="*DELETE"

index=db_azure_activity sourcetype=azure:monitor:activity  change_type="virtual machine"
| rename "identity.authorization.evidence.roleAssignmentScope" as subscription
| stats max(_time) as time by  command object subscription change_type resource_group
| convert ctime(time)
```| dedup object```
| table  change_type object resource_group subscription command time 
| sort object asc

[ITWhisperer · Accepted solution]

Try something like this

index=db_azure_activity sourcetype=azure:monitor:activity  change_type="virtual machine"
| rename "identity.authorization.evidence.roleAssignmentScope" as subscription
| dedup object
| table  change_type object resource_group subscription command _time 
| sort object asc

[JandrevdM]

Thanks! I initially got it right and then tried to think to deep into it. Forgot that if you dedup that splunk will take the latest event.

[gcusello]

Hi @JandrevdM ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @JandrevdM,

could you better describe your requirement?

using your search you have the last events for your grouping fields.

You could add a condition that the last event was before the observation period (e.g. before one day) so you'll have devices that didn't send logs in the last 1 day, is this your requirement?

if this is your requirement, you could use something like this:

index=db_azure_activity sourcetype=azure:monitor:activity  change_type="virtual machine"
| rename "identity.authorization.evidence.roleAssignmentScope" as subscription
| stats latest(_time) AS _time BY command object subscription change_type 
resource_group
| where _time<now()-86400
| table  change_type object resource_group subscription command _time 
| sort object asc

Ciao.

Giuseppe