Hi Guys,
We have built a small Splunk app to retrieve and index web usage info from multiple SQL databases. My Splunk version is 4.0.10.
We have proxies in multiple time zones that reports to the same SQL server and the only way to know what is the timezone of the event is to check where the reporting proxy is located.
Sample Data:
DATE_TIME RECORD_NUMBER USER_NAME WEBPROXY_IP DEST_IP SRC_IP DEST_PORT BYTES_SENT BYTES_RECEIVED URL_DOMAIN FULL_URL
4/10/2010 11:00 543450000000000 user123 10.1.10.10 10.11.11.11 10.1.10.100 80 1000 2000 testsite.com http://testsite.com/page1
4/10/2010 11:01 123450000000000 user321 10.2.10.10 10.22.22.22 10.2.20.100 80 1000 2000 testsite.com http://testsite.com/page1
4/10/2010 11:02 433450000000000 user432 10.3.10.10 10.33.33.33 10.3.30.100 80 1000 2000 testsite.com http://testsite.com/page1
The WEBPROXY_IP is being forcibly set as the host for each event via props/transforms.
I saw the following example on the documentation and tried to replace the host name with the IP address of the source proxy (my host value) without success:
[http://www.splunk.com/base/Documentation/4.0.10/Admin/Applytimezoneoffsetstotimestamps][1]
Examples
Events are coming to an indexer from New York City (in the US/Eastern timezone) and Mountain View, California (US/Pacific). To correctly handle the timestamps for these two sets of events, the props.conf for the indexer needs the timezone offset to be specified as US/Eastern and US/Pacific respectively.
The first example sets the timezone offset of events from host names that match the regular expression nyc.* with the US/Eastern time zone.
[host::nyc*]
TZ = US/Eastern
Any thoughts / recommendations on how to force the timezones based on by host name (which is actually an IP address) on all past and future events?
Below are my configurations:
::::::::::::::
inputs.conf
::::::::::::::
# Data coming from a SQL Database via scripted input every 60s
[script://$SPLUNK_HOME/etc/apps/webproxy/bin/webdb1.sh]
disabled = false
source = mssql
sourcetype = webproxy:webfilter
interval = 60
index = idx_webproxy
# Data coming from a SQL Database via scripted input every 60s
[script://$SPLUNK_HOME/etc/apps/webproxy/bin/webdb2.sh]
disabled = false
source = mssql
sourcetype = webproxy:webfilter
interval = 60
index = idx_webproxy
::::::::::::::
props.conf
::::::::::::::
[webproxy:webfilter]
AUTOKV=none
REPORT-webproxy_header = webproxy_header
REPORT-static_product_for_webproxy = static_product_for_webproxy
TRANSFORMS-force_host_for_webproxy = force_host_for_webproxy
#My attempt to fix the issue
[host:: 10.1.10.10]
TZ = US/Eastern
[host:: 10.2.20.10]
TZ = US/Montain
[host:: 10.3.30.10]
TZ = US/Pacific
::::::::::::::
transforms.conf
::::::::::::::
[static_product_for_webproxy]
REGEX = (.)
FORMAT = vendor::webproxy product::webfilter
[force_host_for_webproxy]
REGEX =.*
SOURCE_KEY=MetaData:WEBPROXY_IP
DEST_KEY = MetaData:Host
FORMAT = host::$1
[webproxy_header]
DELIMS = "\t"
FIELDS = "DATE_TIME","RECORD_NUMBER","USER_ID","USER_NAME","WEBPROXY_IP","DEST_IP","SRC_IP","DEST_PORT","BYTES_SENT","BYTES_RECEIVED","URL_DOMAIN","FULL_URL"
... View more