How to periodically send IIS Logs with custom fields to SQL Server database

Recently I wanted to push IIS logs to SQL database for analytics purpose.

Following are the steps I used

Step 1: Configurations at IIS side

Add custom fields as shown below

Step 2: Create a table in SQL Server database with following columns

Step 3: Construct following PowerShell scripts

a. RemoveLogs.ps1 - This will remove existing logs in working directory and clean up for new logs

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function RemoveLogFiles
{
    Write-Host "Removing log files..."
    Remove-Item ($httpLogPath1)
}
 
function Main
{   
 
    [string] $httpLogPath1 = "E:\TempLogs\*.log"
     
    RemoveLogFiles 
 
    Write-Host -Fore Green "Successfully removed log files."
}

b. CopyLogs.ps1 - This will copy Logs from IIS log file location to our logs directory. I'm copying everything created during last two days

$Date = Get-Date
$Date = $Date.adddays(-2)
$Date2Str = $Date.ToString("yyyMMdd")
 
$Files1 = gci "\\IIS-01\LogFiles\W3SVC4"
ForEach ($File in $Files1){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "E:\TempLogs"}
}

c. CopyLogsToDB.ps1 - This will import logs to our SQL Server database table

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function ImportLogFilesServer(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."
    }
 
    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "
 
    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS ServerIpAddress" `
            + ", cs-method AS Method" `
            + ", cs-uri-stem AS UriStem" `
            + ", cs-uri-query AS UriQuery" `
            + ", s-port AS Port" `
            + ", username  AS Username" `
            + ", c-ip AS ClientIpAddress" `
            + ", cs(User-Agent) AS UserAgent" `
            + ", pageurl AS Referrer" `
            + ", sc-status AS HttpStatus" `
            + ", sc-substatus AS HttpSubstatus" `
            + ", sc-win32-status AS Win32Status" `
            + ", time-taken AS TimeTaken" `
            + ", storename  AS StoreName" `
        + " INTO IISLog" `
        + " FROM $httpLogPath"

 
    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=DB-01;Database=IISLogs;Trusted_Connection=yes;"
 
    [string[]] $parameters = @()
 
    $parameters += $query
    $parameters += "-i:W3C"
    $parameters += "-e:-1"
    $parameters += "-o:SQL"
    $parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"
 
    Write-Debug "Parameters: $parameters"
 
    Write-Host "Importing log files to database..."
    $logParser 
    $parameters
    & $logParser $parameters

}
 
 
function Main
{
    [string] $httpLogPath = "E:\TempLogs\*.log"
 

    ImportLogFilesServer $httpLogPath
 
#Repeat above for all other server
 
    Write-Host -Fore Green "Successfully imported log files."
}
 
Main

Step 4: Apply a schedule using Task Scheduler

This will upload latest logs including custom fields to SQL Server database.

Keynote : Building SPFx solutions for SharePoint Online and MS Teams at Global Microsoft 365 Developer Bootcamp Sri Lanka

It was my privilege to speak at Global Microsoft 365 Developer Bootcamp Sri Lanka.

I presented on building SPFx solutions for SharePoint Online and MS Teams.

Resolving “Could not retrieve a valid windows identity for username with UPN.” error for a user from external domain

In one of my SharePoint/BI farms I got following error when opening a PowerPivot report with data refresh.

     

In this scenario I had another domain with Forest trust and I faced the error when logging in to SharePoint site with a user account from that domain.
Following is the error message I got from ULS

SPSecurityContext: Could not retrieve a valid windows identity for username '' with UPN ''. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied

Following are the checkpoints

• Relevant SPN entries are in place : Done
• Service account is enabled for delegation : Done
• Name suffix is enabled for routing : Gotcha!!

In my scenario relevant name suffix was disabled. Following is the way to enable it

• Log in to Domain Controller
• Navigate to Active Directory Domains and Trusts. Go to Properties & Trusts tab

      

        

• Select the relevant domain name & click on Properties.Then navigate to Name Suffix Routing Tab

    

       

• Enable it

     

   














It resolved the error for me!

FIT Code Rush 2018 - Keynote

I was privileged to address at CodeRush hackathon organized by Faculty of IT - University of Moratuwa recently. Myself and my team at LB Finance PLC submitted few challenges for the hackathon from FinTech and cloud spaces.

We spent the same night at University premises and evaluated the competition. I was amazed and thrilled by the competence and dedication from young students

During my speech I presented opportunities available in FinTech and Cloud segments and stressed the importance of not forgetting basic concepts

    

Excel Services: Unable to refresh one or more data connections when using Excel 2016

Recently I used Microsoft Excel 2016 to publish a excel report to SharePoint, it gives following error when running the workbook. The workbook had a connection to BISM which is referring Analysis Services cube.

Error Message

ULS error log

The workbook '' attempted to access external data using the unsupported provider 'Provider=MSOLAP.8;

Following is the data connection I had

To resolve the issue, I had to add MSOLAP.8 to trusted data providers list.

Finally restart IIS to see the change

Change the size of Azure VM in a given schedule using Azure Runbook

In our previous article, I explained how to write a simple PowerShell script to expand the size of an Azure VM.

1. PowerShell script to expand and reduce the size of an Azure VM
2. Change the size of Azure VM in a given schedule using Azure Runbook

In this article I’ll explain how to execute PowerShell scripts in a given schedule using Azure Automation Account, Runbooks and Schedules.

What is an Azure Automation?

Azure Automation is a Software as a Service (SaaS) that can be used to automate processes. You can automate cloud automation tasks using Azure Automation. Some common tasks are,

• Build and deploy resources
• Configure VMs
• Monitor resources

What is a Runbook?

Azure Automation use Runbooks to automate processes. Runbooks are executed in automation Sandboxes, which means as a Platform as a Service (PaaS) VMs. You will get the isolation that you expect from Runbooks

Following are the steps I used to expand and reduce the size of VMs in my Azure tenant.

1. Create Azure Automation Account

Navigate to Variables section and create following variables

2. Create the Runbook

Create new Runbook as a PowerShell script to expand the size

Add following script

$connectionName = "AzureRunAsConnection"

try

{

# Get the connection "AzureRunAsConnection "

$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

"Logging in to Azure..."

Add-AzureRmAccount `

-ServicePrincipal `

-TenantId $servicePrincipalConnection.TenantId `

-ApplicationId $servicePrincipalConnection.ApplicationId `

-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

catch {

if (!$servicePrincipalConnection)

{

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Error -Message $_.Exception

throw $_.Exception

}

}

$vmName = Get-AutomationVariable -Name 'vmName'

$resourceGroup = Get-AutomationVariable -Name 'resourceGroup'

$lowHwProfile = Get-AutomationVariable -Name 'lowHwProfile'

"Updating VM size"

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName

$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName

$vm.HardwareProfile.VmSize = $lowHwProfile

Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

"VM size updated"

Same way create another Runbook to reduce the VM size

3. Create Schedules and apply to Runbooks

Click on Schedules in Automation Account

Navigate to specific Runbook to apply the schedule

That’s it. We have to create another schedule to reduce the size of the VM on Monday night and apply to the particular Runbook

Presentation - Automating Business Processes with SharePoint & Flow

Following is the presentation I did at Microsoft Student Champs Monthly Meeting- February 2018