How to stitch together a session by userid with dissimilar formats

[packet_hunter]

Scenario:
I have to match up two events into a session by the userid; one event represents a vpn login (vpnIdIn) and the other represents a vpn logout (vpnIdOut).

The userid is present in each event message field however the event message field (msg) values are different based on the (login or logout) event. Therefore I need to REX the msg field differently for each event. The following code shows that I rex each event type differently to get the userid in the same format.

For example, here is a search for login and logout events:

login

host="192.168.1.1" AND message_id = 1 | rex field=msg "user = (?<vpnIdIn>[^ ]*)" | eval Time=date_hour.":".date_minute.":".date_second  | eval Date = date_wday."  ".date_month."/".date_mday."/".date_year | stats values(Time) values(Date)  count by vpnIdIn

logout

host="192.168.1.1" AND message_id = 2 | rex field=msg "User\s+\<(?<vpnIdOut>[^\>]+)\>" | eval Time=date_hour.":".date_minute.":".date_second  | eval Date = date_wday."  ".date_month."/".date_mday."/".date_year |stats values(Time) values (Date) count by vpnIdOut

Both of these searches produce the same format for the user-id, but are converted to vpnIdIn or vpnIdOut.

For example vpnIdIn = johndoe and vpnIdOut=johndoe.

Any advice to string this together is much appreciated.

Thank you.

[sundareshr · Accepted solution]

How about something like this

host="192.168.1.1" (message_id = 1 OR message_id= 2) | eval action=if(message_id=1, "Login", "Logout") |  rex field=msg "user = (?<user>[^ ]*)" | rex field=msg "User\s+\<(?<user>[^\>]+)\>" | eval Time=date_hour.":".date_minute.":".date_second  | eval Date = date_wday."  ".date_month."/".date_mday."/".date_year | stats values(Time) values(Date)  values(action) count by user

[packet_hunter]

Thank you that works great. If you have a minute, do you know a way to map the time to the login or logout?

With your current code, I only get one time value. When I change your code to list(time) I get two time values but I am not sure if that is the best way... Ideally I would like to see all the login and logout with times for the user for the specific day.

I will do some testing.

Thank you for the help.

[sundareshr]

Try this

.... | stats values(action) count by user time

[packet_hunter]

it actually works, forgot to capitalize time... but I only see one time value still

I am probably not explaining this correctly

for example this is what I see now....

user Time Action Count
johndoe 15:59:2 login 2
logout

I would like....

user Time Action Count
johndoe 15:59:2 login 1
johndoe 16:01:01 logout 1

I thank you for getting me this far, I will keep testing to see if I can get what I need... Thank you!

[sundareshr]

Sorry, my bad...

.... | stats count by user Time action

[packet_hunter]

Thank you sir. That will work. I have learned a lot from you today. Thank you.