How to search unfinished transaction events and duration without a unique ID?

[mistertj3]

Hello all and thank you for any help in advance,

I have a log of tunnels like so:

Oct  2 15:23:08 localhost charon: 10[IKE] IKE_SA SVR1-to-20[1] established between 192.168.40.2[192.168.40.2]...192.168.40.2[192.168.40.2]    
Oct  2 15:23:08 localhost charon: 14[IKE] CHILD_SA SVR1-to-20{2} established with SPIs s4zac98e_o a4c0986d_o and TS 192.168.20.0/24 === 192.168.30.0/24    
Oct  2 15:23:26 localhost charon: 15[IKE] 192.168.30.1 is initiating a Main Mode IKE_SA    
Oct  2 16:29:31 localhost charon: 00[IKE] closing CHILD_SA SVR1-to-20{2} with SPIs p0fzc995_a (3192 bytes) 45fjz5d9_o (2112 bytes) and TS  192.168.20.0/24 === 192.168.30.0/24    
Oct  2 16:29:31 localhost charon: 00[IKE] deleting IKE_SA SVR1-to-20[1] between 192.168.70.2[192.168.70.2]...192.168.40.2[192.168.40.2]

I have managed to extract the source and destinations. And I believe I have filtered the finished tunnels using the transaction and streamstats function with a table function at the end:

charon | transaction startswith="established between" endswith="deleting IKE_SA" | convert ctime(_time) as Date | streamstats range(_time) as "Duration" window=0

But now I would like unfinished tunnels/transactions, or rather current tunnels. One problem is I don't have a unique ID because the field "SPIs" are constantly changing when there are re-authentications and the IKE processes aren't consistent. So here is what I am trying to find in one search with one table, or two searches with two separate tables, or if necessary, two appended searches:
- Unfinished transactions
- Durations

Any help would be greatly appreciated!

Thank you

[wpreston]

Try modifying your transaction command to include the keepevicted flag, and then searching on the closed_txn field that transaction creates. You should take a look at the results of a transaction search to make sure that events whereclosed_txn=0 are those that you want to count as unfinished transactions. Also, I'm not sure that streamstats is needed. The Transaction command automatically creates a field called "duration" that contains the duration of the transaction. Examine it a and see if it fits your needs. So perhaps a search like this?

charon | transaction startswith="established between" endswith="deleting IKE_SA" keepevicted=t | convert ctime(_time) as Date | eval Transaction_Status=if(closed_txn=1,"Finished","Unfinished") | table Transaction_Status duration