Splunk Search

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

jambraun
Explorer

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

Tags (4)
0 Karma
1 Solution

mydog8it
Builder

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.

View solution in original post

0 Karma

mydog8it
Builder

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.

0 Karma

jambraun
Explorer

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?

0 Karma

sundareshr
Legend

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)
0 Karma

jambraun
Explorer

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

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...