Custom field to IIS inputRSS

4 replies

Last post Aug 28, 2018 04:27 PM by FrankPostle

‹ Previous Thread|Next Thread ›
  • Anonymous Anonymous

    5283 Posts

    Custom field to IIS input

    Apr 25, 2007 04:35 PM|Anonymous|LINK

    Hello,

    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.

    OLD COMMAND
    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

     

    NEW COMMAND I TRIED
    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

    IISW3C input format

  • Anonymous Anonymous

    5283 Posts

    Re: Custom field to IIS input

    Apr 27, 2007 09:45 AM|Anonymous|LINK

    I've been doing more reading but still cannot find if there is a way to do this. Is this possible?

    IISW3C input format

  • Anonymous Anonymous

    5283 Posts

    Re: Custom field to IIS input

    Apr 29, 2007 10:21 AM|Anonymous|LINK

    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.

    IISW3C input format

  • Anonymous Anonymous

    5283 Posts

    Re: Custom field to IIS input

    Jun 18, 2007 03:03 AM|Anonymous|LINK

    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,...

     

     

    IISW3C input format

  • FrankPostle FrankPostle

    1 Post

    Re: Custom field to IIS input

    Aug 28, 2018 04:27 PM|FrankPostle|LINK

    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)],
  [cs(Cookie)]
 INTO iisLogs FROM C:\inetpub\logs\LogFiles\W3SVC12\u_ex180209.log
 WHERE (EXTRACT_EXTENSION(cs-uri-stem) NOT IN ('gif';'ico';'css';'js'))"
  -i:iisw3c
  -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.

Shortcuts

‹ Previous Thread|Next Thread ›