Import IIS Logs into SQL Server

  • Article

I often need to analyse SharePoint IIS logs for different purpose, mainly for capacity planning. The first step to do so (after collection the logs from the servers) is to bulk import the logs into SQL.

To import the IIS logs into SQL I use the logparser tool (Download Details - Microsoft Download Center - Log Parser 2.2).

The following script will iterate through folders of log files and import them into a SQL Server table named IISLogs

 

$IISLogFolders = Get-ChildItem | ? { $_.PSIsContainer -eq $true}

foreach ($Workloadfldr in $IISLogFolders) { "Processing: {0}, {1}" -f $Workloadfldr.Name, $Workloadfldr.FullName $logfiles = Test-Path $($Workloadfldr.FullName + "\*") -Include *.log if ($logfiles -eq $true) { $QueryStr = "Select 'TypeYourName' As Customer, '$($WorkloadFldr.Name)' as Workload, LogFilename, LogRow,                            to_date(to_localtime(to_timestamp(date,time))) as date,                            to_localtime(to_timestamp(date,time)) as 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 IISLogs from '$(($Workloadfldr.FullName)+'\*.log')'" $cmdLine = "logparser -i:IISW3C ""$QueryStr"" -q -o:SQL -server:SQLServerName -database:IISLogs -createTable:ON" Invoke-Expression $cmdLine } else { "No log files found in folder" }    

}

 

I will try and post more about analysis of IIS logs after importing them into SQL.

- mutaz