How to correlate multiple transactions as a single event?

[erwan_raulet]

I have defined transactions to determine the cut-off times for our telecom links. We have two telecom operators per site and I want to differentiate between links of cuts and the loss of a site in case of power failure for example. I use the start time to count the number of transactions per site. When the number is 1, it is a loss of link and when the number is 2, it is a loss of site. Only a few times the start time is not exactly the same with 1 second deviation therefore result 2 transactions. How do I correlate both transactions as the same event?
Thank you for your help.

[woodcock]

Try this:

host="10.xxx.yyy.*" ospf eventtype="ospf_down*" OR eventtype="ospf_full" Base
| eval start_Time=if(eventtype="ospf_down" OR eventtype="ospf_down_if",_time,null())
| eval end_Time=if(eventtype="ospf_full",_time,null())

| streamstats count(eval(eventtype="ospf_full")) AS sessionID BY host VoisinIP Interface
| stats range(_time) AS duration count values(*) AS * BY host VoisinIP Interface sessionID 

| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(start_Time) as Debut
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(end_Time) as Fin
| eval "Duree Perte"=tostring(duration,"duration")

[koshyk]

it might be easier if you provide examples of different events and we can write the sample spl for you

[erwan_raulet]

Here is my search to determine transactions:

host="10.xxx.yyy.*" ospf eventtype="ospf_down*" OR eventtype="ospf_full" Base
| eval start_Time=if(eventtype="ospf_down" OR eventtype="ospf_down_if",_time,null())
| eval end_Time=if(eventtype="ospf_full",_time,null())
| transaction host VoisinIP Interface startswith=eval(eventtype="ospf_down" OR eventtype="ospf_down_if") endswith=eval(eventtype="ospf_full")
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(start_Time) as Debut
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(end_Time) as Fin
| eval "Duree Perte"=tostring(duration,"duration")

And the result is:

Mar 9 07:27:39 10.xxx.yyy.61 Mar 9 07:27:39 10.xxx.yyy.61 TH2PE1: 525776 Base OSPF-WARNING-tmnxOspfNgNbrStateChange-2042 [VR: 1 OSPFv2 (0) ]: LCL_RTR_ID 10.xxx.yyy.61: Neighbor 10.aaa.bbb.1 on if-MTP1-1 router state changed to down (event BFD_DOWN)
Mar 9 07:33:05 10.xxx.yyy.61 Mar 9 07:33:05 10.xxx.yyy.61 TH2PE1: 525790 Base OSPF-WARNING-tmnxOspfNgNbrStateChange-2042 [VR: 1 OSPFv2 (0) ]: LCL_RTR_ID 10.xxx.yyy.61: Neighbor 10.aaa.bbb.1 on if-MTP1-1 router state changed to full (event EXC_DONE)

Mar 9 07:27:38 10.xxx.yyy.61 Mar 9 07:27:38 10.xxx.yyy.61 TH2PE1: 525744 Base OSPF-WARNING-tmnxOspfNgNbrStateChange-2042 [VR: 1 OSPFv2 (0) ]: LCL_RTR_ID 10.xxx.yyy.61: Neighbor 10.aaa.bbb.2 on if-MTP1-2 router state changed to down (event BFD_DOWN)
Mar 9 07:33:04 10.xxx.yyy.61 Mar 9 07:33:04 10.xxx.yyy.61 TH2PE1: 525788 Base OSPF-WARNING-tmnxOspfNgNbrStateChange-2042 [VR: 1 OSPFv2 (0) ]: LCL_RTR_ID 10.xxx.yyy.61: Neighbor 10.aaa.bbb.2 on if-MTP1-2 router state changed to full (event EXC_DONE)

I hope this will help you better understand my problem