Splunk Search
Highlighted

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

Motivator

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=junipersaauthentication |
transaction user src mvlist=t startswith="eventtype=junipersaauthenticationsuccess" endswith="eventtype=junipersaauthenticationlogout" keepevicted=t

| eval State = if(closedtxn == 1, "Disconnected", "Connected")
| eval starttime = mvindex(
time,0)

| eval elapsedsecs = 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.

0 Karma
Highlighted

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

Builder

I wonder if you can combine xenapp:65:session in the transaction itself. It is unclear to me what src represents in junipersalog. 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

0 Karma
Highlighted

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

Motivator

The problem with using src is that the src in the junipersalog 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?

0 Karma
Highlighted

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

Builder

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

0 Karma
Highlighted

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

Motivator

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?

0 Karma
Highlighted

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

Builder

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