Splunk Search

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

krishnacasso
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
1 Solution

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

DalJeanis
Legend

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
Path Finder

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

0 Karma

DalJeanis
Legend

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

somesoni2
Revered Legend

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.

0 Karma

DalJeanis
Legend

I think you mean

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

somesoni2
Revered Legend

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

0 Karma

krishnacasso
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

somesoni2
Revered Legend

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

0 Karma

krishnacasso
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

krishnacasso
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
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...