Configuring DSN with ODBC Driver and Automating Tasks with PowerShell

Blogs

Monitoring and Handling Throttling in Microservices Applications within the Kubernetes Cluster
August 22, 2023
OBJECT DETECTION: Unleashing the Power of Computer Vision
September 3, 2023

Configuring DSN with ODBC Driver and Automating Tasks with PowerShell

powershell

Introduction

Data Source Name (DSN) configuration is an essential step for connecting applications and scripts to databases using the Open Database Connectivity (ODBC) standard. In this blog post, we will guide you through the process of configuring a DSN using ODBC Driver for SQL Server. Additionally, we will provide you with a sample PowerShell script that automates the process of extracting and importing IIS logs into a SQL Server database using the configured DSN.

Prerequisites

Before we start configuring the DSN and running the PowerShell script, make sure you have the following prerequisites in place:

  1. ODBC Driver  for SQL Server: Download and install the ODBC Driver for SQL Server from the official Microsoft website.
  2. PowerShell: Ensure that you have PowerShell installed on your system. The script we provide is compatible with PowerShell.
  3. SQL Server: You should have access to a SQL Server instance where you want to import IIS logs.

DSN Configuration with ODBC Driver

Follow these steps to configure a DSN using ODBC Driver  for SQL Server:

  1. Open ODBC Data Source Administrator: On your Windows machine, open the ODBC Data Source Administrator. You can find this by searching for “ODBC” in the Start menu.
  2. Navigate to System DSN: Under the “User DSN” tab, select “System DSN” as we want to create a system wide DSN configuration.
  3. Add a New DSN: Click the “Add” button to create a new DSN.
  4. Select ODBC Driver: From the list of available drivers, select “ODBC Driver 17 for SQL Server” and click “Finish.”

5. Configure DSN Parameters: Fill in the necessary details for your SQL Server:

    • Name: Provide a name for your DSN. This name will be used in your PowerShell script to connect to the database.
    • Server: Specify the SQL Server instance name or IP address.
    • Authentication: Choose the appropriate authentication method and provide the necessary credentials.
    • Database: Select the default database you want to connect to.
    • Test Connection: Click the “Test” button to verify that your DSN configuration is correct.
    • Finish: Once the test connection is successful, click “OK” to save your DSN configuration.

With the DSN configured, you can now use it in your PowerShell script to connect to the SQL Server database.

PowerShell Script for Automating IIS Log Import

Below is a sample PowerShell script that automates the process of extracting and importing IIS logs into a SQL Server database using the DSN we configured earlier. This script utilizes the Log Parser tool and SQL queries to accomplish the task:

# DSN Configuration

$DSNName = “YourDSNName”  # Replace with the actual DSN name

# Server and File Paths

$IISServerName = “YourServerName”  # Replace with your IIS server name

$LogParserPath = “C:Program Files (x86)Log Parser 2.2LogParser.exe”  # Specify the path to LogParser executable

$LogFileDir = ‘C:inetpublogsLogFiles*’  # Directory where IIS logs are located

$SqlTable = “IISLogTable”  # Replace with your SQL Server table name

# SQL Query Configuration

$Query = @”

SELECT CONVERT(char(10), Last_Capture_Date, 126) as Last_Capture_Date, Query_text

FROM YourTableName  — Replace with your table name

WHERE YourColumnName = ‘$IISServerName’;  — Replace with your column name

“@

# SQL Query Execution

try {

# Create an ODBC connection using the DSN

$Connection = New-Object System.Data.Odbc.OdbcConnection

$Connection.ConnectionString = “DSN=$DSNName”

$Connection.Open()

# Create a SQL command object

$Command = $Connection.CreateCommand()

$Command.CommandText = $Query

# Execute the SQL query and store the result in a variable

$Result = $Command.ExecuteReader()

# Access the specific column value from the result (assuming it’s the first row)

if ($Result.Read()) {

$SpecificColumnValue = $Result[“Last_Capture_Date”]

$Query_text = $Result[“Query_text”]

}

# Close the SQL connection

$Connection.Close()

}

catch {

Write-Host “Error: $($_.Exception.Message)”

}

$StartDate = $SpecificColumnValue

$EndDate = (Get-Date).AddDays(0).ToString(“yyyy-MM-dd”)

# Get a list of all log files in the directory

$LogFiles = Get-ChildItem -Path $LogFileDir -Filter “*.log” -Recurse

# Loop through each log file and perform an action (e.g., import into SQL Server)

foreach ($LogFile in $LogFiles) {

$LogParserCommand = @”

$LogParserPath “SELECT $Query_text, ‘$IISServerName’ as Server_Name, ‘$EndDate’ as Load_Date INTO $SqlTable FROM $($LogFile.FullName) WHERE date >= ‘$StartDate’ and date <= ‘$EndDate'” -createTable:ON -o:SQL -dsn:$DSNName

“@

# Execute LogParser to import the IIS logs into SQL Server

try {

Invoke-Expression -Command $LogParserCommand

}

catch {

Write-Host “Error: $_”

}}

# SQL Update Configuration

$UpdateQuery = @”

UPDATE YourTableName  — Replace with your table name

SET Last_Capture_Date = ‘$EndDate’, Last_Capture_Status = ‘Success’

WHERE YourColumnName = ‘$IISServerName’;  — Replace with your column name

“@

# Execute the SQL UPDATE statement

try {

# Create a new server connection using the DSN

$Connection = New-Object System.Data.odbc.OdbcConnection

$Connection.ConnectionString = “DSN=$DSNName”

$Connection.Open()

# Create a SQL command object for the update query

$Command = $Connection.CreateCommand()

$Command.CommandText = $UpdateQuery

# Execute the update query

$Command.ExecuteNonQuery()

# Close the SQL connection

$Connection.Close()

Write-Host “Record updated successfully.”

}

catch {

Write-Host “Error: $($_.Exception.Message)”

}

}

Conclusion

In this blog post, we covered the steps involved in configuring a DSN using ODBC Driver  for SQL Server and provided you with a sample PowerShell script to automate the process of importing IIS logs into a SQL Server database. With this setup, you can efficiently manage and analyse IIS logs in your SQL Server environment, making it easier to monitor your web server’s performance and security.


neha.annam

Leave a Reply

Your email address will not be published. Required fields are marked *