ETL Process for the Web Server Log Import DTS Task
The Web server log import DTS task imports Web log file data generated by your Web site into the Data Warehouse.
The configuration of your application and Web servers will affect the log file data imported during the Web server log import DTS task. Because log files can contain information for more than one site you may need to parse the log files on a per site or virtual server level. Therefore, each hit in the log file must be associated with a particular virtual server. The Web server log import task can be configured to filter out the appropriate virtual servers. The virtual server is identified by three properties: port number, IP address, and virtual server key. Because the secure port number (port 443) and the non-secure port (port 80) both effect the virtual server key, they are seen as two different virtual servers, and the Web server log import exclusions must be set up appropriately to include both virtual servers in a Web log import. For more information on filtering log files for the log import, see Setting Web Log File Exclusions.
Notes
- Not all lines of the log file will be imported to the Request table because many lines in the log file are caused by hits, not actual requests. Only requests are imported to the Request table. A series of filters is used to distinguish hits from requests in the log file. For more information about selecting information to exclude from the Web server log import to the Request table, see Setting Web Log File Exclusions.
- The time overlap setting for the Web server log import DTS task will effect the requests imported to the Request table at the beginning and end of a log file. By default, the Web server log import DTS task will discard requests that overlap another log file by 30 minutes. For information about changing the time overlap settings, see Setting Time Overlap and Log File Rotation Properties.
The following tables list the columns extracted from the W3C log file, the transformation made, and the tables the data is saved to in the Data Warehouse.
Source columns from the W3C log file | Transformation | Target columns from the Request table in the Data Warehouse |
---|---|---|
|
Generated
Generated by the log import application. Incremented for each request line of a log file that gets stored. This is unique across different imports. |
|
|
Copy Column
Contains the entire query string from the log file. |
|
|
Copy Column |
|
|
Copy Column
Client to server bytes. |
|
|
Copy Column
Server to client bytes. |
|
|
Generated
This column is generated within the log import application as a unique identifier. |
|
|
Concatenation
Convert to local time. |
|
|
Generated
This is the key member of the SiteURL class. The OLE DB provider will resolve this to SiteURLID in the physical SiteURL table. |
|
|
Generated
Determines if this entry is considered a Request (versus a Hit). This can be set to false when the hit is filtered. The most common filtered hit is when an image is requested and it has a Commerce Event attached to it. |
|
|
Copy Column |
|
|
Middle of String
Takes only the domain portion of the URI. |
|
|
Generated
The user key is the first 8 bytes of an MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Copy Column |
|
|
Copy Column |
|
|
Generated
Log import will match a log line to a configured server binding (including the port number (s-port) and IP address (s-ip)). If the server binding is classified as being secure, this column will be set to true. For more information, see Configuring an Application. |
|
|
Generated
GET = 1, POST=2, HEAD=3 |
|
|
Copy Column |
|
|
Generated
The first request of a visit detected within the log import is 1, and successively gets incremented for each subsequent request in the visit. |
|
|
Generated
If log import detects there is no Commerce Event query string on this line, this will be set to false. |
|
|
Generated
This is incremented for each instance of a log import. |
|
|
Middle of String
Takes only the first level of the URI, if available. |
|
|
Middle of String
Takes only the second level of the URI, if available. |
|
|
Middle of String
Takes only the third level of the URI, if available. |
|
|
Middle of String
Takes only the fourth level of the URI, if available. |
|
|
Middle of String
Takes only the fifth level of the URI, if available. |
|
|
Middle of String
Takes only the sixth level of the URI, if available. |
|
|
Copy Column |
|
|
Generated
The UserId is the first 8 bytes of an MD4 hash of whatever is used as the user ID. What is used as the user ID depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Copy Column
MD4 Hash |
|
|
Generated
Used for check pointing support. |
|
|
Copy Column
MD4 Hash |
|
|
Middle of String
Only the matched cookie is extracted. The shipping schema does not physically store this property, although the schema can be logically extended to store it. |
|
Writing to the Visit Table
A visit begins when the referrer is not internal to the site. For example, if a user hits your site (<www.microsoft.com>), then clicks a link on your site that takes them to a different site (<www.msn.com>), and then returns to your site (<www.microsoft.com>), the Visit table will log two individual visits. The following table shows an example of what the log file for this user might resemble.
Time (in minutes) | UserName | URI | Referrer |
---|---|---|---|
0 | A | /default.asp | - |
1 | A | /foo.asp | <https://www.microsoft.com/default.asp> |
5 | A | /default.asp | <http://www.msn.com> |
If the definition of a visit were based on time alone, this would be one visit. However, the Data Warehouse knows that the user left the site and came back by virtue of the referrer being external to the site. A substring match against the SiteURL table determines if the referrer is internal. The referrer is checked to be internal using a substring match against the SiteURL table.
Note
- Some visits recorded in the Visit table may be open visits. For more information about changing log file rotation settings and overlap settings of your log files, see Setting Time Overlap and Log File Rotation Properties.
Source columns from the W3C log file | Transformation | Target columns from the Visit table in the Data Warehouse |
---|---|---|
|
Generated
This column is generated within the log import application as a unique identifier. This is a foreign key to the Visit table. It is also a foreign key to the VisitInfo table; however, the VisitInfo table is only inserted when the user's visit is complete. |
|
|
Concatenation
This is the timestamp of the first request in the visit. Convert to Local Time. |
|
|
Copy Column
Includes the query string. |
|
|
Takes only the domain portion of the URI. |
|
|
Generated
The user key is the first 8 bytes of an MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
||
|
Copy Column |
|
|
Generated
This is incremented for each instance of a log import. |
|
|
Middle of String |
|
|
Generated
MD4 Hash |
|
|
||
|
Copy Column |
|
|
Generated
If the user has a cookie, this is set to True. |
|
|
Generated
Used for check pointing support. |
|
|
Takes only the matched cookies. This property is not physically stored. User can logically extend to store. |
|
The VisitInfo table represents closed user visits. This class is written from log import when the visit has closed. A visit is closed when an external referrer was encountered or a visit timeout occurred.
Source columns from the W3C log file | Transformation | Target columns from the VisitInfo table in the Data Warehouse |
---|---|---|
|
Generated |
|
|
Generated |
|
|
Generated |
|
|
Generated |
|
|
Generated
This column is generated within the log import application as a unique identifier. This is a foreign key to the Visit table. It is also a foreign key to the VisitInfo table; however, the VisitInfo table is only inserted when the user's visit is complete. |
|
|
Generated
This is incremented for each instance of a log import. |
|
|
Generated
The user key is the first 8 bytes of an MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Copy Column |
|
|
||
|
||
|
||
|
||
|
||
|
||
|
Generated
The user key is the first 8 bytes of an MD4 hash of whatever is used as the user ID. What is used as the user ID depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Generated
Only the domain portion of the URI is loaded. |
|
|
Generated
Used for check pointing support. This is a foreign key to the LogImportSubTask table. |
|
Source columns from the W3C log file | Transformation | Target columns from the TaskHistory table in the Data Warehouse |
---|---|---|
|
Generated
This is incremented for each instance of a DTS import task. |
|
|
Generated
This identifies which import task was used. |
|
|
Generated
Result of the import. |
|
|
Generated
Physical start time of the import. |
|
|
Generated
Physical end time of the import. |
|
|
Generated
Description of the import task. |
|
|
Generated
Used for check pointing support for the Web log import DTS task. |
|
|
Generated
Used for restart for the Web log import DTS task. |
|
The LogImportSubTask class is used internally within the Web log import to track checkpoints for the restart feature.
Source columns from the W3C log file | Transformation | Target columns from the LogImportSubTask table in the Data Warehouse |
---|---|---|
|
Generated
Used for check pointing support. |
|
|
Generated
Incremented for each instance of a log import. |
|
|
Generated
A foreign key to the Site table. |
|
|
Generated
Server group name for which you are importing. There is a unique ServerGroup for each virtual server (for IIS, a distinct log file). |
|
|
Generated
A log file name within the import. |
|
|
Generated
The offset within the log file where the checkpoint occurred. |
|
|
Generated
The offset within the log file where the checkpoint occurred. |
|
|
Generated
The W3C fields directive at the time of the checkpoint. |
|
|
Generated
Current log file date and time when the checkpoint occurred. |
|
The OpenUserVisit class represents users that have active visits at the time of the end of a log import. To modify how open user visits are handled by the Web server log import DTS task you must adjust the log file rotation settings. For more information about changing log file rotation settings, see Setting Time Overlap and Log File Rotation Properties.
Source columns from the W3C log file | Transformation | Target columns from the OpenUserVisit table in the Data Warehouse |
---|---|---|
|
Generated
The count of requests in this current open visit. |
|
|
Generated |
|
|
Generated |
|
|
Generated
Generated within the log import application as a unique identifier. |
|
|
Generated
The user key is the first 8 bytes of an MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Generated
This is incremented for each instance of a log import. |
|
|
Copy Column |
|
|
Generated
Foreign key to the request table. The first request in this visit. |
|
|
Generated
Foreign key to the request table. The second request in this visit. |
|
|
Generated
Foreign key to the request table. The last request in this visit. |
|
|
Generated
Foreign key to the URI table. The first URI in this visit. |
|
|
Generated
Foreign key to the URI table. The second URI in this visit. |
|
|
Generated
Foreign key to the URI table. The last URI in this visit. |
|
|
Generated
The user ID is the first 8 bytes of an MD4 hash of whatever is used as the user key. What is used as the user ID depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address and browser agent string. |
|
|
Middle of String
Only the domain portion of the URI is loaded. |
|
|
Generated
Used for check pointing support. |
|
Source columns from the W3C log file | Transformation | Target columns from the SiteSummary table in the Data Warehouse |
---|---|---|
|
Generated
This is calculated during log import. All valid hits the parser was able to successfully parse from the log file. |
|
|
Generated
Total number of hits that were not filtered out by exclude criteria. |
|
|
Generated
Total number of visits (open and closed) that were encountered. |
|
|
Generated
Number of hits that were excluded by the server exclude criteria. |
|
|
Generated
Number of hits that were excluded by not being able to match a configured site. If this number is high, check your application configuration. For more information about configuring your application, see Configuring an Application. |
|
|
Generated
Number of hits that were excluded by the excluded host criteria. |
|
|
Generated
Total number of hits that were excluded by all criteria. |
|
|
Generated
Number of hits that were excluded by the crawler exclude criteria. |
|
|
Generated
Physical start time of the import. |
|
|
Generated
Physical end time of the import. |
|
Source columns from the W3C log file | Transformation | Target columns from the LevelDir table in the Data Warehouse |
---|---|---|
|
Generated
First 8-bytes of MD4 Hash |
|
|
Generated
First 8-bytes of MD4 Hash |
|
|
Generated
First 8-bytes of MD4 Hash |
|
|
Generated
First 8-bytes of MD4 Hash |
|
|
Generated
First 8-bytes of MD4 Hash |
|
|
Generated
First 8-bytes of MD4 Hash |
|
Source columns from the W3C log file | Transformation | Target columns from the PathInfo table in the Data Warehouse |
---|---|---|
|
Generated |
|
|
||
|
||
|
Generated
This is incremented for each instance of a log import. |
|
Source columns from the W3C log file | Transformation | Target columns from the LogUser table in the Data Warehouse |
---|---|---|
|
Generated
The user key is the first 8 bytes of a MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address plus browser agent string. |
|
|
||
|
||
|
Copy Column |
|
|
Generated
If the user has a cookie, this is set to True. |
|
|
Copy Column |
|
|
Generated
The user key is the first 8 bytes of a MD4 hash of whatever is used as the user key. What is used as the user key depends on what is in the log file, it can be the UPM query string (cookieless), UPM cookie, user configured cookie, user name, or client IP address plus browser agent string. |
|
Source columns from the W3C log file | Transformation | Target columns from the URI table in the Data Warehouse |
---|---|---|
|
Copy Column |
|
|
Generated
This is the home page URL that was matched during log import. |
|
|
Middle of String
Takes only the first level of the URI, if available. |
|
|
Middle of String
Takes only the second level of the URI (if one exists). |
|
|
Middle of String
Takes only the third level of the URI (if one exists). |
|
|
Middle of String
Takes only the fourth level of the URI (if one exists). |
|
|
Middle of String
Takes only the fifth level of the URI (if one exists). |
|
|
Middle of String
Takes only the sixth level of the URI (if one exists). |
|
|
Copy Column
MD4 Hash |
|
See Also
Configuring the Default Web Site Properties for Import
Configuring the Web Server Log Import DTS Task
Running the Web Server Log Import DTS Task
Running the Web Server Log Import DTS Task for a Large Site
Scripting the Web Server Log Import DTS Task
Workflow for Running DTS Tasks
Copyright © 2005 Microsoft Corporation.
All rights reserved.