如何在条件下将特定数据从日志文件插入到sql表中 [英] how to insert specific data from log file to sql table on condition

问题描述

大家好，

我正在c#中创建一个控制台应用程序，它将从日志文件中获取信息，并根据条件(例如所花费的时间，cs_uri_stem等)将其插入到sql表中，但是我不知道如何读取日志文件，而该日志文件反过来会填充我的数据表指定条件.

请帮助我

谢谢
Vivek T

Hello everyone there,

i am creating an console application in c# which will fetch information from log file and insert it in sql table based on condition (like time taken, cs_uri_stem and so on) but i dont know how to read log file which in return fills my datatable for specified condition.

please help me in this

Thank you
Vivek T

推荐答案

Vivek，

如果您尝试从类似IIS日志的日志文件中填充SQL数据库表，则类似Log Analyzer(来自Microsoft)的工具将为您提供帮助.

-Milind

Vivek,

If you are trying to fill SQL database table from log file like that of IIS log, a tool like Log Analyser (from microsoft) will help you.

-Milind

大家好，
我已经在SQL Server中使用批量插入语句以简单的方式解决了该问题

在W3C扩展日志记录格式中，这些字段在某种程度上是不言自明的:数据和时间就是它们看上去的样子:

[c-ip]是客户端的IP地址
[cs-method]是已满足请求的HTTP方法
[cs-uri-stem]是已请求的文件
[cs-uri-query]是作为正在记录的请求的一部分发送的查询字符串
[sc-status]是服务器返回的状态码
[sc-bytes]是已返回给用户的字节数
[花费的时间]是服务器完成请求处理所用的时间，以毫秒为单位.
[cs(Cookie)]是请求中的任何cookie数据或持久性数据
[cs(Referer)]是用户访问的上一个站点的URL.
对于W3C扩展日志记录格式，可以选择许多其他字段.有关更多详细信息，请参见本文:


注意:W3C扩展日志记录格式是唯一可用于确切指定要记录的字段的日志文件格式.



以下是以W3C扩展日志记录格式写入日志文件的标头示例.

#Software:Microsoft Internet信息服务7.5
#Version:1.0
#Date:2012-10-16 00:10:13
#fields:日期时间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花费的时间
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



您可以使用SQL Server企业管理器为您的日志文件创建表


创建表[dbo].[Weblog](
[日期] [日期] NULL，
[时间] [时间] NULL，
[s-ip] [varchar](50)NULL，
[cs-方法] [varchar](50)NULL，
[cs-uri-stem] [varchar](255)NULL，
[cs-uri-query] [varchar](2048)NULL，
[s-port] [varchar](2048)null，
[cs-用户名] [varchar](2048)空，
[c-ip] [varchar](50)NULL，
[cs(User-Agent)] [varchar](255)NULL，
[sc状态] [int] NULL，
[sc-substatus] [int] NULL，
[sc-win32-status] [int] NULL，
[耗时] [int] NULL
)

在上面我已经排除了您可能使用的某些领域，因为在我的日志文件领域中，例如sc-bytes，cs(Cookie)，cs(Referer)或可能更多


您可以根据查询中的内容进行管理



现在可能会批量插入您想要的所有日志文件

批量插入[dbo].[formatWeblog]
从''C:\ inetpub \ logs \ LogFiles \ W3SVC1 \ W3SVC5 \ u_ex120825.log''
WITH(
FIELDTERMINATOR =''''，
ROWTERMINATOR =''\ n''

)



如果要根据需要过滤记录，可以使用诸如
之类的查询
选择不同的[cs-uri-stem]，[花费时间]
来自[formatWeblog]
其中[耗时]> (从[formatWeblog]中选择avg([time-taken]))按[time-taken] desc
顺序
所有这些都将通过控制台实现，方法是使功能满足我在帖子中给出的要求


问候
Vivek T

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

这篇关于如何在条件下将特定数据从日志文件插入到sql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！