Web Traffic Archives -

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"

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 […]

Continue Reading →

Simple GeoIP Information for Web Traffic

This simple query will show if IIS traffic came to a given site from three geographical possibilities: “United States” “International” or “Unknown” sources. This relies entirely on geoip lookup. You can change the country of “United States” to anything you desire for you own data set (just make the change in the eval section below!). […]

Continue Reading →

Weekday Web Traffic Summary in IIS

The following Splunk query will show a summary of all weekday activity for a given website in IIS.

sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) | stats count(JSESSIONID) as Value | eval Metric="Total Hits on Weekdays" | append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) | stats dc(JSESSIONID) as Value | eval Metric="Total Visits on Weekdays"] | append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) JSESSIONID=* | stats dc(JSESSIONID) as count by date_wday | stats avg(count) as Value by date_wday | eval Value=round(Value) | top limit=1 Value | eval Metric = "Average Number of Visits per day on Weekdays"]| append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) JSESSIONID=* | stats count(JSESSIONID) as count by date_wday | stats avg(count) as Value by date_wday | eval Value=round(Value) | top limit=1 Value | eval Metric = "Average Number of Hits per day on Weekdays"] | fieldformat Value=tostring(Value,"commas") | fields - count, percent |fields Metric, Value

sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) | stats count(JSESSIONID) as Value | eval Metric="Total Hits on Weekdays" | append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) | stats dc(JSESSIONID) as Value | eval Metric="Total Visits on Weekdays"] | append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) JSESSIONID=* | stats dc(JSESSIONID) as count by date_wday | stats avg(count) as Value by date_wday | eval Value=round(Value) | top limit=1 Value | eval Metric = "Average Number of Visits per day on Weekdays"]| append [search sourcetype="iis" (NOT date_wday=saturday) (NOT date_wday=sunday) JSESSIONID=* | stats count(JSESSIONID) as count by date_wday | stats avg(count) as Value by date_wday | eval Value=round(Value) | top limit=1 Value | eval Metric = "Average Number of Hits per day on Weekdays"] | fieldformat Value=tostring(Value,"commas") | fields - count, percent |fields Metric, Value

Continue Reading →

Visits by Hour of the Day in IIS

The following Splunk query will list the total visits for each hour in a given time range.

sourcetype="iis" | top limit=24 date_hour | sort +date_hour | rename count as Visits | rename date_hour as "Hour of the Day"

1	sourcetype="iis" \| top limit=24 date_hour \| sort +date_hour \| rename count as Visits \| rename date_hour as "Hour of the Day"

Continue Reading →

Total Hits on Most Active Day in IIS

The following Splunk query will return the total number of hits on the most active day in a given time range within an IIS environment:

sourcetype="iis" |  top limit=1 date_mday | rename count as Value| fieldformat Value=tostring(Value,"commas") | eval Metric="Number of hits on Most active date" | fields - date_mday, count, percent | fields Metric, Value

1	sourcetype="iis" \| top limit=1 date_mday \| rename count as Value\| fieldformat Value=tostring(Value,"commas") \| eval Metric="Number of hits on Most active date" \| fields - date_mday, count, percent \| fields Metric, Value

Continue Reading →

Total Hits on Least Active Day in IIS

The following Splunk Query will return the total number of hits to a web site on the least active day of a given time range:

sourcetype="iis" |  rare limit=1 date_mday | rename count as Value | fieldformat Value=tostring(Value,"commas")| eval Metric="Number of hits on least active date" | fields - date_mday, count, percent | fields Metric, Value

1	sourcetype="iis" \| rare limit=1 date_mday \| rename count as Value \| fieldformat Value=tostring(Value,"commas")\| eval Metric="Number of hits on least active date" \| fields - date_mday, count, percent \| fields Metric, Value

Continue Reading →

Most Active Day and Least Active Day for IIS Web Traffic

The following Splunk query will return the most active and the least active days for web traffic in an IIS environment:

sourcetype="iis" | bucket span=1d _time |top limit=1 _time | eval Date=strftime(_time, "%m/%d/%Y") | eval Metric="Most Active Date" | append [search sourcetype="iis" | bucket span=1d _time |rare limit=1 _time | eval Date=strftime(_time, "%m/%d/%Y") | eval Metric="Least Active Date"] | fields - _time, count, percent | fields Metric, Date

Continue Reading →

Visits by Days of the Week in IIS

The following Splunk query will show the number of web visits for each weekday:

sourcetype="iis" | eval uppercase=upper(substr(date_wday,1,1)).substr(date_wday,2)|dedup JSESSIONID| top limit=7 uppercase | eval sort_field=case(uppercase=="Sunday",1, uppercase=="Monday",2, uppercase=="Tuesday",3, uppercase=="Wednesday",4, uppercase=="Thursday",5, uppercase=="Friday",6, uppercase=="Saturday",7) | sort + sort_field | fields - sort_field |rename count as Visits | rename uppercase as "Day of the Week"

sourcetype="iis" | eval uppercase=upper(substr(date_wday,1,1)).substr(date_wday,2)|dedup JSESSIONID| top limit=7 uppercase | eval sort_field=case(uppercase=="Sunday",1, uppercase=="Monday",2, uppercase=="Tuesday",3, uppercase=="Wednesday",4, uppercase=="Thursday",5, uppercase=="Friday",6, uppercase=="Saturday",7) | sort + sort_field | fields - sort_field |rename count as Visits | rename uppercase as "Day of the Week"

Continue Reading →

Tag: Web Traffic