How to obtain most recent event per host using rex and table commands?

[jambraun]

My problem is I don't think stats will work for what I'm trying, or my syntax is wrong. Either way, hit a stumbling block.

I have a search that queries the status of a server configuration and outputs to a table. Example:

 index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 | rex "STRING: (?<Sync_Status>.*)$" | table host _time Sync_Status

This may return something like the following:
server1 2016-09-22 20:40:02 All devices in the device group are in sync

server2 2016-09-22 20:50:03 Changes pending
server1 2016-09-22 20:51:18 All devices in the device group are in sync

server2 2016-09-22 20:51:18 All devices in the device group are in sync

I'd like to only show the latest event per host in my results. I've seen examples using eval (which might be where I'm headed if I need to compare) but using 'stats' completely obliterates my table results:

index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 | stats last(_time) by host | rex "STRING: (?<Sync_Status>.*)$" | table host _time Sync_Status

Server1 NO_DATA NO_DATA

Server2 NO_DATA NO_DATA

There must be a way to only return the most recent record per host and then perform the rex + table format?

Thank you!

Jarred

[mydog8it · Accepted solution]

Processing of the data needs to be completed before the stats command. Provided your base search returns the results you are looking for, try this:

index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0
 | rex "STRING: (?.*)$"
 | stats latest(Sync_Status) latest(_time) as TIME by host
 | table host TIME Sync_Status
 | convert ctime(TIME)

I do a similar search that works in my environment that works well.

[jambraun]

Thanks for the response mydog. I think it's close but we're not quite there.

The following is the output from the search. The Sync_Status field is still empty:
HOST TIME SYNC_STATUS
cn 09/28/2016 19:19:01

gb1 09/28/2016 19:19:01

gb2 09/28/2016 19:19:01

inr 09/28/2016 19:19:01

jpr 09/28/2016 19:19:03

sg1 09/28/2016 19:19:03

sg2 09/28/2016 19:19:03

us1 09/28/2016 19:19:03

us2 09/28/2016 19:19:01

us3 09/28/2016 19:19:01

xx1 09/28/2016 19:19:03

xx2 09/28/2016 19:19:03

As soon as I add the Table line, the Sync_Status is lost in the output. Here's the output from the code minus the Table portion:

[
index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0
| rex "STRING: (?.*)$"
| stats latest(Sync_Status) latest(_time) as TIME by host
]

HOST SYNC_STATUS TIME
cn1 1475105041

gb1 All devices in the device group are in sync 1475105043

gb2 All devices in the device group are in sync 1475105041

in1 1475105041

jp1 1475105043

sg1 All devices in the device group are in sync 1475105043

sg2 All devices in the device group are in sync 1475105043

us1 All devices in the device group are in sync 1475105041

us2 All devices in the device group are in sync 1475105043

us3 1475105041

xx1 All devices in the device group are in sync 1475105043

xx2 All devices in the device group are in sync 1475105043

Do you see an error in the search above?

[sundareshr]

The alias for latest(Sync_Status) was missing. Try this

 index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0
  | rex "STRING: (?.*)$"
  | stats latest(Sync_Status) as Sync_Status latest(_time) as TIME by host
  | table host TIME Sync_Status
  | convert ctime(TIME)

[jambraun]

Bingo! Looks like the added "as Sync_Status" did it.

I learn by examples so this is awesome. Thank you!