How to Import IIS Log to PostgreSQL

If you ever had a need to dump and analyze IIS logs from a database, this post will show you how to do it in PostgreSQL RDBMS.

The assumption is that you’re already familiar with the default installation and setup of PostgreSQL, if not please check the manual link at the bottom of this post. We will use default settings.

Steps to Create IIS Log Table and Import Log File to PostgreSQL

1. Create a database called mydb.

2. We need to prepare a table that matches the fields from IIS log, so we need to create a table called iislog by running the following SQL:

   CREATE TABLE public.iislog
   (
       date date,
       "time" time without time zone,
       sip character varying(48) COLLATE pg_catalog."default",
       csmethod character varying(8) COLLATE pg_catalog."default",
       csuristem character varying(255) COLLATE pg_catalog."default",
       csuriquery character varying(2048) COLLATE pg_catalog."default",
       sport character varying(4) COLLATE pg_catalog."default",
       susername character varying(256) COLLATE pg_catalog."default",
       cip character varying(48) COLLATE pg_catalog."default",
       csuseragent character varying(1024) COLLATE pg_catalog."default",
       csreferer character varying(4096) COLLATE pg_catalog."default",
       scstatus integer,
       scsubstatus integer,
       scwin32status bigint,
       timetaken integer
   )
   WITH (
       OIDS = FALSE
   )
   TABLESPACE pg_default;
   
   ALTER TABLE public.iislog
       OWNER to postgres;
   

3. Go to the location of where the IIS log files are stored. By default, the location is in the following folder:

   %SystemDrive%\inetpub\logs\LogFiles
   

   However, as the best practice, most enterprise applications will have a different location on a different drive for log. In this example, we’ll use C:\Temp\IISLogs\ folder.

4. We will certainly not going to import each log file one-by-one to the database, so the best thing to do before we do the import is to combine all the log files we want to analyze. Just for the sake of simplicity for this demonstration, we want to include all log files within this folder and combine them into one big log file. We can do this by opening either a Command Prompt or PowerShell Windows. It doesn’t matter which one you prefer, it will work the same way.

5. In the Command Prompt or PowerShell Window, make sure you’re in the IIS log folder and type in the following command:

   Command only

   type *.log > filename.log
   

   Command with prompt (as in this example)

   C:\Temp\IISLog> type *.log > combinedLogs.log
   

   *The prompt is included to show that you need to be in your IIS log folder to execute this operation.

6. Now we need to massage the combined log file a bit before we can import it within one operation.

   If you open the new log file (combinedLogs.log) with a text editor, you will see all the headers from every single log file were included as well and it looks something similar to this:

   #Software: Microsoft Internet Information Services 10.0
   #Version: 1.0
   #Date: 2018-08-02 15:08:58
   #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
   

7. By using a text editor such as Sublime Text, we can clean up this log file by using Regex Find and Replace.

   From the top menu, go to Find and Replace… (CTRL-H in Windows) and make sure the Regex option is activated and type (or copy and paste) the following search string in the Find: box:

   #Software: Microsoft Internet Information Services 10.0\n#Version: 1.0\n#Date: [0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\n#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs\(User-Agent\) cs\(Referer\) sc-status sc-substatus sc-win32-status time-taken\n
   

   

8. Press Replace All button to remove all instances of those headers and re-save the file.

9. Now we will import this log file by using COPY command.

   Go back to PostgreSQL shell and type in the following:

   COPY public.iislog FROM 'c:\temp\iislogs\combinedLogs.log' DELIMITER ' ';
   

   *If you use a different table name and location of IIS log, make sure you change it to match yours.

That’s all there is, now you should be able to query the log and slice and dice the data however you need it.

Caveat

Important: If you have a rather big combined log file (even only a few MB in size), make sure you do the import from the command line. Do not use pgAdmin web interface as it may not be able to handle the load.

Further Reading

PostgreSQL Manuals

Download

PostgreSQL