How to combine transaction search results of one sourcetype with data from another to show chain of events?

[mikaelbje]

I'm working on creating a dashboard that is supposed to show a flow of events in Splunk for VPN logins and Citrix Sessions opened. The idea is to be able to show the src field from juniper_sa_log as it contains the external IP address of the client and display that alongside the session information from Citrix, preferably inside the same time range as the connect/disconnect time of the juniper_sa_log event

It consists of the following sourcetypes:

1. juniper_sa_log - this is the first step where users authenticate
2. Fields: user,src,"Login"|"Logout",...
3. xenapp:65:session - events are generated when a user opens an application.
4. Fields: user,BrowserName,ConnectTime,LogOnTie,SessionId,ServerName

The user field is the same throughout the whole chain of events.

I am able to create a transaction on the first sourcetype to show the duration and whether a VPN session is actie or not with the following search:

eventtype=juniper_sa_authentication  | 

transaction user src mvlist=t startswith="eventtype=juniper_sa_authentication_success" endswith="eventtype=juniper_sa_authentication_logout" keepevicted=t


| eval State = if(closed_txn == 1, "Disconnected", "Connected") 

| eval starttime = mvindex(_time,0)


| eval elapsed_secs = case(State == "Connected" AND NOT duration == 0, now()-starttime, State == "Connected" AND duration == 0, now()-_time, State == "Disconnected" AND NOT duration == 0, duration )


| eval endtime=if(State == "Connected", null(), starttime+duration) | eval ExternalIP = src

| stats  first(starttime) AS starttime latest(realm) latest(State) latest(elapsed_secs) latest(endtime) BY user,ExternalIP

Results:

user,src,starttime,latest(realm),latest(State),latest(elapsed_secs),latest(endtime)

user1,5.5.5.5,1409813577,Java-basert,Disconnected,311,1409813888

user2,4.4.4.4,1409808460,Windows-basert,Connected,12909, 

user3,5.5.5.5,1409810401,Eksterne,Connected,10968, 

user4,5.5.5.5,1409810328,Eksterne,Connected,11041, 

user5,6.6.6.6,1409820159,Windows-basert,Connected,1210, 

johndoe992,7.7.7.7,1409811899,Eksterne,Disconnected,254,1409812153

Now I have the start time of the VPN connection as well as the endtime for disconnected sessions (and obviously no endtime for still connected sessions).

Now I need to connect this with an event from xenapp:65:session:

04.09.2014 11:08:46 - AccessSessionGuid="" AccountName="DOMAIN\johndoe992" ApplicationState="Active" BrowserName="SomeApplicationName" ClientAddress="127.0.0.1" ClientBuffers="0 x 0" ClientBuildNumber="0" ClientCacheDisk="0" ClientCacheLow="3145728" ClientCacheMinBitmapSize="0" ClientCacheSize="0" ClientCacheTiny="32768" ClientCacheXms="0" ClientDirectory="C:\PROGRA~2\Citrix\ICACLI~1\" ClientId="2349571824" ClientIPV4="127.0.0.1" ClientName="MBJERKELAND-PC" ClientProductId="1" ClientType="WI" ClientVersion="14.1.0.0" ColorDepth="Colors32Bit" ConnectTime="09/04/2014 08:24:28" CurrentTime="04.09.2014 09:08:46 GMT" DirectXEnabled="True" DisconnectTime="" EncryptionLevel="Basic" FlashEnabled="True" HorizontalResolution="1080" LastInputTime="09/04/2014 11:07:55" LogOnTime="04.09.2014 06:24:35 GMT" MachineName="XENAPP06" Protocol="Ica" ServerBuffers="0 x 0" ServerName="XENAPP06" SessionId="3" SessionName="ICA-TCP#1" SmartAccessFilters="" State="Active" UsbEnabled="False" VerticalResolution="4864" VirtualIP="" WmpEnabled="True" UserName="johndoe992" FarmName="XenApp65"

I've tried using the first search and then doing a subsearch using join or map but they seem a bit slow and I'm not really sure if I get the right output.

I'd like to be able to get multiple results from the search on xenapp:65:session into the new BrowserName,ConnectTime etc fields after they're joined.

Am I overthinking this? Could this have been achieved through the stats command alone?

The time range is of importance when doing the subsearch as I only need results between the starttime and endttime/now events from the first events.

[iKate]

Hi Mikael! If the question is somehow still relevant I’ll make a suggestion and hope it can(or at least could:) help.
Seems it was better to search by both eventtypes at once; events would be sorted by _time automatically at a search time and with assumption that there can be just one src during transaction for a user, transaction command could be used excluding src that exists just in one eventtype; then a transaction id field can be created, to distinguish transactions from one user, let it be just simple counter. The search can look like this:

eventtype=juniper_sa_authentication OR eventtype=xenapp:65:session 
| transaction user mvlist=t startswith="eventtype=juniper_sa_authentication_success" endswith="eventtype=juniper_sa_authentication_logout" keepevicted=t  
| eval TransactionId=1 
| accum TransactionId 
| eval State = if(closed_txn == 1, "Disconnected", "Connected") 
| eval elapsed_secs = case(State == "Connected" AND NOT duration == 0, now()-starttime, State == "Connected" AND duration == 0, now()-_time, State == "Disconnected" AND NOT duration == 0, duration )    
| eval ExternalIP = src
| stats latest latest(realm), latest(State), latest(elapsed_secs), list(BrowserName), list(ConnectTime), list(LogOnTie), list(SessionId), list(ServerName) BY user TransactionId

[mikaelbje]

I wonder if I can do this in a different way. Perhaps run a nightly search that looks for all closed transactions based on Juniper VPN logs and saves the result as separate events with user, External IP, starttime and endtime to a new index or CSV file, then iterating over each of these events to find data by the same user between starttime and endtime. How could this be accomplished?

[yuanliu]

Not having IP for reference really sucks if a user can connect from multiple locations using simultaneous VPN sessions. If user is allowed only one VPN session at a time, using user alone should suffice as you have startswith and endswith. bucket is perhaps not needed.

BTW, if no other source has field src, | rename src as VPNExternalIP will do. http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Rename

[mikaelbje]

The problem with using src is that the src in the juniper_sa_log sourcetype is the external IP connecting. The src in xenapp:65:session is normally just 127.0.0.1 or an internal IP so it's not any good for correlation. This means that I am left with only the "user" field for correlation. Since I want to break down the sessions and what's been done inside a session I guess I need to do | bucket _time span=24h for instance.
I am renaming the first src field into VPNExternalIP using an eval:

| eval VPNExternalIP = if(sourcetype == "juniper_sa_log", src, null())

Any hints?

[yuanliu]

I wonder if you can combine xenapp:65:session in the transaction itself. It is unclear to me what src represents in juniper_sa_log. If it is the source IP and if that is the same as ClientAddress in xenapp:65:session, the following should work:

sourcetype=juniper_sa_log eventtype=juniper_sa_authentication OR sourcetype=xenapp:65:session |
rename ClientAddress as src |
rename UserName as user |
transaction user src mvlist=t startswith="eventtype=juniper_sa_authentication_success" endswith="eventtype=juniper_sa_authentication_logout" keepevicted=t