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
2017-02-27 18:24 GMT+01:00 Kamal P <[email protected]>:
> 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
>
>
>
>
> On Thu, Feb 23, 2017 at 2:52 PM, Kamal P <[email protected]> wrote:
>
>> perfect.. thanks man.. that helps as well..cheers!!
>>
>> On Thu, Feb 23, 2017 at 2:49 PM, Pablo Asensi <[email protected]>
>> wrote:
>>
>>> 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!
>>>
>>> 2017-02-23 15:45 GMT+01:00 Kamal P <[email protected]>:
>>>
>>>> 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..
>>>>
>>>> On Thu, Feb 23, 2017 at 2:41 PM, Pablo Asensi <[email protected]>
>>>> wrote:
>>>>
>>>>> No no, I mean only in the semantic fields. The semantic is the name of
>>>>> the field where the parsed data will be stored. Try this pattern:
>>>>>
>>>>> %{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}
>>>>>
>>>>> 2017-02-23 15:28 GMT+01:00 Kamal P <[email protected]>:
>>>>>
>>>>>> 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
>>>>>>
>>>>>>
>>>>>> On Thu, Feb 23, 2017 at 2:23 PM, Kamal P <[email protected]>
>>>>>> wrote:
>>>>>>
>>>>>>> well if I remove the '-' then entire grok pattern fails for the logs
>>>>>>> because for missing data in the logs the char is '-'
>>>>>>>
>>>>>>> On Thu, Feb 23, 2017 at 1:59 PM, <[email protected]> wrote:
>>>>>>>
>>>>>>>> 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
> [email protected].
> To post to this group, send email to [email protected].
> 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.
>
--
Remember to include the version number!
---
You received this message because you are subscribed to the Google Groups
"InfluxData" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
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/CAAOTv_jyjpff65TrVXt8aQieQhihZmVGUKxrcgfWzFwY%2B%3DsV5w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
