How do I get the timestamps of the first and last events in a transaction?

[BlueSocket]

Dear All,

I am setting up a report of Username, Logged in time, Logged out time, Internal and External IP Addresses from a VPN node log. I have already determined how I can get the identifying marks for the start and end events, the IP Addresses (all in different events - thank you) and I have created a transaction to group them together. Here is my search string, as is:

index=infrastructure sourcetype=syslog Session_Number="*" | transaction Session_Number | fields Outside_IP, Client_Inside_IP, login_username

However, I then want to use the Internal IP Address and start (logged in) and end (logged out) times and then use the data in a subsearch against other logs.

I know that I could use the stats command to get the Earliest and Latest times, but I need the other fields in the output, so I need a transaction and that would get me:

index=infrastructure sourcetype=syslog Session_Number="*" | stats earliest(_time) AS Login_Time, latest(_time) AS Logout_Time by Session_Number | convert ctime(Login_Time) ctime(Logout_Time)

However, do I put these two together to have both? Ideally, I would ask that Splunk add the fields _transaction_start_time and _transaction_end_time to the function, but that might be asking too much.

How do I do this, please?

Kindest regards,

BlueSocket

[wpreston · Accepted solution]

There are a few ways to do this, here are a couple that come to mind:

1. The time of the first event in the transaction is assigned to _time for the entire transaction. The transaction command automatically assigns a duration field to each transaction. You can eval the end time to be _time + duration. This gives you a per-transaction Login_Time and Logout_Time. So you'd end up with:
   ...beginning of your search and transaction | eval Login_Time=_time | eval Logout_Time=_time + duration | ...rest of your search or reporting commands...
   2.You could eval the start and end times before your transaction command in the search string, then when your transaction is built, the Login_Time and Logout_Times are added as fields to the transaction:

   ... your search before transaction | eval Login_Time=if(searchmatch("Received User-Agent header"),_time,null()) | eval Logout_Time=if(searchmatch("Session statistics - bytes in"),_time,null()) | transaction command and rest of search...

Hopefully one of those ideas will help you out.

[BlueSocket]

Thanks for that. I prefer the "eval Login_Time=_time | eval Logout_Time=_time + duration" one - less work for the Search Head and less work than my solution.

[BlueSocket]

Erm, I think that I just worked it out, but can anyone give a better solution?

Firstly create the Search to create the Logon_Time and Logoff_Time values and then use appendcols with the transaction search:

index=infrastructure sourcetype=syslog Session_Number="*" | stats earliest(_time) AS Logon_Time, latest(_time) AS Logoff_Time by Session_Number | appendcols [ search index=infrastructure sourcetype=syslog Session_Number="*" | transaction Session_Number ] | convert ctime(Logon_Time) ctime(Logoff_Time)

Not very nice. I would prefer, however, to have the earliest and latest fields in the transaction command, though! Much simpler!