topic How to create a search that should map a session from an internal application to the corresponding VPN session? in Splunk Search

How to create a search that should map a session from an internal application to the corresponding VPN session?

alexandrucrc — Tue, 29 Mar 2022 16:11:02 GMT

Hey guys,

I`m trying to create a search that should map a session from an internal application to the corresponding VPN session.

Main search - fields: IP_ADDRESS, USER_AD, _time - internal application login sessions

Sub search - fields: Framed_IP_Address, User_Name, _time - VPN allocating internal IP.

Basically my approach was to join left the VPN search, to main search (internal application login sessions) by Internal IP, but the main problem is that when the results table is displayed, it will map the first VPN session that is found with the specified IP_Address from the join, and I need to map the latest IP allocation.

Example:

IP 10.0.0.1 was allocated to user x at 10:00. - user x did not attempt to log into internal app.

IP 10.0.0.1 was allocated to user y0 at 10:40.

IP 10.0.0.1 made a login session for user y1 at 11:00.

My table of results will display:

user x, user y1, 10.0.0.1, 10.0.0.1, 11:00, 10:00

Instead of :

user y0, user y1, 10.0.0.1, 10.0.0.1, 11:00, 10:40

I understand from join command documentation that "join left=L right=R usetime=true earlier=true where L.IP_ADDRESS=R.Framed_IP_Address" shall look for the IP in the internal app login session, and it will map it with the first event that has that IP in the VPN allocation search, prior to the internal application session.

Could you please help me to get the latest VPN session for the IP that is matched in the internal application login session instead of the earliest(as it is by default in join command)?

index=x host=internal_application
| eval time2=strftime(_time, "%m/%d/%y %I:%M:%S:%p")
| join left=L right=R usetime=true earlier=true where L.IP_ADDRESS=R.Framed_IP_Address [search index=x sourcetype="cisco:acs" Acct_Status_Type=Interim-Update earliest=-12h latest=-1m
| eval time1=strftime(_time, "%m/%d/%y %I:%M:%S:%p")]
| table R.User_Name, L.USER_AD, R.Framed_IP_Address, L.IP_ADDRESS, L.time2, R.time1
| rename R.User_Name as VPN_User, L.USER_AD as Hercules_user, R.Framed_IP_Address as "IP assigned by VPN", L.IP_ADDRESS as "IP Hercules", L.time2 as "User connecting at", R.time1 as "IP allocation time"
| eval Hercules_user=lower(Hercules_user)
| where Hercules_user!=VPN_User
| table VPN_User, Hercules_user, "IP assigned by VPN", "IP Hercules", "User connecting at", "IP allocation time"

Re: Join issues

ITWhisperer — Tue, 29 Mar 2022 13:59:09 GMT

Try sorting the events you are joining

index=x host=internal_application | eval time2=strftime(_time, "%m/%d/%y %I:%M:%S:%p") | join left=L right=R usetime=true earlier=true where L.IP_ADDRESS=R.Framed_IP_Address [search index=x sourcetype="cisco:acs" Acct_Status_Type=Interim-Update earliest=-12h latest=-1m | sort 0 -_time | eval time1=strftime(_time, "%m/%d/%y %I:%M:%S:%p")] | table R.User_Name, L.USER_AD, R.Framed_IP_Address, L.IP_ADDRESS, L.time2, R.time1 | rename R.User_Name as VPN_User, L.USER_AD as Hercules_user, R.Framed_IP_Address as "IP assigned by VPN", L.IP_ADDRESS as "IP Hercules", L.time2 as "User connecting at", R.time1 as "IP allocation time" | eval Hercules_user=lower(Hercules_user) | where Hercules_user!=VPN_User | table VPN_User, Hercules_user, "IP assigned by VPN", "IP Hercules", "User connecting at", "IP allocation time"

Re: Join issues

alexandrucrc — Tue, 29 Mar 2022 14:30:30 GMT

Thanks for your reply, your help is very much appreciated!

I`ve tried sort but the same results are displayed as without sorting.

I`ve also tried sort and head and did not work.

Re: Join issues

ITWhisperer — Tue, 29 Mar 2022 14:37:57 GMT

Have you tried with earlier=false

Re: Join issues

alexandrucrc — Tue, 29 Mar 2022 15:18:01 GMT

Yes sir, I did, but changing this parameter would make my search useless as I`m trying to find VPN sessions that were active prior to the login in the internal app.

Re: Join issues

ITWhisperer — Tue, 29 Mar 2022 15:32:13 GMT

Please can you share some anonymised events from your searches so we can see what it is you are dealing with?