Only show certain transactions

[bcusick]

Hi,

I want to show events that were executed during someone's VPN session. I can create a transaction that pulls from two sources: VPN and changes. VPN contains session times (start_time and end_time). changes contains change_time (all times are in epoch).

However I get multiple change_time values per transaction (expected behavior if people make multiple changes). I only want to show the change_time fields that are within start_time and end_time. My search requires data coming in from BOTH sources and the eventcount must be greater than 1 (1 VPN log represents both start_time and end_time).

source="changes" OR source="VPN" | eval user=coalesce(ad_id,citrix_user) | transaction user | where eventcount > 1 |where mvcount(source)>1 | Table user, start_time, change_time, end_time, ticket_number

If I do | where change_time > start_time | nothing shows up (I think because there are multiple change_time's per transaction) but if I put | where start_time < end_time | this works perfectly.

How can I create a transaction that re-uses the VPN log but matches it separately with each change log?

source=changes contains change_time, ticket_number, ad_id

source=VPN contains start_time, end_time, citrix_user (citrix_user and ad_id hold the same values

Thanks,

Brian

[wpreston]

Would the startswith and endswith options in transaction get what you need? This may need to be tweaked because I'm not familiar with your data, but something like this:

source="changes" OR source="VPN" | eval user=coalesce(ad_id,citrix_user) | transaction user startswith="start_time" endswith="end_time" | where eventcount > 1 | where mvcount(source)>1 | Table user, start_time, change_time, end_time, ticket_number

If this doesn't work, try adding a string or field value that is present in the start_time event but not in the other events for the value of startswith, and a string or field value that is present in the end_time event but not in the other events for the value of endswith.

[bcusick]

This helps..the last event is unique (contains both start_time and end_time). So this breaks it down, and I gave maxspan=12h (because the session times out after that)..but this still doesn't allow me to get granular with selecting strictly the correct transactions. All change events will have identical fields, and the only common trait will be the user name (which will show up in times outside of what I want to see)