How to combine multiple searches with a rex and display results in a table

[jambraun]

Ok, I have 3 searches I'd like to combine the results for and display in a table. The index is the same for all the searches but the event is not. Below are the 3 searches I'm performing (complete with filtering to latest entry and table):

    index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0
| rex "1/1 (?<Failover>.*)$"
| stats latest(Failover) as Failover latest(_time) as TIME by host
| table host TIME Failover
| convert ctime(TIME)

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

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

The data I get for each is HOST | TIME | VARIABLE

Presuming the time codes match up, I could use some advice on how to get these 3 searches to output to a single table.

Thank you!

Jarred

[sideview]

Well, it's far better to avoid the append command if you can, and in this case you certainly can.

A) Simplest. IF you can make the following 3 assumptions
1) That the "Failover" regex will match only the sysCmFailoverStatusSummary events
2) That the "Sync_Status" regex will match only the sysCmSyncStatusSummary events
3) That the "Status" regex will match only the sysCmSyncStatusStatus events,

then you can combine these searches really quite simply as follows:

index=team_f5_metrics ( F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0 )
| rex "1/1 (?<Failover>.*)$"
| rex "STRING: (?<Sync_Status>.*)$"
| rex "STRING: (?<Status>.*)$"
| eval Failover_time = if(isnotnull(Failover),_time,null())
| eval Sync_Status_time = if(isnotnull(Sync_Status),_time,null())
| eval Status_time = if(isnotnull(Status),_time,null())
| stats latest(Failover) as Failover latest(Failover_time) as Failover_time latest(Sync_Status) as Sync_Status latest(Sync_Status_time ) as Sync_Status_time latest(Status) as Status latest(Status_time) as Status_time by host

Conversely, if you CANNOT make those three assumptions, ie if the other regexes will occasionally match elsewhere, then you have to lock it down a bit more.

Here the difference is I'm copying out the _raw to a separate field only for the rows where the _raw exactly matches the corresponding string. Then I have rex extract from that special field instead of always from _raw itself.

index=team_f5_metrics ( F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0 )
| eval Failover_raw = if(searchmatch("F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0"),_raw,null())
| eval Sync_Status_raw = if(searchmatch("F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0"),_raw,null())
| eval Status_raw = if(searchmatch("F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0"),_raw,null())
| rex field=Failover_raw "1/1 (?<Failover>.*)$"
| rex field=Sync_Status_raw "STRING: (?<Sync_Status>.*)$"
| rex field=Status_raw "STRING: (?<Status>.*)$"
| eval Failover_time = if(isnotnull(Failover),_time,null())
| eval Sync_Status_time = if(isnotnull(Sync_Status),_time,null())
| eval Status_time = if(isnotnull(Status),_time,null())
| stats latest(Failover) as Failover latest(Failover_time) as Failover_time latest(Sync_Status) as Sync_Status latest(Sync_Status_time) as Sync_Status_time latest(Status) as Status latest(Status_time) as Status_time by host

[jambraun]

Sorry for not making notes sooner. I was traveling and just got back to this.

The first code block had an error when I tried to run it:
⚠ Error in 'eval' command: The expression is malformed. Expected ).

I'm fairly sure the assumptions are correct so if the values could be pulled without lockdown, that would be amazing. Do you see where the ")" is supposed to be?

[sideview]

Yes - my apologies. There were a couple missing closing parens in there. I've updated my answer and I believe the searches are correct now.

[jambraun]

Ok, that processed, but the output isn't ideal. I sanitized some results so I'll post that.

Here's what I get with the latest code block:

host    Failover    Failover_time   Sync_Status Sync_Status_time    Status  Status_time
cn1 active  1476154441      1476154441      1476154441
gb1 standby 1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443
gb2 active  1476154441  All devices in the device group are in sync 1476154441  All devices in the device group are in sync 1476154441
in1 active  1476154441      1476154441      1476154441
jp1 active  1476154443      1476154443      1476154443
sg1 standby 1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443
sg2 active  1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443
us1 standby 1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443
us2 active  1476154441  All devices in the device group are in sync 1476154441  All devices in the device group are in sync 1476154441
us3 active  1476154443      1476154443      1476154443
xx1 standby 1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443
xx2 active  1476154443  All devices in the device group are in sync 1476154443  All devices in the device group are in sync 1476154443

What I'm expecting is along the lines of:

host    TIME    Failover    Status  SyncStatus
cn1 10/10/2016 22:50    active      Standalone
gb1 10/10/2016 22:50    standby All devices in the device group are in sync In Sync
gb2 10/10/2016 22:50    active  All devices in the device group are in sync In Sync
in1 10/10/2016 22:50    active      Standalone
jp1 10/10/2016 22:50    active      Standalone
sg1 10/10/2016 22:50    standby All devices in the device group are in sync In Sync
sg2 10/10/2016 22:50    active  All devices in the device group are in sync In Sync
us1 10/10/2016 22:50    standby All devices in the device group are in sync In Sync
us2 10/10/2016 22:50    active  All devices in the device group are in sync In Sync
us3 10/10/2016 22:50    active      Standalone
xx1 10/10/2016 22:50    standby All devices in the device group are in sync In Sync
xx2 10/10/2016 22:50    active  All devices in the device group are in sync In Sync

The last block of results is from the code of Sundareshr which is heavily restricted. I had some pictures but I couldn't see how to upload. Sorry.

[sideview]

Hm. Unless you made some inadvertent edits to the table output as you were sanitizing it, I think there's some assumption we're making about the rows that isn't true.

The fact that the Status and the Sync_Status values are always the same, and the "real" values of "In Sync" never make it to the final results, I think means that those two regexes are matching into eachothers events often or all the time, even though my second search is trying to prevent that. Similar problems (and failures of those assumptions) could also explain why the latest times being matched for the three components are always the same.

To troubleshoot, replace the stats command at the end with this:

| table host Failover Failover_time Sync_Status Sync_Status_time Status Status_time 
| convert CTIME(Failover_time) | convert CTIME(Sync_Status_time) | convert CTIME(Status_time)

[jambraun]

Thanks for sticking around on this sideview. I ran the troubleshooting code and looks like we're still getting matches passed through to upstream regex (see below). I don't know if I mentioned it, but it is possible for Sync_Status to be NULL or Blank. Status and Failover will NEVER be NULL or blank. I would also assume the times would always match:

host    Failover    Failover_time   Sync_Status Sync_Status_time    Status  Status_time
cn1             10/13/2016 16:02        10/13/2016 16:02
cn1         Standalone  10/13/2016 16:02    Standalone  10/13/2016 16:02
sg1         All devices in the device group are in sync 10/13/2016 16:02    All devices in the device group are in sync 10/13/2016 16:02
sg1         In Sync 10/13/2016 16:02    In Sync 10/13/2016 16:02
gb1         All devices in the device group are in sync 10/13/2016 16:02    All devices in the device group are in sync 10/13/2016 16:02
gb1         In Sync 10/13/2016 16:02    In Sync 10/13/2016 16:02
in1             10/13/2016 16:02        10/13/2016 16:02
in1         Standalone  10/13/2016 16:02    Standalone  10/13/2016 16:02
sg2         All devices in the device group are in sync 10/13/2016 16:02    All devices in the device group are in sync 10/13/2016 16:02
sg2         In Sync 10/13/2016 16:02    In Sync 10/13/2016 16:02
jp1             10/13/2016 16:02        10/13/2016 16:02
jp1         Standalone  10/13/2016 16:02    Standalone  10/13/2016 16:02
cn1 active  10/13/2016 16:02    1/1 active  10/13/2016 16:02    1/1 active  10/13/2016 16:02
sg1 standby 10/13/2016 16:02    1/1 standby 10/13/2016 16:02    1/1 standby 10/13/2016 16:02
gb2         All devices in the device group are in sync 10/13/2016 16:02    All devices in the device group are in sync 10/13/2016 16:02
gb2         In Sync 10/13/2016 16:02    In Sync 10/13/2016 16:02
gb1 standby 10/13/2016 16:02    1/1 standby 10/13/2016 16:02    1/1 standby 10/13/2016 16:02
xx1         All devices in the device group are in sync 10/13/2016 16:02    All devices in the device group are in sync 10/13/2016 16:02
xx1         In Sync 10/13/2016 16:02    In Sync 10/13/2016 16:02
xx1 active  10/13/2016 16:02    1/1 active  10/13/2016 16:02    1/1 active  10/13/2016 16:02

[sundareshr · Accepted solution]

Try this

*UPDATED*

index=team_f5_metrics (F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0) 
| rex "1/1 (?<Failover>.*)$" 
| rex "STRING: (?<Status>active|standby)$" 
| rex "STRING: (?<Sync_Status>Standalone|In\sSync)$" 
| stats latest(Failover) as Failover latest(Status) as Status latest(Sync_Status) as SyncStatus latest(_time) as TIME by host 
| convert ctime(TIME)
| table host TIME Failover Status SyncStatus

[jambraun]

Thanks for your reply, Sundareshr.

I tried your code and it gave me an "Error in 'eval' command: The arguments to the 'if' function are invalid."

I also added in the rex for Sync_Status since it was missing but still got the same error.

index=team_f5_metrics (F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 OR F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0) 
 | rex "1/1 (?<Failover>.*)$" 
 | rex "STRING: (?<Status>.*)$" 
 | rex "STRING: (?<Sync_Status>.*)$"
 | eval Sync_Status=if("F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0", Status, null())
 | eval Status=if("F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0", null(), Status)
 | stats latest(Failover) as Failover latest(Status) as Status latest(Sync_Status) as SyncStatus latest(_time) as TIME by host 
 | convert ctime(TIME)
 | table host TIME Failover Status SyncStatus

I haven't dealt with eval and if statements yet. Do you see where there error is?

[jambraun]

Looks like my comment didn't post from yesterday. I just wanted to say this latest one will work (Thank You!) but I'd still be interested in seeing if the other will also work without the heavy lockdown. This answer deserves credit none-the-less. I'm getting a lot of understanding from this example.

[aaraneta_splunk]

Hi @jambraun - Glad to hear that you got some good answers from to your question. If one of them has helped to resolve your issue, please don't forget to click "Accept" below the best answer and upvote anything that was helpful. Thanks!

[jambraun]

Sorry for not making notes sooner. I was traveling and just got back to this.

Looks like this latest one will work if I know the string responses before hand (and hopefully don't overlap). I'm learning a lot from this, so thank you! I'm still looking at that first one posted to see how it will work but this deserves credit.

[sundareshr]

Try the updated one. Since the regex for Status and Sync_Status are identical, you don't need the rex for Sync_Status

[jambraun]

That works much better, thanks!
the Status and sync_status aren't the same data so it needs to be played with a bit.

I'm getting some bleed-over from Failover in to Status. The only text should be either 'In Sync' or 'Standalone':

HOST TIME FAILOVER STATUS SYNCSTATUS
cn1 09/29/2016 23:04:02 active 1/1 active

gbs1 09/29/2016 23:04:02 active 1/1 active All devices in the device group are in sync

gbs2 09/29/2016 23:03:02 standby In Sync All devices in the device group are in sync

in1 09/29/2016 23:04:02 active 1/1 active

jp1 09/29/2016 23:03:03 active Standalone

sg1 09/29/2016 23:04:02 active 1/1 active All devices in the device group are in sync

sg2 09/29/2016 23:03:02 standby In Sync All devices in the device group are in sync

us1 09/29/2016 23:04:03 active 1/1 active All devices in the device group are in sync

us2 09/29/2016 23:04:02 standby 1/1 standby All devices in the device group are in sync

us3 09/29/2016 23:04:02 active 1/1 active

xx1 09/29/2016 23:03:02 active In Sync All devices in the device group are in sync

xx2 09/29/2016 23:03:02 standby In Sync All devices in the device group are in sync

[sundareshr]

Try now with updated regex

[Richfez]

Perhaps all you might need is an append?

Update: fixed a typo on the search below

index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 
| rex "1/1 (?<Failover>.*)$" 
| stats latest(Failover) as Failover latest(_time) as TIME by host 
| table host TIME Failover 
| convert ctime(TIME) 
| append 
    [ search index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 
    | rex "STRING: (?<Sync_Status>.*)$" 
    | stats latest(Sync_Status) as Sync_Status latest(_time) as TIME by host 
    | table host TIME Sync_Status 
    | convert ctime(TIME)] 
| append 
    [ search index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0 
    | rex "STRING: (?<Status>.*)$" 
    | stats latest(Status) as Status latest(_time) as TIME by host 
    | table host TIME Status 
    | convert ctime(TIME)]

It's just

search 1
| append [ search 2]
| append [ search 3]

(With a bit of formatting thrown in)

[Richfez]

Also I'm doing some fooling with it, try this.

index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmFailoverStatusSummary.0 
| rex "1/1 (?<Failover>.*)$" 
| stats latest(Failover) as Failover latest(_time) as TIME by host 
| eval output="Failover event " . Failover . " happened at " . TIME 
| table host TIME output 
| append 
    [ search index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusSummary.0 
    | rex "STRING: (?<Sync_Status>.*)$" 
    | stats latest(Sync_Status) as Sync_Status latest(_time) as TIME by host 
    | eval output="Sync Status event " . Sync_Status . " happened at " . TIME 
    | table host TIME output 
| append 
    [ search index=team_f5_metrics F5-BIGIP-SYSTEM-MIB::sysCmSyncStatusStatus.0 
    | rex "STRING: (?<Status>.*)$" 
    | stats latest(Status) as Status latest(_time) as TIME by host 
    | eval output="Sync Status event " . Sync_Status . " happened at " . TIME 
    | table host TIME output ] 
| convert ctime(TIME) | rename output AS "Status Event"

There's a lot of tomfoolery in there that I can't validate (hence why I'm calling it tomfoolery), but it might work or be trivially fixable. I'd still like a clearer picture of the events you have and the sort of output you are looking for, but this might do it.

[Richfez]

Sorry, I was trying to delete my own comment, but deleted the thread of them down here. Fortunately, it didn't really matter.

In reading my above (Which may work fine even if oddly formatted - we can fix that if it's OK otherwise!), and in reading the below, I think it may be very beneficial to both sundareshr and I if you could post a few of the actual events you are basing this on. Preferably a couple of events of each "type" that you describe above. Sanitize them if you need to, of course. 🙂