compare field values for VPN times

[bcusick]

Hi,

I have a search that combines 2 sources (VPN and another event system - system B). I am trying to pinpoint if events happen from system B while the user is remotely logged in. I have start_time and end_time from my VPN source, and execution_time from system B. They are in the same format and if my search has | where start_time < end_time | it shows that the values are comparable. However I cannot compare execution_time to start_time/end_time. All 3 of these fields show up as possible fields, but there are multiple "execution_time's" for each log. I tried dedup, but the point is to list the executions that were made during VPN sessions.

Here is a basic view of my search:

source=VPN OR source="B" | eval user=coalesce(ad_id,citrix_user)  | transaction user   | where eventcount > 2 | where mvcount(source)>1 | where execution_time > start_time | Table start_time, execution_time, end_time, user

This is just supposed to show where execution was after the login (I'm looking at a transaction log where this exists, but it does not show up when I add | where execution_time > start_time |

[somesoni2]

Try this:

source=VPN OR source="B" | eval user=coalesce(ad_id,citrix_user) | eval tempField=ticket_number."#".execution_time | transaction user   | where eventcount > 2 | where mvcount(source)>1 | Table start_time, tempField, end_time, user | mvexpand tempField | rex field=tempField "(?<ticket_number>.*)#(?<execution_time>.*)" | fields - tempField | where strptime(execution_time,"%m/%d/%Y:%H:%M:%S") > strptime(start_time,"%m/%d/%Y:%H:%M:%S") AND strptime(execution_time,"%m/%d/%Y:%H:%M:%S") < strptime(end_time,"%m/%d/%Y:%H:%M:%S")

[bcusick]

Can I have more than one value for execution_time PER event for this? Or would it have to be one single value?

[somesoni2]

I tried with some sample data and it worked fine with normal string as well. To be on safer side, we can include the time conversion and comparison. See the updated answer.

[bcusick]

So this pulls 8 "events" which are transactions by the users, but the stats table says "no results found". Basically it looks like it's finding all the transactions, but still not executing the WHERE clause correctly. Do my times need to be in epoch for this? start, end, and execution time look like: 03/12/2014:01:33:50

[bcusick]

Close! VPN contains citrix_user, start_time, and end_time.
System B has ad_id, ticket_number, and execution_time...however the transaction pulls all the execution_time's and the user's 1 start_time and 1 end_time. So if I search for one user, I get 1 log with 1 start_time and 1 end_time, but multiple execution_time's. I think this is what's throwing me off. Transaction makes it all 1 log but I only want to see a ticket_number if execution_time is in between start_time and end_time

[somesoni2]

If I am understanding right, in VPN log there will one record per user session and will have field user(ad_id or citrix_user), start_time and end_time (for session). In systemB log there will be user(citrix_user or ad_id), execution_time and there can be multiple entries. You need to find, for each VPN Session, all events from systemB log if there are more then one logs available. Please correct me if I am wrong.

[yannK]

The time is in epoch time in splunk, but they are converted at display time.
What is the format of your execution_time ?

Try to convert all to epoch before the where condition.
see http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/convert

| convert timeformat="mytimeformat" mktime(execution_time) AS execution_time_epoch | where execution_time_epoch > start_time

[bcusick]

No, that's what my coalesce statement is for. It creates the field "user" which links the two sources to one log per user. This part works, and now I just need to say "only show me logs where 1 or more executions happened between start_time and end_time"

[somesoni2]

Does both sources have any common field?

[bcusick]

Disregard the extra ")" after source=B