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.

How to configure SQL server Fail over clustering Instance (FCI).

This blog is part of a SQL HA-DR solution series , In my previous blogs I mentioned how Log Shipping, Mirroring & AlwaysOn Availability Group can be configured, now here you will get step by step procedure for SQL Fail Over Cluster Instance (FCI) high Availability solution. SQL FCI is sometime also known as AlwaysOn FCI and it’s bit different then AlwaysOn AG (Availability group). Always ON FCI need shared storage that is accessible from all the participant node and it provide instance level high availability.  If your primary (or active) server is down then secondary (passive) take responsibilities for all SQL operation.

The details about SQL FCI can be found here.

Continue reading

Unable to re-create Availability group with same name

While working in my test environment, we have delete existing Availability group and try creating a new one with same name. Unfortunately It was failing with error  “Failed to create availability group ‘SQL AAG’, because a Windows Server Failover Cluster (WSFC) group with the specified name already exists.”

erroraag

We successfully deleted existing configuration as per Microsoft & Fail-over cluster log does not give any clue about existing AAG group.

Here is the solution.

  1. Drop the availability group if not in previous attempt, refer
  2. Take backup of registry then delete key “HKEY_LOCAL_MACHINE\Cluster \HadrAgNameToldMap” from all nodes participating in cluster.
  3. From SQL configuration manager, uncheck ‘Enable Alwayson Availability Groups’, apply OK. Restart SQL service.config
  4. Check the box ‘Enable Alwayson Availability Groups’ again, Apply-ok, restart SQL service.

Now you should be able to create availability group with same existing name.

successaag

#aag, #alwayson

Overview of Memory-Optimized Table.

We all know, retrieving data from physical memory (AKA RAM) is faster than the data saved in physical disk (HDD). Memory-optimized tables save & retrieve data into/from physical memory rather than Hard-disk. This could be excellent feature to solve lot of use cases such as online game where only end results matters.

This feature was introduced with SQL version 2014 and continuous improvement are being done in consecutive releases. In lot of places, you would see this feature named as “Hekaton” OR “In-Memory optimization”.

Following a short demo may help to develop your understanding regarding Memory table & performance comparison with disk based table.

-- Create InMemoryTableDemo database. 
USE [master]
CREATE DATABASE [InMemoryTableDemo]
GO
-- Create Memory optimized File Group.
USE [master]
ALTER DATABASE [InMemoryTableDemo]
ADD FILEGROUP MemoryGroupFG CONTAINS MEMORY_OPTIMIZED_DATA
-- Add the container for file group. 
ALTER DATABASE [InMemoryTableDemo]
ADD FILE (NAME = AWMemFile, FILENAME = N'F:\FGData\FG1') TO FILEGROUP MemoryGroupFG
GO

In my following example, I have set ‘Durability’ to ‘schema_only’ which means only the schema will be recoverable in case of crash as the rows will remain in memory. The other option is ‘Schema_and_Data’ that protect table schema as well rows, the copy of data will also save in HDD.

-- Create Memory Based Table.
USE [InMemoryTableDemo]
CREATE TABLE TableInMemory
(
       ID INT CONSTRAINT [pk_TableInMemory_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), 
       EMPID INT,
       CharData VARCHAR(100)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Let’s create a disk based table with same columns.

-- Create Disk Based Table.
USE [InMemoryTableDemo]
CREATE TABLE TableInDisk
(      
       ID INT CONSTRAINT [pk_TableInDisk_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), 
       EMPID INT, 
       CharData VARCHAR(100)
)
GO

Now we are inserting 100k rows and it takes only (approximately) 4 seconds to insert in memory based table whereas disk based table takes (approximately) 200 seconds.

-- Test 100,000 inserts into a Memory Based Table 
DECLARE @start DATETIME = GETDATE();
DECLARE @count INT = 1;
WHILE @count < 100000
BEGIN
  INSERT INTO TableInMemory VALUES 
       (@count, 'stuff');
        SET @count = @count + 1;
END
SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert]
GO

-- Test 100,000 inserts into a Disk Based Table
DECLARE @start DATETIME = GETDATE();
DECLARE @count INT = 1;
WHILE @count < 100000
BEGIN
  INSERT INTO TableInDisk
VALUES 
       (@count, 'stuff');
       SET @count = @count + 1;
END
SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert]
GO

first

Let’s test the retrieval of rows. Run following 3 queries together while enabling ‘Include actual execution plan’ OR Live query statistics‘.

-- Run following 3 queries together while enabling 'include actual execution plan' OR 'Live querystatistics' 
USE [InMemoryTableDemo]
SELECT * FROM TableInDisk
SELECT * FROM TableInMemory
GO

Comparsion.jpg

You would notice Memory table takes (approximately) 16 % resources as compare to disk based table that takes 84 % of resources. Additionally storage & I/O Cost columns will give the details of they type of query.

More details on Memory optimized table.

#hekaton, #in-memory-optimization, #sql2016, #storage

Overview of SQL 2016 ‘Always Encrypted’ Security feature.

In couple of my last blogs, we discussed about various security options with SQL 2016. Now in this blog, we are going to discuss & demonstrate one other important security feature of SQL 2016 that is called as ‘Always Encrypted’.

The legacy features provide encryption either on network layer (SSL) OR data files (MDF) OR Backup files(BAK) and encryption happens for all data OR none. But ‘Always encrypted’ uses client application layer via ADO.Net to encrypt/decrypt column specific data. You may have table where ‘FirstName’ & ‘LastName’ is in plain text but the ‘passport no’ is encrypted. The ‘passport no’ will always be in cipher text during transition via network and will save in encrypted format in data files OR backup files. This feature also available for SQL azure so even Microsoft will see ecrypted data.

Let’s use following steps to demonstrate how Always encrypted can be configured & use in test/production environments of SQL 2016.

---Create AlwaysEncrptedDemo database. 
USE [master]
CREATE DATABASE AlwaysEncryptedDemo
GO
---Create table with 6 columns.   
USE [AlwaysEncryptedDemo]
CREATE TABLE EmployeeData
(
       EMPID INT Primary key,
       FirstName VARCHAR(100), 
       LastName VARCHAR(100),
       MobileNo BIGINT, 
       SALARY BIGINT,
       PassportNo VARCHAR(100)
)
GO

-- Insert 5 records. 
USE [AlwaysEncryptedDemo]
INSERT INTO EmployeeData VALUES
       (1, 'Pradeep', 'Papnai', 9595046647, 50000, 'ATNPP12345'), 
       (2, 'Vikram', 'Jadhav', 8088145698, 52000, 'YAG8383P'),
       (3, 'Darshan', 'Bandodkar', 9198234567, 51000, 'DD123453'),
       (4, 'Ajay', 'Jain', 875691232, 55000, 'AJ123JK12'),
       (5, 'Yogesh', 'Ghute', 8787453212, 49000, 'PT9876KA12')

Normal retrieval of rows.

-- Retrieve all records, our next action will encrypt last three columns those are confidential. 
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo
FROM EmployeeData

FirstSelect

Configure Always Encrypted on columns of a table

Please note these step can also be done using SQL query & Powershell but it’s recommended to do it using wizard for better understanding.

  1. Expand ‘AlwaysEncrptedDemo’ database, Right click on ‘EmployeeData’, select ‘Encrypt columns’
  2. Click NEXT on introduction page.
  3. Check on columns ‘MobileNo, Salary, PassportNo. Select EncrptionType click NEXT.

There are two type of encryption is supported:-

Deterministic – Always encrypts to the same cipher text means decrypted data will always be the same and data can be indexed.

Randomized – It’s consider as more secure as the cipher text will always be different.  The data of this column cannot be indexed.

  1. Key Store provider = Windows Certificate Store, Master key source = current user, click NEXT.
  2. Select ‘Proceed to finish now’, although you can generate PowerShell next time you do it, click NEXT.
  3. Verify the choice you made, click ‘NEXT’.
  4. Encryption might take time due to size of column present in table. In production system, it is usually performed off peak hours.

Here is the slide show for above mentioned steps:-

This slideshow requires JavaScript.

-- Now if you run select query again the results for column MobileNo, Salary, PassportNo will be encrypt
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName, MobileNo, SALARY, PassportNo
FROM EmployeeData

EncryptSelect

If you wish to see encrypted data using SQL Management studio then the connection string should include following line ‘column encryption setting=enabled’

Right click on query windows, connection-change connection, Additional Connection Parameters

SSMS_Connection_string

-- Now the columns can be seen in without encryption. 
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName, MobileNo, SALARY, PassportNo
FROM EmployeeData

FirstSelect

Well this is not the end of story. If you connect to this SQL server using SSMW from other machine you will notice columns are encrypted even though connection string encrypted is enabled, it’s because you don’t have certificate for encryption.

Always encrypted feature maintain two keys :-

Column Master Key – this is an encryption key that protects the column encryption key(s). You must have at least one master key before encrypting any columns.

Column Encryption Key – this is the encryption key that actually protects that encrypted columns.

Keys

You can either use existing keys OR create a new one. In order to copy the certificate, use following steps.

Click on ‘RUN’, Type ‘MMC’ and press the ENTER key. On the ‘File’ menu, click on ‘Add/Remove Snap In’.
Select ‘Certificates’. Select ‘My user account’ and click ‘Finish’ and ‘OK’.
Expand ‘Personal’, then ‘Certificate’, Select ‘Always Encrypted auto certificate’

Certificate 1

You can export this certificate to other machine where ever client OR application connects to SQL servers for encryption purpose. Here is example how connection string can be used with PowerShell.

Use case without encryption connection string

#Change SQL server name
$connectionString = "Data Source=SQL1;Integrated Security=true;Initial Catalog=AlwaysEncryptedDemo;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = “SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo FROM EmployeeData”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object "System.Data.DataTable"
$table.load($result)
$table | format-table 
$connection.Close()

Powershell_Without_S.jpg

Use case with encryption connection string

$connectionString = "Data Source=SQL1;Integrated Security=true;Initial Catalog=AlwaysEncryptedDemo;column encryption setting=enabled"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = “SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo FROM EmployeeData”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object "System.Data.DataTable"
$table.load($result)
$table | format-table 
$connection.Close()

With_connection_string.jpg

More Details

#sql, #sql-security, #sql2016