<--- NOOB
Ok...so here is my quandry... I have a query (see below) that returns a list of users, ips and client info from the geoip app. I want to take these results and only look at user with more than one ip associated with them. I used dedup to get rid of duplicates so this is a pretty boiled down set of results. However, there are still thousands of them. How do I get rid of "non-unique" users keeping only the users that appear more than once.
query:
index=<some_index> c_ip=* User_Agent!="*<string>*" cs_username!="-" rs_Content_Type = "text/html*" | `exclude_internal_ip` | dedup username ip | lookup geoip clientip as ip | fillnull value=NULL | table username,ip,client_country,client_region,date,time,client_city,client_lat,client_lon | sort by str(username),num(ip),date,time.str(client_region)
output is:
user1 xxx.xx.xx.xx US etc...
user2 xxx.xx.xx.yy US etc...
user2 xxx.xx.xx.xy US etc...
user3 xxx.xx.xx.xz US etc...
user4 xxx.xx.xx.zz US etc...
user4 xxx.xx.xx.zx US etc...
I want to pair down the list to:
user2 xxx.xx.xx.yy US etc...
user2 xxx.xx.xx.xy US etc...
user4 xxx.xx.xx.zz US etc...
user4 xxx.xx.xx.zx US etc...
Use eventstats
to find users with multiple logins.
index=<some_index> c_ip=* User_Agent!="*<string>*" cs_username!="-" rs_Content_Type = "text/html*" | `exclude_internal_ip` | eventstats dc(ip) as distinct_ips by username| search distinct_ips>1 | lookup geoip clientip as ip | fillnull value=NULL | table username,ip,client_country,client_region,date,time,client_city,client_lat,client_lon | sort by str(username),num(ip),date,time.str(client_region)
awesome! I just moved the eventstats to the end :
index=some_log c_ip=* cs_User_Agent!="exclude_internal_ip
| dedup cs_username ip | lookup geoip clientip as ip | fillnull value=NULL | table cs_username,ip,client_country,client_region,date,time,client_city,client_lat,client_lon | sort by str(cs_username),num(ip),date,time.str(client_region) | eventstats dc(ip) as distinct_ips by cs_username| search distinct_ips>1 |
this lists only the users with multiple ips!
Thank You!!!!!
I would use eventstats
in the search string.
With eventstats
you can the dc(x)
function which will provide a distinct (unique) count of x (which could be your IP addresses). I have not tested this, so may need a little tweaking, but perhaps this:
index=<some_index> c_ip= User_Agent!="<string>" cs_username!="-" rs_Content_Type = "text/html"
| exclude_internal_ip
| lookup geoip clientip as ip
| fillnull value=NULL
| eventstats dc(ip) AS dcIP by username
| dedup username, ip
| where dcSrc>1
| table username,ip,client_country,client_region,date,time,client_city,client_lat,client_lon
| sort by str(username),num(ip),date,time.str(client_region)
Hope this helps.
Ref:
http://docs.splunk.com/Documentation/Splunk/4.2.3/SearchReference/Eventstats
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/CommonStatsFunctions
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/Where
Use eventstats
to find users with multiple logins.
index=<some_index> c_ip=* User_Agent!="*<string>*" cs_username!="-" rs_Content_Type = "text/html*" | `exclude_internal_ip` | eventstats dc(ip) as distinct_ips by username| search distinct_ips>1 | lookup geoip clientip as ip | fillnull value=NULL | table username,ip,client_country,client_region,date,time,client_city,client_lat,client_lon | sort by str(username),num(ip),date,time.str(client_region)
Ah beat me to it!