Use PowerShell to Collect, Store, and Parse IIS Log Data

Use PowerShell to Collect, Store, and Parse IIS Log Data

  • Comments 3
  • Likes

Summary: Guest blogger, Microsoft PFE Chris Weaver, shows how to use Windows PowerShell to collect, store, and parse IIS log data.

Microsoft Scripting Guy, Ed Wilson, is here. Today we have back with us Chris Weaver.

Photo of Chris Weaver

I have been working at Microsoft since late 2008, during that time I have been an engineer within CSS, a SharePoint 2010 TAP, and most recently I have become a dedicated premier field engineer working with several of our premier customers to support their SharePoint infrastructure. I have been using Windows PowerShell for the last two years to simplify the administration and troubleshooting of SharePoint for my customers. I enjoy camping with my family and kite surfing in my spare time. (Yeah, right who has any of that...)

Blog: Wondering Mind (about issues with SharePoint and its supporting infrastructure)

Raise your hands if you recently tried to parse through your IIS logs to get an answer. Did you find it easy? Did you still have the correct log files? One of my customers recently brought this problem to me saying that they were not happy with any of the current methods and wondered if I could make something to work with Windows PowerShell.

They wanted to be able to remove IIS logs from their web front ends when they needed to and still maintain a long-term repository of this rich and valuable data. They had already centralized the IIS logs into one folder, which had the following structure of a parent folder for the repository and then a folder for each web application that contained files from the web front ends server.

 Image of folder

With the file collection already solved for me, I started on the script. I realized there where a few things I would have to accomplish: 

  • Create database and table structures.
  • Extract data from the files. This is very simple because the IIS logs were Tab delimited with each entry on its own line. 
  • Clean-up the data.
  • Import the data into SQL Server.

It all turned out to be a lot simpler than I thought it would be. I started off by writing functions to do the following: 

Create a database

This function uses the Smo.Database class to get all the databases, and then it enumerates all the databases on SQL Server. It compares the database name to the one provided, and if it finds no match, I create the database. In either case, I return the database object.

function Create_Database

{

param($SQLSvr, [string]$DatabaseName, [string]$DBServer)

 

foreach($db in $SQLSvr.Databases) # Check to see if our Database exists

{

if($db.Name -eq $DatabaseName)

{

return $db

}

}

$db = New-Object Microsoft.SqlServer.Management.Smo.Database($SQLSvr, $DatabaseName)

$db.Create()

return $db

}

Create a table

By using the StringCollection class, I add a SQL Create Table statement to a string and then pass the string to my Execute Statements function. In that statement, I follow the article 296085 in the Microsoft Knowledge Base to create all the correct columns. Be aware that if you want to change the type of IIS log that you use, you need to change the columns that you create.

function Create_Table

{

param($DB, [string]$TableName)

 

$TableScript = New-Object -Type System.Collections.Specialized.StringCollection

$TableScript.Add("CREATE TABLE [dbo].[$TableName] ([date] [datetime] NULL,[time] [datetime] NULL ,[s-sitename] [varchar] (255) NULL,[s-computername] [varchar] (255) NULL ,[s-ip] [varchar] (50) NULL ,[cs-method] [varchar] (50) NULL ,[cs-uri-stem] [varchar] (512) NULL ,[cs-uri-query] [varchar] (2048) NULL ,[s-port] [varchar] (255) NULL ,[cs-username] [varchar] (255) NULL ,[c-ip] [varchar] (255) NULL ,[cs-version] [varchar] (255) NULL ,[cs(User-Agent)] [varchar] (512) NULL ,[cs(Cookie)] [varchar] (4096) NULL ,[cs(Referer)] [varchar] (2048) NULL,[cs-host] [varchar] (255) NULL ,[sc-status] [int] NULL ,[sc-substatus] [varchar] (255) NULL,[sc-win32-status] [varchar] (255) NULL,[sc-bytes] [int] NULL ,[cs-bytes] [varchar] (255) NULL ,[time-taken] [int] NULL)") | Out-Null

Database_ExecuteNonQuery_Command $DB $TableScript #Create Table

}

Execute statements

Executes any non-query statement that you provide it as a string through the ExecuteNonQuery class. 

Note   The statement cannot perform queries on the database like SELECT.

function Database_ExecuteNonQuery_Command

{

param($SQLDataBase, $CommandScript)

$Error.Clear()

$ExecutionType = [Microsoft.SqlServer.Management.Common.ExecutionTypes]::ContinueOnError

$SQLDataBase.ExecuteNonQuery($CommandScript, $ExecutionType)

 

trap {Write-Host "[ERROR]: $_"; continue}

}

Clean the log files

The IIS logs have headers and other lines that we do not want to import into the database. This function will get all the lines from the file by using Get-Content. By using Select-String, it removes any lines that contain a Regular Pattern, and then rewrites the file with all the good lines by using Set-Content.

function Clean_Log_File

{

param ($LogFile)

$Content = Get-Content $LogFile.FullName | Select-String -Pattern "^#" -notmatch

Set-Content $LogFile.FullName $Content

}

Then I started on the main logic 

  • Do a little bit of error checking (this is a great place for you to improve because I have done only a little bit)
  • Add the type Microsoft.SQLServer.Smo and create a connection to my SQL Server
  • Create my database or find the preexisting one
  • Get all folders in the path provided
  • Create my table (one per subfolder)
  • Load all the files
  • Clean the file
  • Load the cleaned file into SQL Server

$LineScript = New-Object -Type System.Collections.Specialized.StringCollection                                        

$LineScript.Add("BULK INSERT $Database.[dbo].[$TableName] FROM `"$File`" WITH (BATCHSIZE = 10,FIRSTROW = 1,FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n')") | Out-Null

Database_ExecuteNonQuery_Command $Database $LineScript

Rename the file

I rename the file with the .old extension, so I do not read the file more than once.

Rename-Item $File (($LogFile.FullName).TrimEnd($LogFile.extension) + ".old")        #Insure we don't add contents of file to table again

Note   You need to run with elevated permissions to be able to write to the database. You can do this by typing runas when you open Windows PowerShell, or read my blog post about how to do this with Task Manager.

If everything works well, you should see the following in SQL Management Studio.

Image of command output

After the script finishes running, you will be able to run Select statements and other SQL queries against your database to find information such as: 

  • Top users
  • Top sites
  • Top five users getting unauthorized access to sites

Watch for the next post when I will develop a script that automatically runs different reports from the information that we have collected.

~Chris

Thanks Chris. This has been a great blog. The script can be found in the Script Repository.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Chris,

    this article is very interesting and a good basis for solving this or any similar analyzing problem. Searching in a Database is usually more efficient than parsing log files and sure, the DB is good place to persist the required information! Great!

    I tried to look at your uploaded cod in the scripting repository and saw only one function published there: function Database_ExecuteNonQuery_Command

    So I had to download the whole script just to look at the code ... I have no Log files at home, but at work, I may be able to follow all of your steps.

    There are some nore general things I would like to ask, comment and suggest now ( offline :-)

    First of all I would generally recommend that you add

    Set-StrictMode -version 2

    to at least any longer and productive scripts. It's only one addional line and it greatly helps resolving typos!

    In your Create-Database function you added an unreferenced parameter; [string]$DBServer

    which is not referenced in the function and confused me from the beginning on ( btw: strict-mode doesn't help here, but maybe if they add a new directive to detect unused variables once upon a time .... who knows ... *sss* :_)

    Another thing, I would definitely recommend:

    Use here string to represent long SQL Statements, like

    $createTableString = @"

    CREATE TABLE [dbo].[$TableName] (

       [date] [datetime] NULL,

       [time] [datetime] NULL ,

       [s-sitename] [varchar] (255) NULL,

       [s-computername] [varchar] (255) NULL ,

       [s-ip] [varchar] (50) NULL ,

       [cs-method] [varchar] (50) NULL ,

       [cs-uri-stem] [varchar] (512) NULL ,

       [cs-uri-query] [varchar] (2048) NULL ,

       [s-port] [varchar] (255) NULL ,

       [cs-username] [varchar] (255) NULL ,

       [c-ip] [varchar] (255) NULL ,

       [cs-version] [varchar] (255) NULL ,

       [cs(User-Agent)] [varchar] (512) NULL ,

       [cs(Cookie)] [varchar] (4096) NULL ,

       [cs(Referer)] [varchar] (2048) NULL,

       [cs-host] [varchar] (255) NULL ,

       [sc-status] [int] NULL ,

       [sc-substatus] [varchar] (255) NULL,

       [sc-win32-status] [varchar] (255) NULL,

       [sc-bytes] [int] NULL ,

       [cs-bytes] [varchar] (255) NULL ,

       [time-taken] [int] NULL)

    "@

    If we add comment based help and some more error handling, as you already mentioned, the script would be ready for production!

    Thank you for sharing it

    Klaus (Schulte)

  • LogParser 2.2 can directly query IIS logfiles using SQL language constructs.  It can also driectly upload IIS logs to any ADO/OLE database system.

    All of this can be done from a simple commandline.

    Save PowerShell for doing the more difficult analysis.

    PowerShell can call the COM inrterface of LogParser to automate it.

    www.microsoft.com/.../details.aspx

  • Hey Chris,

    Seems like a good time to leave the SQLCMD call to create the database.. Hope to see more coming on this. Thanks once agian for sharing this..