how to insert specific data from log file to sql table on condition

hello everyone,
i have resolved that in simple way using bulk insert statement in sql server

In W3C Extended Logging format the fields are somewhat self explanatory: data and time are just what they seem:

[c-ip] is the IP address of the client
[cs-method] is the HTTP method for the request that was met
[cs-uri-stem] is the document that has been requested
[cs-uri-query] is the query string that was sent as part of the request being logged
[sc-status] is the status code returned by the server
[sc-bytes] is the number of bytes that have been returned to the user
[time-taken] is the time in milliseconds that it took for the server to complete the processing of the request
[cs(Cookie)] is any cookie data or persistent data in the request
[cs(Referer)] is the URL of the previous site visited by the user.
For the W3C Extended Logging format, there are a number of additional fields that can be chosen; for more details see this article:


Note: The W3C Extended Logging format is the only log file format you can use to specify exactly which fields you want to log.



Below is an example of the headers that are written to a log file in W3C Extended Logging format.

#Software: Microsoft Internet Information Services 7.5
#Version: 1.0
#Date: 2012-10-16 00:10:13
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken
2012-10-16 00:10:13 10.3.100.26 GET / - 80 - 10.3.100.3 Mozilla/5.0+(Windows+NT+6.1)+AppleWebKit/537.4+(KHTML,+like+Gecko)+Chrome/22.0.1229.94+Safari/537.4 200 0 0 202
2012-10-16 00:10:13 10.3.100.26 GET /css/JRTStyles.css - 80 - 10.3.100.3 Mozilla/5.0+(Windows+NT+6.1)+AppleWebKit/537.4+(KHTML,+like+Gecko)+Chrome/22.0.1229.94+Safari/537.4 304 0 0 202



You could use SQL Server Enterprise Manager to create the table for your log file


CREATE TABLE [dbo].[Weblog] (
[date] [date] NULL,
[time] [time] NULL ,
[s-ip] [varchar] (50) NULL ,
[cs-method] [varchar] (50) NULL ,
[cs-uri-stem] [varchar] (255) NULL ,
[cs-uri-query] [varchar] (2048) NULL ,
[s-port] [varchar](2048) null,
[cs-username] [varchar](2048) null,
[c-ip] [varchar] (50) NULL ,
[cs(User-Agent)] [varchar] (255) NULL ,
[sc-status] [int] NULL ,
[sc-substatus] [int] NULL ,
[sc-win32-status] [int] NULL ,
[time-taken] [int] NULL
)

in above i have excluded some of the feild which you may use because in my log file feild like sc-bytes,cs(Cookie),cs(Referer) or may be some more


you can manage in accordance to that in query



now may may bulk insert all the log file which you want

BULK INSERT [dbo].[formatWeblog]
FROM 'C:\inetpub\logs\LogFiles\W3SVC1\W3SVC5\u_ex120825.log'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'

)



further more if you want to filter records as per your requirement you can use some query like

Select distinct [cs-uri-stem], [time-taken]
From [formatWeblog]
Where [time-taken] > (select avg([time-taken]) From [formatWeblog]) order by [time-taken] desc

all this will be done through console by making function to meet the requirement which i will be giving in my post


Regards
Vivek T