How to edit my search to track consecutive and dual logins by the same user?

[krishnacasso]

Trying to make a table to track login of a user at same time from different IP.

[AzA][][host][12/Mar/2017:**15:28:29** -0600][Agentname][asfafaadvwegwegwevw][**USER=abc**][2412-34234-32235-2323-341342-234234][automatic][as124-4f12c2-fef3-f23f23f3f34d]
[**24.00.00.242**][/abc/aed/dsd][method][][][][qwrqwqsasasdaqwawsadsas][][protect][][][][][]

[AzA][][host][12/Mar/2017:**15:28:40** -0600][Agentname][asfafaadvwegwegwevw][**USER=abc**][2412-34234-32235-2323-341342-234234][automatic][as124-4f12c2-fef3-f23f23f3f34d]
[**23.00.00.4**][/abc/aed/dsd][method][][][][qwrqwqsasasdaqwawsadsas][][protect][][][][][]

[AzA][][host][12/Mar/2017:**15:28:55** -0600][Agentname][asfafaadvwegwegwevw][**USER=abc**][2412-34234-32235-2323-341342-234234][automatic][as124-4f12c2-fef3-f23f23f3f34d][**234.234.244.242**][/abc/aed/dsd][method][][][][qwrqwqsasasdaqwawsadsas][][protect][][][][][]

I am trying to make a table with IP like below(Only USER having different IP in log at same second).

USER         IP1                Time          City                   IP2            Time             City      TIMEDifference(sec)
abc     24.00.00.242    15:28:29     City1         23.00.00.4     15:28:40       City2         11

I am trying something like below and not sure to join them and get the city field of two IP.

basesearch  ip!=""  USER!=""  |  rename USER AS login| rename ip AS Address | iplocation allfields=true eval first_t=strftime(_time, "%Y-%m-%d %H:%M:%S") | 
eval prev_t=strftime(prev_t, "%Y-%m-%d %H:%M:%S")   | first(ipAddress) as IP1 by login | second(ipAddress) as IP2 by login 

What command I can use to divide the IP as IP1 and IP2 and get city information for respective IP?

Thanks.

[somesoni2 · Accepted solution]

Give this a try
Updated#3

basesearch  ip!=""  USER!="" 
| eval Time=_time
| bucket span=1m _time 
| stats list(ip) as IP list(Time) as Time by _time USER
| where mvcount(mvdedup(IP))=2 AND mvcount(IP)=2
| eval IP1=mvindex(IP,0) | iplocation IP1 | rename City as IP1_City
| eval IP2=mvindex(IP,1) | iplocation IP2 | rename City as IP2_City
| eval "TimeDifference(sec)"=abs(tonumber(mvindex(Time,1))-tonumber(mvindex(Time,0)))
| eval IP1_Time=strftime(mvindex(Time,0), "%Y-%m-%d %H:%M:%S")
| eval IP2_Time=strftime(mvindex(Time,1), "%Y-%m-%d %H:%M:%S")
| table USER IP1* IP2* "TimeDifference(sec)"

[DalJeanis]

We need to change the list(ip) to values(ip) or do a real mvdedup on it before line 5-7.

Also, someone might be logged on three times, so the mvcount should test for >1.

And as long as I'm being picky, we should add an "n" to "TimeDifferece(sec)".

[krishnacasso]

Thank you Jeanis/Somesoni.
Update 3 worked perfectly. Thanks for sharing knowledge, Learned few good ways of using eval.

[DalJeanis]

So, here we go...

This first part generates some sample data. Three records will come out in one minute, two in another.

| gentimes start="01/25/2017:23:00:37" end="01/25/2017:23:03:40" increment=27s | eval _time=starttime | table _time | eval USER="joe" | streamstats count | eval Time = _time | eval ip="001.001.001".case(count % 3==0,".000",count%3==1,".001",true(),".002") | table _time USER ip

Here's the meat. The key is using stats list() for IP and Time, then using mvfind to find the offset of the deduped IPs, grabbing the time that lines up with them. In the case of more than two, this code will always return the two lowest-numbered IP addresses.

 | bucket span=1m _time 
 | stats list(ip) as IP list(Time) as Time by _time USER
 | eval IPnondup=mvdedup(IP)
 | where mvcount(IPnondup)>1
 | eval IP1offset=mvfind(IP,mvindex(IPnondup,0))
 | eval IP2offset=mvfind(IP,mvindex(IPnondup,1))
 | eval IP1=mvindex(IP,IP1offset)| iplocation IP1 | rename City as IP1_City
 | eval IP2=mvindex(IP,IP2offset) | iplocation IP2 | rename City as IP2_City
 | eval IP1_Time=strftime(mvindex(Time,IP1offset), "%Y-%m-%d %H:%M:%S")
 | eval IP2_Time=strftime(mvindex(Time,IP2offset), "%Y-%m-%d %H:%M:%S")
 | eval "TimeDifference(sec)"=abs(tonumber(mvindex(Time,IP1offset))-tonumber(mvindex(Time,IP2offset)))
 | table USER IP1* IP2* "TimeDifference(sec)"

[somesoni2]

The values function will change the order of the IP and TIME (always ascending), so I had to avoid that. May be a dedup based on _time, USER and ip just before the stats is needed. And yes, it doesn't handle 3 IPs, it could be any number actually.

[DalJeanis]

I think you mean

 | stats list(ip) as IP list(Time) as Time by USER _time

[somesoni2]

Ohh yes.. Thanks @DalJeanis for pointing that out. Updated.

[krishnacasso]

Hi Somesoni,
Can we give condition like IP1!=IP2 and display a table.
Here we are getting the table with IP1 and IP2 same IP.
Can we add a filter to list only IP which are not same for the user at same minute.

[somesoni2]

Try the updated#2 answer. I've added a check that both the IPs in field IP are not same.

[krishnacasso]

Hi,
I tried updated2 still getting IP1 and IP2 same IP for some, This table gives all users info whoes IP are same and IP are different.
I tried something like below using where IP1!=IP2

basesearch  ip!=""  USER!="" 
 | eval Time=_time
 | bucket span=1m _time 
 | stats list(ip) as IP list(Time) as Time by _time USER
 | where mvcount(mvdedup(IP))=2
 | eval IP1=mvindex(IP,0) | iplocation IP1 | rename City as IP1_City
 | eval IP2=mvindex(IP,1) | iplocation IP2 | rename City as IP2_City | where IP1!=IP2
 | eval IP1_Time=strftime(mvindex(Time,0), "%Y-%m-%d %H:%M:%S")
 | eval IP2_Time=strftime(mvindex(Time,1), "%Y-%m-%d %H:%M:%S") | where IP1_City != IP2_City
 | table USER IP1* IP2* "TimeDifferece(sec)"

I took out time difference eval using that in search getting below error.

Error in 'eval' command: Typechecking failed. '-' only takes numbers.

Is there any syntax missing.

[somesoni2]

I'm confused. The mvcount(mvdedup(IP)) is matching 2 then , the field IP should have two distinct values. It may be the case that there are multiple records for same IP and thus the later mvindex are resulting in same IP. Similarly, the Time field has all epoch values so, the regular mathematical operation should work just fine. Give updated answer a try.

[somesoni2]

In your example, the logins are within same minute, not same second. Which one is your requirement? So, basically if a user logs in using two different IP, you want to list details for them?

[krishnacasso]

Sorry I mean logins are at same minute and with different IP. Yes I want user table who have different IP at same minute. Thanks.