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
On Mon, Feb 27, 2017 at 5:24 PM, Kamal P <[email protected]> wrote:
> 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 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/CAPFArmo0qdWzaqCjWsaXX3h87C27azpyFh6gv_4h9KA8_vQtbQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
