|
Запуск из Query Analyzer'а:
truncate table inetlog
exec sp_LOG_FilePars
Процкдура sp_LOG_FilePars:
REATE PROCEDURE sp_LOG_FilePars AS
declare @Line varchar(2048 ),
@Tail varchar(2048 ),
@s1 varchar(250),
@s2 varchar(250),
@ClientHost varchar(20),
@UserName varchar(50),
@SrvcName varchar(10),
@ServerName varchar(50),
@ServerIP varchar(20),
@Operation varchar(10),
@Target varchar(250),
@Referer varchar(250)
declare @LogDate Datetime
declare @n int,
@Cnt int,
@Servicestatus int,
@BytesSent int,
@BytesRecvd int
SET XACT_ABORT ON
SET NOCOUNT ON
Delete From Load_buffer Where DataLength(Line)<=9
Declare LogData CURSOR FOR
Select Line From Load_Buffer
OPEN LogData
FETCH LogData INTO @Line
WHILE @@FETCH_STATUS = 0 Begin
If Left(@Line, 1) = '#' GoTo SkipLine
Set @Tail = @Line + ' '
Set @n = CHARINDEX(' ', @Tail)
Set @s1 = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @s2 = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @LogDate = Convert(Datetime, @s1+' '+@s2,102)
Set @n = CHARINDEX(' ', @Tail)
Set @ClientHost = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @UserName = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @SrvcName = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @ServerName = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @ServerIP = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @Operation = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @Target = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @Servicestatus = Convert(int, Left(@Tail, @n - 1))
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @BytesSent = Convert(int, Left(@Tail, @n - 1))
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @BytesRecvd = Convert(int, Left(@Tail, @n - 1))
Set @Tail = Substring(@Tail,@n+1, 2048 )
Set @n = CHARINDEX(' ', @Tail)
Set @Referer = Left(@Tail, @n - 1)
Set @Tail = Substring(@Tail,@n+1, 2048 )
--Select @ClientHost, @UserName, @SrvcName, @ServerName, @ServerIP,@Operation, @Target, @Servicestatus, @BytesSent, @BytesRecvd, @Referer
Insert Into INetLog (LogTime, ClientHost, UserName, Service, ServerName, ServerIP,
Operation, Target, ServiceStatus, BytesSent, BytesRecvd, Referer)
Values (@LogDate, @ClientHost, @UserName, @SrvcName, @ServerName, @ServerIP,
@Operation, @Target, @Servicestatus, @BytesSent, @BytesRecvd, @Referer)
SkipLine:
FETCH LogData INTO @Line
End
CLOSE LogData
DEALLOCATE LogData
SET NOCOUNT OFF
SET XACT_ABORT OFF
GO
|
