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