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:
DSN Configuration with ODBC Driver
Follow these steps to configure a DSN using ODBC Driver for SQL Server:
5. Configure DSN Parameters: Fill in the necessary details for your SQL Server:
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