Splunk Search

Nested Subsearches - How do I return a list of web activity based on IPs leased to MACs over time

sprooit
Observer

Use case: I have three sourcetypes:

DHCP Events with these fields:
- dhcp_mac
- dhcp_ip (the ip just leased)
- dhcp_username

Proxy Events with these fields:
- px_src_ip
- px_url

User_Database with these fields:
- ud_username
- All the other fields that go with user identities: firstname, lastname, supervisor, etc.

ud_username matches dhcp_username

I’ve been asked to allow a supervisor to get a list of users for which they are responsible and provide analysis on those user’s web traffic.

I have a search that will generate all usernames based on supervisor using the supervisor field from the User_Database sourcetype. I then match those usernames to the DHCP events pulling a distinct count of MACs. This generates my list of MACs for the users. I then have to build a list of what IPs are assigned to these MACs, the start time of the lease, and the end time. I then pass the IP, starttime and endtime to a search that looks through the Proxy events looking for activity from each px_src_ip between the start and end times.

I have two issues.

The first: due to the way I’m passing the MACs into the IP generating search, I’ll have times where the early epoch time is greater than the later epoch time, so that is killing the search.
The second: I’m in nested subsearch land. I’m sure there is a better way of doing this, but my skills/knowledges are what they are.

Here is the search. The list of macs in the search below are actually populated via another subsearch. I removed that subsearch and replaced with the actual result of the subsearch in an attempt to remove noise.

sourcetype=Proxy 
[ | search sourcetype=DHCP
[ | search sourcetype=DHCP ((dhcp_mac=”00:8C:AB:22:0B:BF”) OR (dhcp_mac=”48:7D:50:25:7A:69”) OR (dhcp_mac=”54:62:87:91:6A:BB”) OR (dhcp_mac=”54:22:33:51:87:A7”) OR (dhcp_mac=”54:62:69:DB:6A:BB”))
| sort str(dhcp_mac), -_time
| streamstats current=false last(dhcp_ip) as px_src_ip last(_time) as time_of_change by dhcp_mac  
| where dhcp_ip!=px_src_ip  
| convert ctime(time_of_change) as time_of_change  
| streamstats range(_time) as Duration window=2 earliest(time_of_change) as earliest latest(time_of_change) as latest  
| convert timeformat="%m/%d/%Y %H:%M:%S" mktime("earliest")  
| convert timeformat="%m/%d/%Y %H:%M:%S" mktime("latest")  
| fields px_src_ip, earliest, latest    
| format "(" "(" " " ")" "OR" ")"]
| table _time, px_src_ip, px_url, px_category

This search produces the following error:

Error in 'search' command: Unable to parse the search: Invalid time bounds in search: start=1467930882 > end=1467901898

I can see why. If you look at the subsearch results: see "earliest="1467930882" latest="1467929042"" below

( ( px_src_ip="192.168.50.172" earliest="1467950435" latest="1467950435" ) OR ( px_src_ip="192.168.50.175" earliest="1467950426" latest="1467950435" ) OR ( px_src_ip="192.168.50.172" earliest="1467854651" latest="1467950426" ) OR ( px_src_ip="192.168.50.195" earliest="1467854651" latest="1467926792" ) OR ( px_src_ip="192.168.50.201" earliest="1467925132" latest="1467926792" ) OR ( px_src_ip="192.168.50.195" earliest="1467923906" latest="1467925132" ) OR ( px_src_ip="192.168.50.201" earliest="1467909647" latest="1467923906" ) OR ( px_src_ip="192.168.50.195" earliest="1467907579" latest="1467909647" ) OR ( px_src_ip="192.168.50.201" earliest="1467905508" latest="1467907579" ) OR ( px_src_ip="192.168.50.195" earliest="1467901898" latest="1467905508" ) OR ( px_src_ip="192.168.50.224" earliest="1467930882" latest="1467901898" ) OR ( px_src_ip="192.168.50.193" earliest="__1467930882__" latest="__1467929042__" ) OR ( px_src_ip="192.168.50.181" earliest="1467910119" latest="1467929042" ) OR ( px_src_ip="192.168.50.193" earliest="1467910007" latest="1467910119" ) )

You can see there is an earliest time that has a greater epoch time than a later epoch time. That is because of the way the list of MACs is being returned. Since I’m returning MACs from multiple users and the way I’m doing streamstats, events like the two below are causing the error. An earlier event is coming after a later event.

7/7/16
8:05:37.000 AM
Jul 07 08:05:37 192.168.100.98 : 2016/07/07 08:05:37 EDT,1,-1,IP Address Update,0,BSMITH118,"Smith, Bob",192.168.50.128,54:62:87:91:6A:BB,"Adapter Smith, Bob IP Address changed from 192.168.50.224 to 192.168.50.128"

7/7/16
6:04:02.000 PM
Jul 07 18:04:02 192.168.100.98 : 2016/07/07 18:04:02 EDT,1,-1,IP Address Update,0,JSIMS809,"Sims, John",192.168.50.193,54:22:33:51:87:A7,"Adapter Sims, John IP Address changed from 192.168.50.181 to 192.168.50.193"

I’m looking down two paths to resolve this issue. Find a way to pass only one user’s MAC address into the streamstats search at a time instead of passing a long list of MACS, keeping them separate instead of compiling all the macs into one list.

Or

Making the search better.

I suspect the latter is the correct answer. I’m just unsure of how to change the search.

0 Karma

sundareshr
Legend

Try this (verify userid field names)

  sourcetype=Proxy 
  [ | search sourcetype=DHCP ((dhcp_mac=”00:8C:AB:22:0B:BF”) OR (dhcp_mac=”48:7D:50:25:7A:69”) OR (dhcp_mac=”54:62:87:91:6A:BB”) OR (dhcp_mac=”54:22:33:51:87:A7”) OR (dhcp_mac=”54:62:69:DB:6A:BB”)) 
 | stats earliest(_time) as earliest latest(_time) as latest by dhcp_mac user
 | eval Duration=latest-earliest
 | convert ctime(time_of_change) as time_of_change  
 | convert timeformat="%m/%d/%Y %H:%M:%S" mktime("earliest")  
 | convert timeformat="%m/%d/%Y %H:%M:%S" mktime("latest")  
 | rename dhcp_mac AS px_src_ip
 | fields px_src_ip, earliest, latest    
 | format "(" "(" " " ")" "OR" ")"]
 | table _time, px_src_ip, px_url, px_category
0 Karma

sprooit
Observer

Thanks for the reply. Based on the results of your search, this returns only one earliest and latest time from the DHCP logs. The users are constantly getting different DHCP leases (and therefor IPs) based on where they are on the property, so there should be several different sets of IPs/earliest/latest. Like this:

dhcp_mac                      px_src_ip       earliest                           latest
F8:95:C7:E3:3B:32       10.21.1.247 07/07/2016 08:44:47 07/07/2016 08:50:47
F8:95:C7:E3:3B:32           10.21.2.253 07/07/2016 08:41:47 07/07/2016 08:44:47
F8:95:C7:E3:3B:32       10.21.98.22 07/06/2016 15:47:37 07/07/2016 08:41:47
F8:95:C7:E3:3B:32       10.21.1.247 07/06/2016 15:47:30 07/06/2016 15:47:37
F8:95:C7:E3:3B:32       10.21.98.22 07/06/2016 15:47:07 07/06/2016 15:47:30
F8:95:C7:E3:3B:32       10.21.2.253 07/06/2016 15:39:57 07/06/2016 15:47:07
F8:95:C7:E3:3B:32       10.21.1.247 07/06/2016 15:39:47 07/06/2016 15:39:57
F8:95:C7:E3:3B:32       10.21.2.253 07/06/2016 15:39:37 07/06/2016 15:39:47
F8:95:C7:E3:3B:32       10.21.1.247 07/06/2016 15:26:27 07/06/2016 15:39:37
F8:95:C7:E3:3B:32       10.21.2.253 07/06/2016 15:11:47 07/06/2016 15:26:27
F8:95:C7:E3:3B:32       10.21.1.247 07/06/2016 15:08:27 07/06/2016 15:11:47
F8:95:C7:E3:3B:32       10.21.2.253 07/06/2016 15:08:17 07/06/2016 15:08:27
F8:95:C7:E3:3B:32       10.21.1.247 07/06/2016 15:06:27 07/06/2016 15:08:17
0 Karma

sundareshr
Legend

How about this

sourcetype=Proxy 
[ | search sourcetype=DHCP ((dhcp_mac=”00:8C:AB:22:0B:BF”) OR (dhcp_mac=”48:7D:50:25:7A:69”) OR (dhcp_mac=”54:62:87:91:6A:BB”) OR (dhcp_mac=”54:22:33:51:87:A7”) OR (dhcp_mac=”54:62:69:DB:6A:BB”)) 
| streamstats window=2 first(dhcp_ip) as px_src_ip first(_time) as latest by dhcp_mac user
| where px_src_ip!=dhcp_ip
| rename _time AS earliest
| fields px_src_ip, earliest, latest    
| format "(" "(" " " ")" "OR" ")"]
| table _time, px_src_ip, px_url, px_category
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.