Splunk Search
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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?

0 Karma
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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)"

View solution in original post

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

I think you mean

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

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.