Apache High Level Visitor Info

The following query gives a breakdown on traffic by clientip. I run this over all time so I can get detailed information on first visit versus latest visit as you can see below.

sourcetype=access_combined (referer_domain!="https://gosplunk.com" AND referer_domain!="http://gosplunk.com")
| iplocation clientip
| stats first(_time) as First_Visit latest(_time) as Last_Visit sum(eval(round(bytes/1024/1024,2))) as MB first(Country) as Country count as Views first(referer_domain) as Referer_Domain first(uri) as Landing_Page by clientip
| eval diff_time_days=round((Last_Visit-First_Visit)/60/60/24, 6)
| convert ctime(First_Visit) ctime(Last_Visit)
| fields clientip Country First_Visit Last_Visit diff_time_days MB Views Referer_Domain Landing_Page
| fillnull value="0"

This will return something like the following:

If you want to run this as a scheduled search, which I advise doing due to the lengthy historical search of all time you can add an outputlookup to the end and search against that for near instantaneous results:

sourcetype=access_combined (referer_domain!="https://gosplunk.com" AND referer_domain!="http://gosplunk.com")
| iplocation clientip
| stats first(_time) as First_Visit latest(_time) as Last_Visit sum(eval(round(bytes/1024/1024,2))) as MB first(Country) as Country count as Views first(referer_domain) as Referer_Domain first(uri) as Landing_Page by clientip
| eval diff_time_days=round((Last_Visit-First_Visit)/60/60/24, 6)
| convert ctime(First_Visit) ctime(Last_Visit)
| fields clientip Country First_Visit Last_Visit diff_time_days MB Views Referer_Domain Landing_Page
| fillnull value="0"
| outputlookup historical_client_insight.csv

Once this search runs you can access this by running the following:

| inputlookup historical_client_insight.csv

Check out the difference in search run time. Barely more than a second versus more than 80 seconds! If you have a lot of users accessing this information you’ll want to do this as a scheduled search. Just be sure to change the permissions on the lookup file so intended users can access it.

Share This:

Comments

  1. YiHu

    stats count as 访问次数 sum(bytes) as t1 first(Country) as 国家 by clientip | eval 流量=round(t1/1048576,2)
    I suggest that the bytes be calculated separately, which is more accurate. Thank you again.

Leave A Comment?