I need to create a query which returns a list of unique hosts (shost), the most recent 'status' column matching that host, and furthermore, the most recent time of an Event_desc matching 'Heartbeat' for that same host. All of the data is in the same index.
I think my approach may be a bit misguided since I'm not seeing any results. If I've done a decent job explaining this, hopefull that answer is more clear to someone else: How might I be able to achieve this? This is what I have so far:
index=companyindex | customparser | dedup shost, status | join shost [search Event_desc = Heartbeat | dedup _time] | table _time, host, shost, status, LastHeartbeat
You can probably get away without join by using append-stats (this will perform better as well. Try something like this:-
index=companyindex | customparser | dedup shost, status |table _time shost status| append [search Event_desc = Heartbeat | stats max(_time) as LastHeartbeat by hostfieldForthisSearch | rename hostfieldForthisSearch as shost | table shost,LastHeartbeat ] | stats values(*) as * by shost | table _time, shost, status, LastHeartbeat