[influxdb] Need help for IIS access log Grok parser

[Kamal P]

Hi Guys,

Need an expert help to parse the IIS access logs as shown below, I am new
to grok parser and finding it difficult to parse below IIS log format, can
someone please help with grok pattern.

I am using Telegraf 3.1.1 to parse this log

Input from IIS logs one line
---------------------------------------------
#Fields: *date time s-sitename s-computername s-ip cs-method cs-uri-stem
cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie)
cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes
cs-bytes time-taken*

2017-02-02 15:22:17 W3SVC1 axy1-wpsdwrd-01 10.223.1.5 GET /news/news.asp
id=106381 80 - 10.111.222.333 HTTP/1.1
Mozilla/5.0+(compatible;+Googlebot/2.1;++http://www.google.com/bot.html) -
- www.google.co.uk:40004 404 0 2 1439 422 0

Grok pattern tried so far which does not work, used the debugger but can't
find what am I missing - https://grokdebug.herokuapp.com/

CUSTOM_LOG %{DATESTAMP:EventTime} %{WORD:sitename} %{HOSTNAME:computername}
%{IP:hostip} %{URIPROTO:method} %{URIPATH:request}
(?:%{NOTSPACE:queryparam}|-) %{NUMBER:port} (?:%{WORD:username}|-)
%{IP:clientip} %{NOTSPACE:httpversion} %{NOTSPACE:user-agent}
(?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}|-)
%{NUMBER:status} %{NUMBER:sub-status} %{NUMBER:win32-status}
%{NUMBER:bytes-received} %{NUMBER:bytes-sent} %{NUMBER:time-taken}

Appreciate your help in advance.

Thank you,
Kamal

[pabsensi via InfluxData]

I'm having exactly the same issue. Any way to make this work?

[Kamal P]

Hi,

I am able to parse IIS access logs now *BUT *STUCK in the next step where
the Telegraf 1.2.1 is unable to parse the CUSTOM_LOG Patterns, below is the
GROK pattern which works but telegraf is not able to parse the IIS logs..
any help please?


*Working GROK pattern for IIS logs is below*
----------------------------------------------------------------
%{DATESTAMP:EventTime} %{WORD:sitename} %{HOSTNAME:computername}
%{IP:hostip} %{WORD:verb} (%{URIPATH:request}|%{NOTSPACE:request}|/)
(?:%{NOTSPACE:queryparam}|-) %{NUMBER:port} (?:%{WORD:username}|-)
%{IP:clientip} %{NOTSPACE:httpversion} %{NOTSPACE:user-agent}
(?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-) %{NUMBER:resp_code}
%{NUMBER:sub-status} %{NUMBER:win32-status} %{NUMBER:bytes-received}
%{NUMBER:bytes-sent} %{NUMBER:time-taken}

*Telegraf 1.2.1 config file updated below*
---------------------------------------------------------------
# Telegraf configuration

# Telegraf is entirely plugin driven. All metrics are gathered from the
# declared inputs, and sent to the declared outputs.

# Plugins must be declared in here to be active.
# To deactivate a plugin, comment out the name and any variables.

# Use 'telegraf -config telegraf.conf -test' to see what metrics a config
# file would generate.

# Global tags can be specified here in key="value" format.
[global_tags]
# dc = "us-east-1" # will tag all metrics with dc=us-east-1
# rack = "1a"

# Configuration for telegraf agent
[agent]
## Default data collection interval for all inputs
interval = "10s"
## Rounds collection interval to 'interval'
## ie, if interval="10s" then always collect on :00, :10, :20, etc.
round_interval = true

## Telegraf will cache metric_buffer_limit metrics for each output, and
will
## flush this buffer on a successful write.
metric_buffer_limit = 1000
## Flush the buffer whenever full, regardless of flush_interval.
flush_buffer_when_full = true

## Collection jitter is used to jitter the collection by a random amount.
## Each plugin will sleep for a random time within jitter before
collecting.
## This can be used to avoid many plugins querying things like sysfs at
the
## same time, which can have a measurable effect on the system.
collection_jitter = "0s"

## Default flushing interval for all outputs. You shouldn't set this below
## interval. Maximum flush_interval will be flush_interval + flush_jitter
flush_interval = "10s"
## Jitter the flush interval by a random amount. This is primarily to
avoid
## large write spikes for users running a large number of telegraf
instances.
## ie, a jitter of 5s and interval 10s means flushes will happen every
10-15s
flush_jitter = "0s"

## Logging configuration:
## Run telegraf in debug mode
debug = true
## Run telegraf in quiet mode
quiet = false
## Specify the log file name. The empty string means to log to stdout.
logfile = ""

## Override default hostname, if empty use os.Hostname()
hostname = ""


###############################################################################
# OUTPUTS
#
###############################################################################

# Configuration for influxdb server to send metrics to
[[outputs.influxdb]]
# The full HTTP or UDP endpoint URL for your InfluxDB instance.
# Multiple urls can be specified but it is assumed that they are part of
the same
# cluster, this means that only ONE of the urls will be written to each
interval.
# urls = ["udp://localhost:8089"] # UDP endpoint example
urls = ["http://localhost:8086"] # required
# The target database for metrics (telegraf will create it if not exists)
database = "telegraf" # required
# Precision of writes, valid values are "ns", "us" (or "µs"), "ms", "s",
"m", "h".
# note: using second precision greatly helps InfluxDB compression
precision = "s"

## Write timeout (for the InfluxDB client), formatted as a string.
## If not provided, will default to 5s. 0s means no timeout (not
recommended).
timeout = "5s"
# username = "telegraf"
# password = "metricsmetricsmetricsmetrics"
# Set the user agent for HTTP POSTs (can be useful for log
differentiation)
# user_agent = "telegraf"
# Set UDP payload size, defaults to InfluxDB UDP Client default (512
bytes)
# udp_payload = 512



###############################################################################
# INPUTS
#
###############################################################################

# Stream and parse log file(s).
[[inputs.logparser]]
## Log files to parse.
## These accept standard unix glob matching rules, but with the addition of
## ** as a "super asterisk". ie:
## /var/log/**.log ->recursively find all .log files in /var/log
## /var/log/*/*.log ->find all .log files with a parent dir in /var/log
## /var/log/apache.log -> only tail the apache log file
files = ["C:/Temp/test.log"]

## Read file from beginning.
from_beginning = true

## Parse logstash-style "grok" patterns:
## Telegraf built-in parsing patterns: https://goo.gl/dkay10

[inputs.logparser.grok]
## This is a list of patterns to check the given log file(s) for.
## Note that adding patterns here increases processing time. The most
## efficient configuration is to have one pattern per logparser.
## Other common built-in patterns are:
patterns = ["%{CUSTOM_LOG}"]

## Name of the outputted measurement name.
measurement = "CWS_logs"
## Full path(s) to custom pattern files.
## custom_pattern_files = []

## Custom patterns can also be defined here. Put one pattern per line.
custom_patterns = '''CUSTOM_LOG %{DATESTAMP:EventTime} %{WORD:sitename}
%{HOSTNAME:computername} %{IP:hostip} %{WORD:verb:tag}
(%{URIPATH:request}|%{NOTSPACE:request}|/) (?:%{NOTSPACE:queryparam}|-)
%{NUMBER:port} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:user-agent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:resp_code:tag} %{NUMBER:sub-status:int} %{NUMBER:win32-status:int}
%{NUMBER:bytes-received:int} %{NUMBER:bytes-sent:int}
%{NUMBER:time-taken:int}'''

*After executing it does not parse the logs and output looks like below..
does not throw any error as well*
----------------------------------------------------------------------------------------------------------------------------------------------------------
C:\telegraf-1.2>telegraf -config LogParseCWSIIS.conf
2017-02-23T13:05:48Z D! Attempting connection to output: influxdb
2017-02-23T13:05:48Z D! Successfully connected to output: influxdb
2017-02-23T13:05:48Z I! Starting Telegraf (version 1.2.1)
2017-02-23T13:05:48Z I! Loaded outputs: influxdb
2017-02-23T13:05:48Z I! Loaded inputs: inputs.logparser
2017-02-23T13:05:48Z I! Tags enabled: host=
2017-02-23T13:05:48Z I! Agent Config: Interval:10s, Quiet:false,
Hostname:"", Flush Interval:10s
2017/02/23 08:05:48 Seeked C:/Temp/test.log - &{Offset:0 Whence:0}
2017-02-23T13:06:00Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.
2017-02-23T13:06:10Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.
2017-02-23T13:06:20Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.
2017-02-23T13:06:30Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.
2017-02-23T13:06:40Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.
2017-02-23T13:06:50Z D! Output [influxdb] buffer fullness: 0 / 1000 metrics.

Please help if I am missing something?


Thanks,
Kamal

Post by pabsensi via InfluxData
I'm having exactly the same issue. Any way to make this work?

[pabsensi via InfluxData]

I think I found the problem. Try removing the '-' in the all the semantic fields for example instead of %{NUMBER:sub-status:int} make it %{NUMBER:substatus:int} and so on. Hope this works!

[Kamal P]

Hi Guys/Experts,

I am stuck in next step now based on the below email thread. I am able to
parse the Windows IIS access Logs (with the help of Pablo) and able to push
data into influx which has default time field and I am using Grafana for
the dashboard from where I could not query the timestamp which was parsed
from the IIS Logs. The issue is if it is default Apache or Nginx logs then
timestamp is parsed and updated to the time field of Influx (Using Influx
1.2.0.1) using COMMONLOGFORMAT but in case of IIS logs (CUSTOM_LOG) it does
not do that and due to which the Grafana's Time Range filter does not work
properly

IIS Log samples below
------------------------------------
2017-02-02 15:31:00 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++
http://OpenLinkProfiler.org/bot+) - - www.google.com 403 14 0 1446 286 1468
2017-02-02 15:56:04 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++
http://megaindex.com/crawler) - - www.google.com 403 14 0 1427 176 2000
2017-02-02 16:09:19 W3SVC11 server1 101.123.112.50 GET /index.html - 80 -
10.223.129.95 HTTP/1.0
Mozilla/5.0+(Macintosh;+Intel+Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/39.0.2171.99+Safari/537.36
- http://www.google.com/ www.google.com 200 0 0 1578227 427 421
2017-02-02 16:42:13 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1
Mozilla/5.0+(Windows+NT+10.0;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google.com 403 14 0 1446 246 2000

Grok Pattern for above IIS logs to be parsed using Telegraf 1.2
-----------------------------------------------------------------------------------------
custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp} %{WORD:sitename:tag}
%{HOSTNAME:computername} %{IP:sip} %{URIPROTO:csmethod:tag}
%{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-) %{NUMBER:csport}
(?:%{WORD:username}|-) %{IP:clientip} %{NOTSPACE:httpversion}
%{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int}
%{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int}
%{NUMBER:timetaken:int}
'''

Influx output for the log measurement
-----------------------------------------------------------
time timestamp clientip computername cookie csbytes csmethod csport
csquery csstem host host_1 httpversion referer scbytes scstatus
scsubstatus scwin32status sip sitename timetaken useragent
2017-02-27T16:58:42.1476989Z 2017-02-02 15:31:00 W3SVC11 server1
101.123.112.50 GET /test.html - 80 - 10.223.1.192 HTTP/1.1
Mozilla/5.0+(compatible;+spbot/5.0.3;++http://OpenLinkProfiler.org/bot+) -
- www.google.com 403 14 0 1446 286 1468

Now the problem is, if you look at first two columns/tag which is *time and
timestamp in the above influx output, *they are different, ideally I want
the default time series field of measurement which is *time *to have the
timestamp values from IIS logs rather than it is picking up the time from
when the logs are parsed

So to overcome this situation, I've modified the grok patter for the
timestamp column as below

Original --- %{TIMESTAMP_ISO8601:timestamp}
Modified to -- *%{TIMESTAMP_ISO8601:time:tag}*

So I am forcing the timestamp from the IIS Logs to be parsed and updated to
the default *time *field in Influx measurement but still it does not do
that and shows the time of when the logs were parsed instead of timestamp
from the log file. Please let me know what am I missing here and how can I
force the IIS log timestamp to be default *time *in measurement

Thanks,
Kamal

perfect.. thanks man.. that helps as well..cheers!!

Another thing to take in mind: I recommend using TIMESTAMP_ISO8601
instead of DATESTAMP to parse your date in that format, since DATESTAMP has
a Month/Day/Year or Day/Month/Year format (depending on EU or US) instead
of the Year/Month/Day that the log dates are in. Cheers!

great it works..wonderful..
Thanks a lot Pablo, you made my day... have a wonderful day ahead..you
are genius... so this '-' was the issue..

No no, I mean only in the semantic fields. The semantic is the name of
%{DATESTAMP:timestamp} %{WORD:sitename} %{HOSTNAME:computername}
%{IP:sip} %{URIPROTO:csmethod} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-)
%{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}|-) %{NUMBER:scstatus}
%{NUMBER:scsubstatus} %{NUMBER:scwin32status} %{NUMBER:scbytes}
%{NUMBER:csbytes} %{NUMBER:timetaken}

Here is the sample log, try it by removing the '-', the grok pattern
will fail.. may be we need more generic grok pattern in that case
2017-02-02 15:31:00 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++
http://OpenLinkProfiler.org/bot+) - - www.google-docs.com 403 14 0
1446 286 1468
2017-02-02 15:56:04 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++
http://megaindex.com/crawler) - - www.google-docs.com 403 14 0 1427
176 2000
2017-02-02 16:09:19 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.0 Mozilla/5.0+(Macintosh;+Intel+
Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/39.0.2171.99+Safari/537.36
- http://www.google-docs.com/ www.google-docs.com 200 0 0 1578227 427
421
2017-02-02 16:42:13 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google-docs.com 403 14 0 1446 246 2000
2017-02-02 17:35:30 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 200 0 0 1578208 452
890
2017-02-02 17:35:34 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 206 0 0 311789 480 62
2017-02-02 18:39:10 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/wp-login.php - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
404 0 2 1439 179 906
2017-02-02 18:39:12 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
403 14 0 1427 167 1203
2017-02-02 18:58:15 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/favicon.ico - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.28
83.87+Safari/537.36+OPR/42.0.2393.517+(Edition+Campaign+70) - -
www.google-docs.com 404 0 2 1439 307 15

well if I remove the '-' then entire grok pattern fails for the logs
because for missing data in the logs the char is '-'

Post by pabsensi via InfluxData
I think I found the problem. Try removing the '-' in the all the
semantic fields for example instead of %{NUMBER:sub-status:int} make it
%{NUMBER:substatus:int} and so on. Hope this works!

['Pablo Asensi' via InfluxData]

Hello Kamal,

I encountered the same problem, but could finally parse the time using the
timestamp modifier from Telegraf's logparser. Since none of the timestamp
formats included in the log parser are compatible with the IIS logs, you
have to build a custom timestamp format. It's not very clearly explained in
the docs, but to make a custom timestamp you simply have to write in the
modifier field "ts-" followed by the reference time specified in quotes. So
in this case, we wish to pass a timestamp that's in the format "YYYY/MM/DD
HH:MM:SS". The reference time is Mon Jan 2 15:04:05 -0700 MST 2006 so the
timestamp modifier should be the following:

ts-"2006/01/02 15:04:05"

Your custom pattern would end up being:


custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp:ts-"2006/01/02 15:04:05"}
%{WORD:sitename:tag} %{HOSTNAME:computername} %{IP:sip}
%{URIPROTO:csmethod:tag} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-)
%{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int}
%{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int}
%{NUMBER:timetaken:int}
'''

No timezone information is stored in the logs, so I think Influx simply
assigns it to default which is UTC. I guess you can change that in the
config files. Hope this works for you

Post by Kamal P
Hi Guys/Experts,
I am stuck in next step now based on the below email thread. I am able to
parse the Windows IIS access Logs (with the help of Pablo) and able to push
data into influx which has default time field and I am using Grafana for
the dashboard from where I could not query the timestamp which was parsed
from the IIS Logs. The issue is if it is default Apache or Nginx logs then
timestamp is parsed and updated to the time field of Influx (Using Influx
1.2.0.1) using COMMONLOGFORMAT but in case of IIS logs (CUSTOM_LOG) it does
not do that and due to which the Grafana's Time Range filter does not work
properly
IIS Log samples below
------------------------------------
2017-02-02 15:31:00 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++http://
OpenLinkProfiler.org/bot+) - - www.google.com 403 14 0 1446 286 1468
2017-02-02 15:56:04 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++http://
megaindex.com/crawler) - - www.google.com 403 14 0 1427 176 2000
2017-02-02 16:09:19 W3SVC11 server1 101.123.112.50 GET /index.html - 80 -
10.223.129.95 HTTP/1.0 Mozilla/5.0+(Macintosh;+Intel+
Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+
Chrome/39.0.2171.99+Safari/537.36 - http://www.google.com/ www.google.com
200 0 0 1578227 427 421
2017-02-02 16:42:13 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google.com 403 14 0 1446 246 2000
Grok Pattern for above IIS logs to be parsed using Telegraf 1.2
------------------------------------------------------------
-----------------------------
custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp} %{WORD:sitename:tag}
%{HOSTNAME:computername} %{IP:sip} %{URIPROTO:csmethod:tag}
%{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-) %{NUMBER:csport}
(?:%{WORD:username}|-) %{IP:clientip} %{NOTSPACE:httpversion}
%{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int}
%{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int}
%{NUMBER:timetaken:int}
'''
Influx output for the log measurement
-----------------------------------------------------------
time timestamp clientip computername cookie csbytes csmethod csport
csquery csstem host host_1 httpversion referer scbytes scstatus
scsubstatus scwin32status sip sitename timetaken useragent
2017-02-27T16:58:42.1476989Z 2017-02-02 15:31:00 W3SVC11 server1
101.123.112.50 GET /test.html - 80 - 10.223.1.192 HTTP/1.1
Mozilla/5.0+(compatible;+spbot/5.0.3;++http://OpenLinkProfiler.org/bot+)
- - www.google.com 403 14 0 1446 286 1468
Now the problem is, if you look at first two columns/tag which is *time
and timestamp in the above influx output, *they are different, ideally I
want the default time series field of measurement which is *time *to have
the timestamp values from IIS logs rather than it is picking up the time
from when the logs are parsed
So to overcome this situation, I've modified the grok patter for the
timestamp column as below
Original --- %{TIMESTAMP_ISO8601:timestamp}
Modified to -- *%{TIMESTAMP_ISO8601:time:tag}*
So I am forcing the timestamp from the IIS Logs to be parsed and updated
to the default *time *field in Influx measurement but still it does not
do that and shows the time of when the logs were parsed instead of
timestamp from the log file. Please let me know what am I missing here and
how can I force the IIS log timestamp to be default *time *in measurement
Thanks,
Kamal

perfect.. thanks man.. that helps as well..cheers!!

Another thing to take in mind: I recommend using TIMESTAMP_ISO8601
instead of DATESTAMP to parse your date in that format, since DATESTAMP has
a Month/Day/Year or Day/Month/Year format (depending on EU or US) instead
of the Year/Month/Day that the log dates are in. Cheers!

great it works..wonderful..
Thanks a lot Pablo, you made my day... have a wonderful day ahead..you
are genius... so this '-' was the issue..

No no, I mean only in the semantic fields. The semantic is the name of
%{DATESTAMP:timestamp} %{WORD:sitename} %{HOSTNAME:computername}
%{IP:sip} %{URIPROTO:csmethod} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-)
%{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}|-) %{NUMBER:scstatus}
%{NUMBER:scsubstatus} %{NUMBER:scwin32status} %{NUMBER:scbytes}
%{NUMBER:csbytes} %{NUMBER:timetaken}

Here is the sample log, try it by removing the '-', the grok pattern
will fail.. may be we need more generic grok pattern in that case
2017-02-02 15:31:00 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++
http://OpenLinkProfiler.org/bot+) - - www.google-docs.com 403 14 0
1446 286 1468
2017-02-02 15:56:04 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++
http://megaindex.com/crawler) - - www.google-docs.com 403 14 0 1427
176 2000
2017-02-02 16:09:19 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.0 Mozilla/5.0+(Macintosh;+Intel+
Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/39.0.2171.99+Safari/537.36
- http://www.google-docs.com/ www.google-docs.com 200 0 0 1578227
427 421
2017-02-02 16:42:13 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google-docs.com 403 14 0 1446 246 2000
2017-02-02 17:35:30 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 200 0 0 1578208 452
890
2017-02-02 17:35:34 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 206 0 0 311789 480 62
2017-02-02 18:39:10 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/wp-login.php - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
404 0 2 1439 179 906
2017-02-02 18:39:12 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
403 14 0 1427 167 1203
2017-02-02 18:58:15 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/favicon.ico - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.28
83.87+Safari/537.36+OPR/42.0.2393.517+(Edition+Campaign+70) - -
www.google-docs.com 404 0 2 1439 307 15

well if I remove the '-' then entire grok pattern fails for the logs
because for missing data in the logs the char is '-'

Post by pabsensi via InfluxData
I think I found the problem. Try removing the '-' in the all the
semantic fields for example instead of %{NUMBER:sub-status:int} make it
%{NUMBER:substatus:int} and so on. Hope this works!

--
Remember to include the version number!
---
You received this message because you are subscribed to a topic in the
Google Groups "InfluxData" group.
To unsubscribe from this topic, visit https://groups.google.com/d/
topic/influxdb/2QQ8T8Uc3HM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
Visit this group at https://groups.google.com/group/influxdb.
To view this discussion on the web visit https://groups.google.com/d/
msgid/influxdb/CAPFArmrQteFdeS7r1GE8fZRLqVX9Bo17PT5dAtOPTfBtNmiKAQ%40mail.
gmail.com
<https://groups.google.com/d/msgid/influxdb/CAPFArmrQteFdeS7r1GE8fZRLqVX9Bo17PT5dAtOPTfBtNmiKAQ%40mail.gmail.com?utm_medium=email&utm_source=footer>
.
For more options, visit https://groups.google.com/d/optout.

[Kamal P]

Hi Experts,

Any help please I am still struggling to parse the IIS timestamp to
Influxdb time series through Telegraf

*IIS Log timestamp sample - 2017-02-03 17:18:17*
Grok Parser used - %{TIMESTAMP_ISO8601:time:ts-rfc3339}

But any modifier I use, it says can't parse the time, please help what
should I do so that Telegraf can parse the above timestamp format to time
series in INfluxdb and store that into *time *field

2017/03/03 05:16:10 ERROR parsing 2017-02-21 13:43:56 to time layout
[2006-01-02T15:04:05Z07:00]: parsing time "2017-02-21 13:43:56" as
"2006-01-02T15:04:05Z07:00": cannot parse " 13:43:56" as "T"

Thanks,
Kamal

Post by Kamal P
Hi Guys/Experts,
I am stuck in next step now based on the below email thread. I am able to
parse the Windows IIS access Logs (with the help of Pablo) and able to push
data into influx which has default time field and I am using Grafana for
the dashboard from where I could not query the timestamp which was parsed
from the IIS Logs. The issue is if it is default Apache or Nginx logs then
timestamp is parsed and updated to the time field of Influx (Using Influx
1.2.0.1) using COMMONLOGFORMAT but in case of IIS logs (CUSTOM_LOG) it does
not do that and due to which the Grafana's Time Range filter does not work
properly
IIS Log samples below
------------------------------------
2017-02-02 15:31:00 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++http://
OpenLinkProfiler.org/bot+) - - www.google.com 403 14 0 1446 286 1468
2017-02-02 15:56:04 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++http://
megaindex.com/crawler) - - www.google.com 403 14 0 1427 176 2000
2017-02-02 16:09:19 W3SVC11 server1 101.123.112.50 GET /index.html - 80 -
10.223.129.95 HTTP/1.0 Mozilla/5.0+(Macintosh;+Intel+
Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+
Chrome/39.0.2171.99+Safari/537.36 - http://www.google.com/ www.google.com
200 0 0 1578227 427 421
2017-02-02 16:42:13 W3SVC11 server1 101.123.112.50 GET /test.html - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google.com 403 14 0 1446 246 2000
Grok Pattern for above IIS logs to be parsed using Telegraf 1.2
------------------------------------------------------------
-----------------------------
custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp} %{WORD:sitename:tag}
%{HOSTNAME:computername} %{IP:sip} %{URIPROTO:csmethod:tag}
%{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-) %{NUMBER:csport}
(?:%{WORD:username}|-) %{IP:clientip} %{NOTSPACE:httpversion}
%{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-)
(?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int}
%{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int}
%{NUMBER:timetaken:int}
'''
Influx output for the log measurement
-----------------------------------------------------------
time timestamp clientip computername cookie csbytes csmethod csport
csquery csstem host host_1 httpversion referer scbytes scstatus
scsubstatus scwin32status sip sitename timetaken useragent
2017-02-27T16:58:42.1476989Z 2017-02-02 15:31:00 W3SVC11 server1
101.123.112.50 GET /test.html - 80 - 10.223.1.192 HTTP/1.1
Mozilla/5.0+(compatible;+spbot/5.0.3;++http://OpenLinkProfiler.org/bot+)
- - www.google.com 403 14 0 1446 286 1468
Now the problem is, if you look at first two columns/tag which is *time
and timestamp in the above influx output, *they are different, ideally I
want the default time series field of measurement which is *time *to have
the timestamp values from IIS logs rather than it is picking up the time
from when the logs are parsed
So to overcome this situation, I've modified the grok patter for the
timestamp column as below
Original --- %{TIMESTAMP_ISO8601:timestamp}
Modified to -- *%{TIMESTAMP_ISO8601:time:tag}*
So I am forcing the timestamp from the IIS Logs to be parsed and updated
to the default *time *field in Influx measurement but still it does not
do that and shows the time of when the logs were parsed instead of
timestamp from the log file. Please let me know what am I missing here and
how can I force the IIS log timestamp to be default *time *in measurement
Thanks,
Kamal

perfect.. thanks man.. that helps as well..cheers!!

Another thing to take in mind: I recommend using TIMESTAMP_ISO8601
instead of DATESTAMP to parse your date in that format, since DATESTAMP has
a Month/Day/Year or Day/Month/Year format (depending on EU or US) instead
of the Year/Month/Day that the log dates are in. Cheers!

great it works..wonderful..
Thanks a lot Pablo, you made my day... have a wonderful day ahead..you
are genius... so this '-' was the issue..

No no, I mean only in the semantic fields. The semantic is the name of
%{DATESTAMP:timestamp} %{WORD:sitename} %{HOSTNAME:computername}
%{IP:sip} %{URIPROTO:csmethod} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-)
%{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}|-) %{NUMBER:scstatus}
%{NUMBER:scsubstatus} %{NUMBER:scwin32status} %{NUMBER:scbytes}
%{NUMBER:csbytes} %{NUMBER:timetaken}

Here is the sample log, try it by removing the '-', the grok pattern
will fail.. may be we need more generic grok pattern in that case
2017-02-02 15:31:00 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(compatible;+spbot/5.0.3;++
http://OpenLinkProfiler.org/bot+) - - www.google-docs.com 403 14 0
1446 286 1468
2017-02-02 15:56:04 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(compatible;+MegaIndex.ru/2.0;++
http://megaindex.com/crawler) - - www.google-docs.com 403 14 0 1427
176 2000
2017-02-02 16:09:19 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.0 Mozilla/5.0+(Macintosh;+Intel+
Mac+OS+X+10_10_1)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/39.0.2171.99+Safari/537.36
- http://www.google-docs.com/ www.google-docs.com 200 0 0 1578227
427 421
2017-02-02 16:42:13 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/45.0.2454.93+Safari/537.36
- - www.google-docs.com 403 14 0 1446 246 2000
2017-02-02 17:35:30 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 200 0 0 1578208 452
890
2017-02-02 17:35:34 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/search.html - 80 - 10.223.129.95 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.2883.87+Safari/537.36
- https://www.google.co.in/ www.google-docs.com 206 0 0 311789 480 62
2017-02-02 18:39:10 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/wp-login.php - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
404 0 2 1439 179 906
2017-02-02 18:39:12 W3SVC11 abcd-xyz-01 101.223.111.511 GET / - 80 -
10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+6.1;+W
OW64;+rv:40.0)+Gecko/20100101+Firefox/40.1 - - www.google-docs.com
403 14 0 1427 167 1203
2017-02-02 18:58:15 W3SVC11 abcd-xyz-01 101.223.111.511 GET
/favicon.ico - 80 - 10.223.1.192 HTTP/1.1 Mozilla/5.0+(Windows+NT+10.0;+
WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/55.0.28
83.87+Safari/537.36+OPR/42.0.2393.517+(Edition+Campaign+70) - -
www.google-docs.com 404 0 2 1439 307 15

well if I remove the '-' then entire grok pattern fails for the logs
because for missing data in the logs the char is '-'

Post by pabsensi via InfluxData
I think I found the problem. Try removing the '-' in the all the
semantic fields for example instead of %{NUMBER:sub-status:int} make it
%{NUMBER:substatus:int} and so on. Hope this works!

[pabsensi via InfluxData]

Hello Kamal,

You have to use a custom time layout that fits the format of your time field. Copy and paste this Grok pattern, as I explained in my previous reply and see if it works:

custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp:ts-"2006/01/02 15:04:05"} %{WORD:sitename:tag} %{HOSTNAME:computername} %{IP:sip} %{URIPROTO:csmethod:tag} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-) %{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip} %{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-) (?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-) %{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int} %{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int} %{NUMBER:timetaken:int}
'''

[Kamal P]

Hi Pablo,

Thank you, I managed to get that after a while once I sent this email.
Thanks once again for your help.

Post by 'Pablo Asensi' via InfluxData
Hello Kamal,
You have to use a custom time layout that fits the format of your time
field. Copy and paste this Grok pattern, as I explained in my previous
custom_patterns = '''
CUSTOM_LOG %{TIMESTAMP_ISO8601:timestamp:ts-"2006/01/02 15:04:05"}
%{WORD:sitename:tag} %{HOSTNAME:computername} %{IP:sip}
%{URIPROTO:csmethod:tag} %{URIPATH:csstem} (?:%{NOTSPACE:csquery}|-)
%{NUMBER:csport} (?:%{WORD:username}|-) %{IP:clientip}
%{NOTSPACE:httpversion} %{NOTSPACE:useragent} (?:%{NOTSPACE:cookie}|-)
(?:%{NOTSPACE:referer}|-) (?:%{HOSTNAME:host}:%{NUMBER:port}|%{HOSTNAME:host}|-)
%{NUMBER:scstatus:tag} %{NUMBER:scsubstatus:int}
%{NUMBER:scwin32status:int} %{NUMBER:scbytes:int} %{NUMBER:csbytes:int}
%{NUMBER:timetaken:int}
'''