IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
You are here: Home / How To / How to Import IIS Log to PostgreSQL

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
    

    Remove IIS Log headers using Regex Find and Replace

  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

March 20, 2019 Filed Under: How To Tagged With: IIS, Internet Information Services, PostgreSQL, SQL

Comments

  1. Michael Houston says

    April 8, 2019 at 7:39 am

    Dart Dallas Area Rapid Transit has the ASP 500 error at Richardson, Texas Arapaho Center Station refusing credit card.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • WordPress
  • Writing

Recent Posts

  • F#: Seq, List, Array, Map, Set. Which One to Use?
  • How to Get Table Definition in SQL Server
  • Why You Need to Use a VPN
  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
WP Engine Hassle-Free Managed WordPress Hosting

Recent Posts

  • F#: Seq, List, Array, Map, Set. Which One to Use?
  • How to Get Table Definition in SQL Server
  • Why You Need to Use a VPN
  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
  • RSS

Tags

.NET Core Access Adobe AdSense Amazon ASP.NET Cdonts Dll Classic ASP Code Editor Connect-It Copywriting ETL FSharp Genesis Framework Git Google HP Asset Manager HTML HTML5 Hugo IIS Information Security Internet Internet Information Services iOS Linux macOS Microsoft Microsoft SQL Server MVC PHP Simple Mail Transfer Protocol Smtp Server Social Media SQL SQL Server SSIS SSMS SSRS VPN Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2021 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure