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. My goal is to check whether users are using their AD account to log into application or not. The problem right now is that field USER_AD is not displayed in the table and I was wondering why it is happening and how could I remediate that. index=tkrsec sourcetype="cisco:acs" Acct_Status_Type=Interim-Update earliest=-8h latest=-1m [ search index=tkrsec host=Hercules_fusion | rename IP_ADDRESS as Framed_IP_Address | table Framed_IP_Address ] | eval time1=strftime(_time, "%m/%d/%y %I:%M:%S:%p") | table User_Name,Acct_Status_Type,Framed_IP_Address,time1 | join type=outer USER_AD [ search index=tkrsec host=Hercules_fusion | eval time2=strftime(_time, "%m/%d/%y %I:%M:%S:%p") | table time2,USER_AD ] | table User_Name,Acct_Status_Type,Framed_IP_Address,time1, USER_AD, time2   ... View more

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" ... View more