Most of us will agree if I say “SQL server is one of the powerful engine for query, reporting and statistic purpose”. As part of application troubleshooting, we come across many different set of logs those can be open in Notepad OR similar tools however it’s very different to take stats out it OR even open if it large. IIS log from web server is one of the nice example. Similarly you may have logs for different application such as D-trace for Enterprise vault.
Here in my blog, I am using IIS logs those can be uploaded to SQL server database using PowerShell script.
Create database (using PS script). If database already exists then it wouldn’t execute the command.
$PrepareDB= " USE master DECLARE @DBname VARCHAR(100) ='IISLogReview' DECLARE @DBcreate varchar(max) ='CREATE DATABASE ' + @DBNAME IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DBname) BEGIN EXECUTE(@DBcreate) END; GO " Invoke-Sqlcmd -ServerInstance SQL1 -Query $PrepareDB
Create table. If table already exists then it will skip but truncate existing rows so it hold only newest sets.
$PrepareTable= " USE IISLogReview DROP TABLE IF EXISTS dbo.IISLOG CREATE TABLE dbo.IISLOG ( [DATE] [DATE] NULL, [TIME] [TIME] NULL, [s-ip] [VARCHAR] (16) NULL, [cs-method] [VARCHAR] (8) NULL, [cs-uri-stem] [VARCHAR] (255) NULL, [cs-uri-query] [VARCHAR] (2048) NULL, [s-port] [VARCHAR] (4) NULL, [s-username] [VARCHAR] (16) NULL, [c-ip] [VARCHAR] (16) NULL, [cs(User-Agent)] [VARCHAR] (1024) NULL, [cs(Referer)] [VARCHAR] (4096) NULL, [sc-STATUS] [INT] NULL, [sc-substatus] [INT] NULL, [sc-win32-STATUS] [INT] NULL, [time-taken] [INT] NULL, INDEX cci CLUSTERED COLUMNSTORE ) TRUNCATE TABLE [IISLogReview].dbo.IISLOG " Invoke-Sqlcmd -ServerInstance SQL1 –Query $PrepareTable
Once database & table is prepared then you can upload multiple IIS log files placed at given location using either ‘Bulk insert’ OR ‘BCP’.
Bulk Insert script
$FolderPath='F:\IIS' $IISFiles=Get-ChildItem -Path $FolderPath -Name foreach ($File in $IISFiles) { $PATH=$FolderPath+'\'+$File $UploadQuery=" BULK INSERT [IISLogReview].dbo.IISLOG FROM '$($PATH)' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n' )" Invoke-Sqlcmd -ServerInstance SQL1 -Query $UploadQuery }
Please note, you may see “Bulk load data conversion” error those can ignored as IIS logs have few middle lines those are not well formatted and doesn’t need to be upload.
The upload activity can also be performed using BCP, my favorite for import/export to/from SQL server.
$FolderPath='F:\IIS' $IISFiles=Get-ChildItem -Path $FolderPath -Name foreach ($File in $IISFiles) { $PATH=$FolderPath+'\'+$File $PATH bcp [IISLogReview].dbo.IISLOG in $PATH -c -t" " -r\n -T -S SQL1 }
Here is complete script, make sure to change SQL server name & IIS folder location at the end of the script.
Function UploadIIS { PARAM ( [Parameter(Mandatory=$true)] [string]$SQLServer, [Parameter(Mandatory=$true)] [string]$IISlogFolder ) $PrepareDB= " USE master DECLARE @DBname VARCHAR(100) ='IISLogReview' DECLARE @DBcreate varchar(max) ='CREATE DATABASE ' + @DBNAME IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DBname) BEGIN EXECUTE(@DBcreate) END; GO " $PrepareTable= " USE IISLogReview DROP TABLE IF EXISTS dbo.IISLOG CREATE TABLE dbo.IISLOG ( [DATE] [DATE] NULL, [TIME] [TIME] NULL, [s-ip] [VARCHAR] (16) NULL, [cs-method] [VARCHAR] (8) NULL, [cs-uri-stem] [VARCHAR] (255) NULL, [cs-uri-query] [VARCHAR] (2048) NULL, [s-port] [VARCHAR] (4) NULL, [s-username] [VARCHAR] (16) NULL, [c-ip] [VARCHAR] (16) NULL, [cs(User-Agent)] [VARCHAR] (1024) NULL, [cs(Referer)] [VARCHAR] (4096) NULL, [sc-STATUS] [INT] NULL, [sc-substatus] [INT] NULL, [sc-win32-STATUS] [INT] NULL, [time-taken] [INT] NULL, INDEX cci CLUSTERED COLUMNSTORE ) TRUNCATE TABLE [IISLogReview].dbo.IISLOG " $Queries = $PrepareDB,$PrepareTable foreach ($Query in $Queries) { Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query } $IISFiles=Get-ChildItem -Path $IISlogFolder -Name foreach ($File in $IISFiles) { $PATH=$IISlogFolder+'\'+$File bcp [IISLogReview].dbo.IISLOG in $PATH -c -t" " -r\n -T -S $SQLServer } } UploadIIS -SQLServer SQL1 -IISlogFolder F:\IISFolder
Although few of next blogs will be on analysis of IIS log uploaded on SQL but there is always a way to manually query the uploaded data using adhoc queries via SQL management studio.