Comparing two multivalue fields

[gcbysc]

I'm trying to compare multiplevalue fields in a search.

My query is below:

sourcetype=app2_log OR sourcetype=app1_log | stats values(App1_Login_Time) as App1_Login_Time values (App2_Login_Time) as App2_Login_Time by User | eval res = if(App1_Login_Time > App2_Login_Time, "illegal activity", "ok")

So the output for above query is below.

User	App1_Login_Time	App2_Login_Time	Res
user1	08:41:33	08:55:20	ok
user2	08:43:00	09:01:18	ok
user3	08:40:25	08:10:30 08:20:12 08:30:15	ok
user4	08:30:20	08:10:05	illegal activity
user5	09:35:20	09:50:00	ok

As you can see, the query check clients login time for both application. If clients are logged in to "app2" before "app1", it will be an illegal activity for my case. My queries output is working for user1,2,4 and 5. User4 logged in to app2 before application1 so in res column it says 'illegal activity'. For user3, it also logged in to app2 before app1 but in res column it says 'ok'. There are many users and I checked most of them. When there are multiple values for an application login time, the query can't compare and give true result. The result for user3 should be like this:

User	App1_Login_Time	App2_Login_Time	Res
user1	08:41:33	08:55:20	ok
user2	08:43:00	09:01:18	ok
user3	08:40:25	08:10:30 08:20:12 08:30:15	illegal activity illegal activity illegal activity
user4	08:30:20	08:10:05	illegal activity
user5	09:35:20	09:50:00	ok

So with this output, I need to get 3 "illegal activity" outputs for user3. Also There can be a case where user can log in to "app2" before "app1" 1 time which should considered as "illegal activity" and after that log in to "app1" again and after that "app2" which should considered as "ok". 

user 6

08:30:20 09:15:00

08:15:10 09:30:00

illegal activity ok

Also I need to count the number of illegal activity for specific user.

I couldn't make splunk to do this comparison. Any ideas?

EDIT:

There is also a scenario that users login to app1 but they may not login to app2 which is consideres as 'ok' in my case

User	App1_Login_Time	App2_Login_Time	Res
user01	08:30:00		ok

Thank you.

[bowesmana]

It sound like you could have the case where you can have n values of app 1 login and m values of app 2 login. Dealing with indeterminate numbers of elements in the two MV fields will be challenging, but one option is to have the times as epoch times in the MV field, in which case, you can use numerical comparisons

I think perhaps you could do this by mvexpanding the App1_Login_Time field and then you know you will have a single value.

If you have Splunk 8, the eval+mvmap function will allow you to iterate over the values of the field, performing an operation on that value of the field.

See this query from your example data - it will only with with Splunk 8

| makeresults
| eval _raw="User	App1_Login_Time	App2_Login_Time	Res
user1	08:41:33	08:55:20	ok
user2	08:43:00	09:01:18	ok
user3	08:40:25	08:10:30,08:20:12,08:30:15	ok
user4	08:30:20	08:10:05	illegal activity
user5	09:35:20	09:50:00	ok
user6	08:30:20,09:15:00	08:15:10,09:30:00	illegal activity,ok"
| multikv forceheader=1
| fields - _raw _time linecount
| foreach App* Res [ eval <<FIELD>>=split(<<FIELD>>,",") ]
| mvexpand App1_Login_Time
| foreach App* [ eval T_<<MATCHSTR>>=strptime(<<FIELD>>,"%T") ]
| eval NewRes=mvmap(T_2_Login_Time,if(T_2_Login_Time-T_1_Login_Time<0,"illegal activity","ok"))
| table User App1_Login_Time App2_Login_Time NewRes Res

 You can see that NewRes for User3 is now as wanted. User6 results in two rows and on the surface, it looks like it's giving you the right answer, but you will need to validate the possible permutations for multi valued App1 logins to see if that correct in all cases, and then work out how to aggregate the results for User6 to a single row.

Hope this helps

[gcbysc]

Hello. We are using splunk 6.5.3 environment so the mvmap function is not supported.

So in order to solve this case, should I use and compare linux epoch time values?

I can convert the App1_Login_Time and App2_Login_Time date information to linux epoch time. Actually, in the first place, splunk indexer get these date values from corresponding databases as Linux epoch time. I change them with `to_char(App1_Login_Time,'DD-MM-YYYY HH24:MI:SS')`so that I can read them in splunk search head. If I don't write this function in indexer, indexer will get those values with epoch time. 

[ITWhisperer]

What about these scenarios:

user 7

09:15:00

09:10:10 09:30:00

user 8

08:30:20 09:15:00

08:45:10

How do you determine which log on time to use when determining whether it is illegal activity or not?

[gcbysc]

Actually I use first application to determine if it is illegal activity or not.

[ITWhisperer]

So for user 8, is 08:45:10 OK since it is after 08:30:20 or illegal since it is before 09:15:00?

[gcbysc]

That should be 'ok' for 8:30 and 'illegal' for 9:15. So there should be 2 'Res' records in this case.

[ITWhisperer]

| makeresults
| eval _raw="User	App1_Login_Time	App2_Login_Time	Res
user1	08:41:33	08:55:20	ok
user2	08:43:00	09:01:18	ok
user3	08:40:25	08:10:30,08:20:12,08:30:15	ok
user4	08:30:20	08:10:05	illegal activity
user5	09:35:20	09:50:00	ok
user6	08:30:20,09:15:00	08:15:10,09:30:00	illegal activity,ok
user7	09:15:00	09:10:10,09:30:00	ok
user8	08:30:20,09:15:00	08:45:10	ok"
| multikv forceheader=1
| fields - _raw _time linecount
| foreach App* Res [ eval <<FIELD>>=split(<<FIELD>>,",") ]
| mvexpand App1_Login_Time
| mvexpand App2_Login_Time
| eval new_Res=if(strptime(App1_Login_Time,"%T")-strptime(App2_Login_Time,"%T") < 0, "ok", "illegal activity")
| stats values(App1_Login_Time) as App1_Login_Time by User, App2_Login_Time, new_Res

[gcbysc]

Your command works like a charm in your example. I'll try to combine this with my sourcetypes, and let you know if it works.