Custom field to IIS input RRS feed

  • Question

  • User531388329 posted


    I have been using a SELECT * statement before to put all the IIS log info into a SQL table and it works fine. I want to add 1 extra column to the SQL table though (a primary key). It wont let me use the select * statement anymore because the fields do not match. I even tried specifying the fields one by one but it will not work because there is a different amount of columns now.

    What I did was I modified the table already there with a new column (RecordID which is a auto-increment primary key). Can someone please give me advice on how I should persue this?  Keep the table with the new design and input the data differently, or somehow specify this new primary key column in the logparser statement.

    logparser.exe "SELECT * INTO tblIISLog_NEW FROM \\Hamweb001\LogFiles\W3SVC85298408\*.log" -o:SQL -server:hamdev005 -database:IISLOG_REPORTS -driver:"SQL Server" -createTableN -i:IISW3C -iCheckPoint:d:\checkpointweb.lpc>>checkpointweblog.txt


    logparser.exe "SELECT LogFilename, LogRow, date, time, c-ip, cs-username, s-sitename, s-computername, s-ip, s-port, cs-method, cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status, sc-bytes, cs-bytes, time-taken, cs-version, cs-host, cs(User-Agent), cs(Cookie), cs(Referer), s-event, s-process-type, s-user-time, s-kernel-time, s-page-faults, s-total-procs, s-active-procs, s-stopped-procs INTO tblIISLog_NEW FROM \\Hamweb001\LogFiles\W3SVC85298408\*.log" -o:SQL -server:hamdev005 -database:IISLOG_REPORTS -driver:"SQL Server" -createTableN -i:IISW3C -iCheckPoint:d:\checkpointweb.lpc>>checkpointweblog.txt


    Errors I get now:

    1) Number of columns in table 'tblIISLog_NEW' (33) is different than number of columns in SELECT clause (32)

    2) SQL table column "RecordID" data type is not compatible with SELECT clause item "LogFilename" (type STRING)


    Thank you

    Tuesday, August 21, 2007 2:21 AM

All replies

  • User531388329 posted
    I've been doing more reading but still cannot find if there is a way to do this. Is this possible?
    Friday, April 27, 2007 9:45 AM
  • User531388329 posted
    I don't think this is something you could easily do straight from LP. If you can't re-import the data, you probably need to look at using a different tool to update the table. As I just mentioned to someone else, an ETL tool like Kettle (http://kettle.pentaho.org) might help.
    Sunday, April 29, 2007 10:21 AM
  • User531388329 posted

    in qwery-
    select 1 as id, ... from iisw3c_file

    and set id in your real sql table as int, identity - yes

    id's in your real sql table will be 1,2,3,4,5,6,...



    Monday, June 18, 2007 3:03 AM
  • User487007144 posted

    I create the table I use as follows (note it has a primary key):

    CREATE TABLE [dbo].[iisLogs](
    	[sComputername] [varchar](255) NOT NULL,
    	[sSitename] [varchar](255) NOT NULL,
    	[LogFilename] [varchar](255) NOT NULL,
    	[LogRow] [int] NOT NULL,
    	[date] datetime NULL,
    	[cIp] [varchar](255) NULL,
    	[csUsername] [varchar](255) NULL,
    	[sIp] [varchar](255) NULL,
    	[sPort] [int] NULL,
    	[csMethod] [varchar](255) NULL,
    	[csUriStem] [varchar](255) NULL,
    	[csUriQuery] [varchar](255) NULL,
    	[scStatus] [int] NULL,
    	[scSubstatus] [int] NULL,
    	[scWin32Status] [int] NULL,
    	[scBytes] [int] NULL,
    	[csBytes] [int] NULL,
    	[timeTaken] [int] NULL,
    	[csVersion] [varchar](255) NULL,
    	[csHost] [varchar](255) NULL,
    	[csUserAgent] [varchar](255) NULL,
    	[csCookie] [varchar](4096) NULL,
    	CONSTRAINT PK_iisLogs PRIMARY KEY ( [sComputername], [sSitename], [LogFilename], [LogRow] )

    The command I use to populate this database look like this:

    "SELECT '%ComputerName%', [s-sitename], EXTRACT_FILENAME([LogFilename]), [LogRow],
      to_timestamp(date,time), [c-ip], [cs-username], [s-ip], [s-port], [cs-method],
      [cs-uri-stem], [cs-uri-query], [sc-status], [sc-substatus], [sc-win32-status],
      [sc-bytes], [cs-bytes], [time-taken], [cs-version], [cs-host], [cs(User-Agent)],
     INTO iisLogs FROM C:\inetpub\logs\LogFiles\W3SVC12\u_ex180209.log
     WHERE (EXTRACT_EXTENSION(cs-uri-stem) NOT IN ('gif';'ico';'css';'js'))"
      -o:SQL -server:<database server> -database:<database name> -transactionRowCount:10000

    The biggest thing that I have found is that with IIS 8.5 and Custom Fields the iisw3c input format no longer works.

    Tuesday, August 28, 2018 4:27 PM