Analyze IIS log using PowerShell

In my last blog, you learn how IIS (or similar) log can be uploaded to SQL DB. Once logs are uploaded you can analyze it using PowerShell and create fancy report as well. Let’s see how this can be done.

Following script gives number of request per IIS page with response code.

$Query1="
Use IISLogReview
     SELECT TOP 20 [sc-STATUS] Response, [cs-uri-stem] Access_Page,  count(*) Total_Request from IISLOG
     GROUP BY [sc-STATUS],  [cs-uri-stem]
     ORDER BY COUNT(*) DESC"
$SqlOut1=Invoke-Sqlcmd -ServerInstance SQL1 -Query $Query1 -QueryTimeout 65535
$PrintOut1=$SqlOut1|Select-Object Response,Access_Page,Total_Request|Format-Table -AutoSize
$PrintOut1

iis1.jpg

Following script gives number of request per user.

$Query2="
Use IISLogReview
    SELECT COUNT(*) Total_Request, [s-username] UserName  FROM IISLOG
    GROUP BY [s-username]
    ORDER BY Total_Request DESC"
$SqlOut2=Invoke-Sqlcmd -ServerInstance SQL1 -Query $Query2 -QueryTimeout 65535
$PrintOut2=$SqlOut2|Select-Object Total_Request,UserName |Format-Table -AutoSize
$PrintOut2

iis2.jpg

This is the complete tool/function however this time it will generate a HTML output.

Function AnalyseIIS
{
    PARAM
    (
    #Default SQL will be local host
    [string]$SQLServer=$env:computername,
    #Default HTML File will be c;\temp\yyyymmdd.html
    [string]$HTMLFile='C:\TEMP\'+(Get-Date).tostring("yyyyMMddhhmm")+'.html'
    )

    $Query1=
        "Use IISLogReview
        SELECT TOP 20 [sc-STATUS] Response, [cs-uri-stem] Access_Page,  count(*) Total_Request from IISLOG
        GROUP BY [sc-STATUS],  [cs-uri-stem]
        ORDER BY COUNT(*) DESC"

    $Query2=
        "Use IISLogReview
        SELECT COUNT(*) Total_Request, [s-username] UserName  FROM IISLOG
        GROUP BY [s-username]
        ORDER BY Total_Request DESC"

    $a = ""
    $a = $a + "BODY{background-color:peachpuff;font-family: Calibri; font-size: 12pt;}"
    $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
    $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
    $a = $a + ""

    $SqlOut1=Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query1 -QueryTimeout 65535
    $PrintOut1=$SqlOut1|Select-Object Response,Access_Page,Total_Request| ConvertTo-HTML -PreContent '</pre>
<h2>Statistics Based on IIS Reponse, Page and Count</h2>
<pre>
'  -head $a |Out-String

    $SqlOut2=Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query2 -QueryTimeout 65535
    $PrintOut2=$SqlOut2|Select-Object Total_Request,UserName | ConvertTo-HTML -PreContent '</pre>
<h2>Statistics Based on UserName and Count</h2>
<pre>
'  -head $a |Out-String

    ConvertTo-Html -Title "IIS Log Analysis" -PostContent $PrintOut1,$PrintOut2 |Out-File $HTMLFile
    Invoke-Item $HTMLFile #open html after processing
}

AnalyseIIS
#OR Run with Parameter, Eg.
#AnalyseIIS -SQLServer SQL1 -HTMLFile C:\TEMP\IISReview.html 

iis3.jpg

You can have different select queries based on analysis you need and I believe my last two blogs can give you direction on how large set of text files can be uploaded to SQL then analyze it using PowerShell.

 

Advertisements

#default

Upload large logs files to SQL database

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.

 

SQL table export using PowerShell

Most of the PowerShell guys may need to extract data from SQL databases. There could be two or more ways to do the same task however as per my latest findings in one of the project I figured out including BCP in our script is the best way to achieve that. In my following two examples I am using AdventureWorksDB and a SQL query that write data into a text file.

Example 1 using Invoke-SQLCmd.

$SQLQueryy= "USE AdventureworksDW2016CTP3
SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI]
WHERE ProductKey =532"
$StartTime = (Get-Date)
Invoke-Sqlcmd -ServerInstance SQL2 -Query $SQLQueryy -QueryTimeout 65535 |Format-Table -HideTableHeaders -AutoSize |Out-File 'C:\temp\via_InvokeSQL.txt'  -Width 500
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

UsingSQLCmd.jpg

This script takes almost 14 seconds to extract the rows from SQL.

Example 2 Using BCP

$StartTime = (Get-Date)
bcp  "USE AdventureworksDW2016CTP3 SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI] WHERE ProductKey =532"  queryout 'C:\temp\via_BCP.txt' -T -c -S SQL2
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

UsingBCP.jpg

This query take less than 1 seconds with neat & clean output file.

Design Windows form using PowerShell

As you all be agree, windows forms are more user friendly then command line interface. If you are a windows admin and figuring out how to write a windows form using PS code then use following link and download Form Builder PS script to easily write such code.

In my simple example, created a windows form with a ‘Button’ and a ‘Label’ however your script may have lots of different options.

FormMaker.jpg

Once you complete preparation of your windows form, then click on ‘Export’, this will develop code file for the form and windows control you have selected. Now you write down code on ‘Button’ click. I have added following line of code.

$Button_click=
 {
  $wmiOS = Get-WmiObject -Class Win32_OperatingSystem;
  $OS = $wmiOS.caption;
  $mLabel1.Text=$OS
 }

$mButton1.add_click($Button_click)

Outputfile.jpg

Entire script can be copied from Here.

    Add-Type -AssemblyName System.Windows.Forms
    Add-Type -AssemblyName System.Drawing
    $MyForm = New-Object System.Windows.Forms.Form
    $MyForm.Text="MyForm"
    $MyForm.Size = New-Object System.Drawing.Size(400,200) 

        $mButton1 = New-Object System.Windows.Forms.Button
                $mButton1.Text="GetOSVersion"
                $mButton1.Top="43"
                $mButton1.Left="7"
                $mButton1.Anchor="Left,Top"
        $mButton1.Size = New-Object System.Drawing.Size(120,23)
        $MyForm.Controls.Add($mButton1) 

        $mLabel1 = New-Object System.Windows.Forms.Label
                $mLabel1.Text=""
                $mLabel1.Top="83"
                $mLabel1.Left="17"
                $mLabel1.Anchor="Left,Top"
        $mLabel1.Size = New-Object System.Drawing.Size(400,23)
        $MyForm.Controls.Add($mLabel1) 

        $Button_click=
        {
        $wmiOS = Get-WmiObject -Class Win32_OperatingSystem;
        $OS = $wmiOS.caption;
        $mLabel1.Text=$OS
        }
        $mButton1.add_click($Button_click)

        $MyForm.ShowDialog()

If you feel the underline code should remain hidden and the end user should not see the background logic then you can convert your PS1 script to EXE using PS2EXE tool.