Import IIS logs into the database

1. Build a table

CREATE TABLE [dbo].[inetlog0828](    [Date] [Date] NULL,    [ Time] [ Time](7)NULL,    [ServerIP] [nvarchar]( -)NULL,    [Method] [nvarchar]( -)NULL,    [URL] [nvarchar](4000)NULL,    [Query] [nvarchar](4000)NULL,    [Port] [int] NULL,    [username] [nvarchar]( -)NULL,    [IP] [nvarchar]( -)NULL,    [useragent] [nvarchar](655)NULL,    [Scstatus] [int] NULL,    [Scsubstatus] [int] NULL,    [Scwin32status] [int] NULL,    [Timetaken] [int] NULL)  on [PRIMARY]

2. Import IIS Logs

   BULK INSERTinetlog0828 from 'F:\Desktop\u_ex15082814.log'     with(FieldTerminator=' ', Rowterminator='\ n', CODEPAGE='RAW', FirstRow=5, DataFileType='Widechar')   

3. Enquiry

Select [IP],COUNT([IP]) b frominetlog0828Group  by [IP] Order  byBdescSelect [IP],[URL],Count([IP]) b frominetlog0828Group  by [IP],[URL] Order  byBdescSelect [Scstatus],COUNT(1) b frominetlog0828Group  by [Scstatus] Order  byBdesc

4.bulk Insert Command Detailed

=============================================================   BULK INSERT       [database_name. [Schema_name].|Schema_name. ][table_name | view_name]           from 'data_file'         [with ([[,]BatchSize=Batch_size]--batchsize directives to set the number of records that can be inserted into a table in a single transaction   [ [ , ]Check_constraints]--specifies that all constraints on the target table or view must be checked during a bulk-import operation.   Without the check_constraints option, all CHECK and FOREIGN KEY constraints will be ignored, and the table's constraints will be marked as untrusted after this operation.    [ [ , ]CODEPAGE={'ACP' | 'OEM' | 'RAW' | 'Code_page'} ]--Specify the code page for the data in the data file   [ [ , ]DataFileType=          { 'Char' | 'native'| 'Widechar' | 'widenative'} ]--Specifies that BULK INSERT performs an import operation using the specified data file type value.    [ [ , ]FieldTerminator= 'Field_terminator']--identify symbols that delimit content   [ [ , ]FirstRow=First_row]--Specifies the line number of the first row to load. The default value is the first row in the specified data file   [ [ , ]Fire_triggers]--whether to start the trigger   [ [ , ]FormatFile= 'Format_file_path' ]       [ [ , ]KeepIdentity]--specifies that the identity values in the import data file are used to identify the column   [ [ , ]Keepnulls]--specifies that empty columns should retain a null value during the bulk-import operation without inserting any default values for the column   [ [ , ]Kilobytes_per_batch=Kilobytes_per_batch][ [ , ]LastRow=Last_row]--Specifies the line number of the last row to load   [ [ , ]Maxerrors=Max_errors]--Specifies the maximum number of syntax errors that are allowed to occur in the data, and the bulk-import operation is canceled when the number is exceeded.    [ [ , ] ORDER( {column [ASC | DESC]}[,... N]) ]--specify how data in the data file is sorted   [ [ , ]Rows_per_batch=Rows_per_batch][ [ , ]Rowterminator= 'Row_terminator']--symbols that identify delimited lines   [ [ , ]TABLOCK]--specifies that a table-level lock is obtained for the duration of the bulk-import operation   [ [ , ]ErrorFile= 'file_name']--specifies a file that is used to collect rows that are malformed and cannot be converted to an OLE DB rowset. )]

There are not perfect places, and then perfect ...

Import IIS logs into the database