1.1 - 8.10 - ApacheLogParser Example: Default LogFormat - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

The input table, apache_logs, contains a sample of five records of apache web user logs.

 id |                                                                                                                                                                                                                       logdata                                                                                                                                                                                                                       
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | 69.236.77.51 - Frank [26/Mar/2011:09:17:31 -0700] "GET /about/careers.php HTTP/1.1" 200 5976 "http://www.bing.com/search?q=abc+data&src=ie9tr" "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)"
  2 | 168.187.7.114 - Lewis [27/Mar/2011:00:16:49 -0700] "GET / HTTP/1.0" 200 7203 "http://search.yahoo.com/search;_ylt=AtMGk4Fg.FlhWyX_ro.u0VybvZx4?p=abc&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-383-1" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2;.NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0;InfoPath.2)"
  3 | 75.36.209.106 - Patrick [20/May/2008:15:43:57 -0400] "GET / HTTP/1.1" 200 15251 "http://www.google.com/search?hl=en&q=%22abc+Data+Systems%22" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; YPC 3.2.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; MS-RTC LM 8)"
  4 | 159.41.1.23 - - [06/Jul/2010:07:19:45 -0400] "GET /public/js/common.js HTTP/1.1" 200 16711 "http://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=0&rsv_idx=1&tn=baidu&wd=abc%20data&rsv_pq=d31bd31c000dd71c&rsv_t=982dONZ4XBYXizw4wA%2BQD411WcEyn1YoJu4QSpNTQwwoTE7hgPFD9OBTObk&rsv_enter=1&rsv_sug3=11&rsv_sug1=1&rsv_sug2=0&rsv_sug7=100&inputT=3572&rsv_sug4=6596" "Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3"
  5 | 127.0.0.1 - -  [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326 "http://www.example.com/start.html" "Mozilla/4.08 [en] (Win98; I ;Nav)"
(5 rows)

SQL Call

SELECT * FROM ApacheLogParser (
  ON apache_logs
  USING
  TargetColumn ('logdata')
  LogFormat ('%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"')
  SearchInfoFlag ('true')
) AS dt;

Output

There is no output corresponding to input id=4, because .js pages are omitted by default (see ExcludeFiles in ApacheLogParser Syntax Elements).

The first row, which corresponds to input id=5, is empty in the search_engine and search_term columns because the referrer for that input row, http://www.example.com/start.html, is not a search engine. The supported search engines are Google, Bing, and Yahoo.

 remote_host   remote_log_name remote_user request_time               requested_page     final_status bytes_sent_including_header referrer                                                                                                          search_engine search_terms         request:User-Agent                                                                                                                                                
 ------------- --------------- ----------- -------------------------- ------------------ ------------ --------------------------- ----------------------------------------------------------------------------------------------------------------- ------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 127.0.0.1     -               -           2000-10-10 13:55:36.000000 /apache_pb.gif     200          2326                        http://www.example.com/start.html                                                                                 NULL          NULL                 Mozilla/4.08 [en] (Win98; I ;Nav)                                                                                                                                
 75.36.209.106 -               Patrick     2008-05-20 15:43:57.000000 /                  200          15251                       http://www.google.com/search?hl=en&q=%22Aster+Data+Systems%22                                                     google        "Aster Data Systems" Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; YPC 3.2.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; MS-RTC LM 8)                                           
 69.236.77.51  -               Frank       2011-03-26 09:17:31.000000 /about/careers.php 200          5976                        http://www.bing.com/search?q=Aster+data&src=ie9tr                                                                 bing          Aster data           Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)                                                                                                  
 168.187.7.114 -               Lewis       2011-03-27 00:16:49.000000 /                  200          7203                        http://search.yahoo.com/search;_ylt=AtMGk4Fg.FlhWyX_ro.u0VybvZx4?p=ASTER&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-383-1 yahoo         ASTER                Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2;.NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0;InfoPath.2)

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.