#region ETW Monitoring Made Simple
function Start-LIMSTrace {
param(
[string]$TraceName = "LIMS-Debug",
[string]$OutputPath = "C:\temp\lims-trace.etl",
[string[]]$Providers = @("Microsoft-Windows-IIS-Logging", "Microsoft-Windows-ASP.NET", "Microsoft-Windows-HttpService")
)
Write-Host "Starting LIMS ETW trace..." -ForegroundColor Green
Write-Host "Trace file: $OutputPath" -ForegroundColor Cyan
Write-Host "Providers: $($Providers -join ', ')" -ForegroundColor Cyan
# Create trace with multiple providers
$providerArgs = $Providers | ForEach-Object { "-p `"$_`"" }
$command = "logman create trace `"$TraceName`" $($providerArgs -join ' ') -o `"$OutputPath`" -ets"
try {
Invoke-Expression $command
Write-Host "✓ Trace started successfully" -ForegroundColor Green
Write-Host "To stop: Stop-LIMSTrace -TraceName '$TraceName'" -ForegroundColor Yellow
Write-Host "Reproduce your issue now, then stop the trace." -ForegroundColor Yellow
}
catch {
Write-Error "Failed to start trace: $($_.Exception.Message)"
}
}
function Stop-LIMSTrace {
param(
[string]$TraceName = "LIMS-Debug",
[string]$OutputPath = "C:\temp\lims-trace.etl",
[string]$ConvertToCSV = "C:\temp\lims-trace.csv"
)
Write-Host "Stopping LIMS ETW trace..." -ForegroundColor Yellow
try {
# Stop the trace
logman stop $TraceName -ets
Write-Host "✓ Trace stopped" -ForegroundColor Green
# Convert to readable format
if ($ConvertToCSV) {
Write-Host "Converting trace to CSV..." -ForegroundColor Cyan
tracerpt $OutputPath -o $ConvertToCSV -of CSV
Write-Host "✓ Trace converted to: $ConvertToCSV" -ForegroundColor Green
# Show sample of the data
$sample = Import-Csv $ConvertToCSV | Select-Object -First 5
Write-Host "`nSample trace data:" -ForegroundColor Cyan
$sample | Format-Table -AutoSize
}
}
catch {
Write-Error "Failed to stop trace: $($_.Exception.Message)"
}
}
function Analyze-LIMSTrace {
param(
[string]$CSVPath = "C:\temp\lims-trace.csv",
[string[]]$ErrorKeywords = @("error", "fail", "exception", "timeout", "500", "401", "403")
)
if (-not (Test-Path $CSVPath)) {
Write-Error "Trace file not found: $CSVPath"
return
}
Write-Host "Analyzing LIMS trace data..." -ForegroundColor Cyan
$traceData = Import-Csv $CSVPath
Write-Host "`nTrace Summary:" -ForegroundColor Yellow
Write-Host "Total events: $($traceData.Count)"
Write-Host "Time range: $($traceData[0].'Date/Time') to $($traceData[-1].'Date/Time')"
# Look for errors
$errors = $traceData | Where-Object {
$line = $_.Description + " " + $_.'User Data'
foreach ($keyword in $ErrorKeywords) {
if ($line -like "*$keyword*") { return $true }
}
return $false
}
if ($errors) {
Write-Host "`nErrors found:" -ForegroundColor Red
$errors | Select-Object 'Date/Time', Description, 'User Data' | Format-Table -Wrap
} else {
Write-Host "`nNo obvious errors found in trace" -ForegroundColor Green
}
# Event type summary
Write-Host "`nEvent types:" -ForegroundColor Cyan
$traceData | Group-Object 'Event Name' | Sort-Object Count -Descending | Select-Object -First 10 | Format-Table Name, Count
}
#endregion
#region Oracle Quick Checks (Using SQLPLUS or SQL Developer)
function New-OracleHealthCheckScript {
param(
[string]$OutputPath = "C:\temp\oracle_health_check.sql"
)
$sqlScript = @"
-- Oracle LIMS Health Check Script
-- Generated: $(Get-Date)
-- Run this in SQL*Plus or SQL Developer
SET PAGESIZE 50
SET LINESIZE 120
COLUMN tablespace_name FORMAT A20
COLUMN status FORMAT A10
COLUMN pct_used FORMAT 999.99
PROMPT ================================================
PROMPT ORACLE LIMS HEALTH CHECK - $(Get-Date -Format 'yyyy-MM-dd HH:mm')
PROMPT ================================================
PROMPT
PROMPT 1. INSTANCE STATUS
SELECT instance_name, status, database_status, uptime FROM v$$$instance;
PROMPT
PROMPT 2. CRITICAL TABLESPACE USAGE (>85% = CRITICAL)
SELECT
ts.tablespace_name,
ROUND(((ts.total_mb - NVL(fs.free_mb, 0)) / ts.total_mb) * 100, 2) as pct_used,
ts.total_mb,
NVL(fs.free_mb, 0) as free_mb,
CASE
WHEN ((ts.total_mb - NVL(fs.free_mb, 0)) / ts.total_mb) * 100 > 95 THEN 'CRITICAL'
WHEN ((ts.total_mb - NVL(fs.free_mb, 0)) / ts.total_mb) * 100 > 85 THEN 'WARNING'
ELSE 'OK'
END as status
FROM (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as total_mb
FROM dba_data_files GROUP BY tablespace_name
) ts
LEFT JOIN (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space GROUP BY tablespace_name
) fs ON ts.tablespace_name = fs.tablespace_name
WHERE ((ts.total_mb - NVL(fs.free_mb, 0)) / ts.total_mb) * 100 > 80
ORDER BY pct_used DESC;
PROMPT
PROMPT 3. ACTIVE BLOCKING SESSIONS
SELECT
s1.sid as blocked_sid,
s1.username as blocked_user,
s1.machine as blocked_machine,
s2.sid as blocking_sid,
s2.username as blocking_user,
s2.machine as blocking_machine
FROM v$$$session s1
JOIN v$$$session s2 ON s1.blocking_session = s2.sid
WHERE s1.blocking_session IS NOT NULL;
PROMPT
PROMPT 4. LONG RUNNING QUERIES (>10 minutes)
SELECT
sid,
serial#,
username,
machine,
program,
ROUND(last_call_et/60, 1) as minutes_running,
sql_id,
status
FROM v$$$session
WHERE status = 'ACTIVE'
AND type = 'USER'
AND last_call_et > 600
ORDER BY last_call_et DESC;
PROMPT
PROMPT 5. TOP WAIT EVENTS (LAST HOUR)
SELECT
event,
total_waits,
ROUND(time_waited/100, 2) as time_waited_sec,
ROUND(average_wait, 2) as avg_wait_ms
FROM v$$$system_event
WHERE wait_class != 'Idle'
AND time_waited > 100
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;
PROMPT
PROMPT 6. CONNECTION COUNT BY APPLICATION
SELECT
program,
machine,
username,
COUNT(*) as connection_count,
COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) as active_count
FROM v$$$session
WHERE type = 'USER'
AND username IS NOT NULL
GROUP BY program, machine, username
HAVING COUNT(*) > 3
ORDER BY connection_count DESC;
PROMPT
PROMPT 7. RECENT ERRORS FROM ALERT LOG
SELECT
timestamp,
message_text
FROM v$$$diag_alert_ext
WHERE component_id = 'rdbms'
AND message_level <= 16 -- Errors and warnings
AND timestamp > SYSDATE - 1 -- Last 24 hours
ORDER BY timestamp DESC
FETCH FIRST 10 ROWS ONLY;
PROMPT
PROMPT Health check complete. Review any WARNING or CRITICAL items above.
PROMPT
"@
$sqlScript | Out-File -FilePath $OutputPath -Encoding UTF8
Write-Host "Oracle health check script created: $OutputPath" -ForegroundColor Green
Write-Host "Run this in SQL*Plus: sqlplus username/password@database @$OutputPath" -ForegroundColor Cyan
}
function Test-OracleQuick {
param(
[string]$Server = "localhost",
[int]$Port = 1521,
[string]$ServiceName,
[string]$Username,
[string]$Password
)
if (-not $ServiceName -or -not $Username -or -not $Password) {
Write-Host "Usage: Test-OracleQuick -Server 'server' -ServiceName 'ORCL' -Username 'user' -Password 'pass'" -ForegroundColor Yellow
return
}
$connectionString = "Data Source=$Server`:$Port/$ServiceName;User Id=$Username;Password=$Password;"
Write-Host "Testing Oracle connection..." -ForegroundColor Cyan
Write-Host "Server: $Server`:$Port/$ServiceName" -ForegroundColor Gray
Write-Host "User: $Username" -ForegroundColor Gray
try {
# Test basic connectivity first
$tcpTest = Test-NetConnection -ComputerName $Server -Port $Port -WarningAction SilentlyContinue
if (-not $tcpTest.TcpTestSucceeded) {
Write-Host "✗ Network connectivity failed" -ForegroundColor Red
return
}
Write-Host "✓ Network connectivity OK" -ForegroundColor Green
# Test Oracle connection (requires Oracle Data Provider)
Add-Type -Path "C:\oracle\product\client\ODP.NET\bin\4\Oracle.DataAccess.dll" -ErrorAction Stop
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$connection = New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
$connection.Open()
$command = New-Object Oracle.DataAccess.Client.OracleCommand("SELECT SYSDATE FROM DUAL", $connection)
$result = $command.ExecuteScalar()
$stopwatch.Stop()
$connection.Close()
Write-Host "✓ Oracle connection successful" -ForegroundColor Green
Write-Host "Response time: $($stopwatch.ElapsedMilliseconds)ms" -ForegroundColor Gray
Write-Host "Server time: $result" -ForegroundColor Gray
}
catch {
Write-Host "✗ Oracle connection failed" -ForegroundColor Red
Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
if ($_.Exception.Message -like "*ORA-01017*") {
Write-Host "Hint: Check username/password" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*ORA-12541*") {
Write-Host "Hint: Check server name and port" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*ORA-12514*") {
Write-Host "Hint: Check service name" -ForegroundColor Yellow
}
}
}
#endregion
#region Simple Log Monitoring
function Watch-LIMSErrors {
param(
[string]$LogPath = "C:\inetpub\logs\LogFiles\W3SVC1",
[string[]]$ErrorPatterns = @("500", "403", "401", "timeout", "error", "exception", "fail")
)
$logFile = Get-ChildItem $LogPath -Filter "*.log" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
if (-not $logFile) {
Write-Error "No log files found in $LogPath"
return
}
Write-Host "Watching for LIMS errors in real-time..." -ForegroundColor Yellow
Write-Host "Log: $($logFile.FullName)" -ForegroundColor Cyan
Write-Host "Patterns: $($ErrorPatterns -join ', ')" -ForegroundColor Gray
Write-Host "Press Ctrl+C to stop`n" -ForegroundColor Gray
$alertCount = 0
Get-Content $logFile.FullName -Tail 0 -Wait | ForEach-Object {
$line = $_
$hasError = $false
foreach ($pattern in $ErrorPatterns) {
if ($line -like "*$pattern*") {
$hasError = $true
break
}
}
if ($hasError) {
$alertCount++
$timestamp = Get-Date -Format "HH:mm:ss"
Write-Host "[$timestamp] ALERT #$alertCount" -ForegroundColor Red -NoNewline
Write-Host " $line" -ForegroundColor White
# Save alerts to file
"[$timestamp] $line" | Add-Content "C:\temp\lims_alerts_$(Get-Date -Format 'yyyyMMdd').log"
}
}
}
function Get-LIMSRecentErrors {
param(
[string]$LogPath = "C:\inetpub\logs\LogFiles\W3SVC1",
[int]$LastMinutes = 30,
[string[]]$StatusCodes = @("4*", "5*")
)
$cutoffTime = (Get-Date).AddMinutes(-$LastMinutes)
$errors = @()
Get-ChildItem "$LogPath\*.log" | Where-Object { $_.LastWriteTime -gt $cutoffTime } | ForEach-Object {
$content = Get-Content $_.FullName
$fields = ($content | Where-Object { $_ -like "#Fields:*" } | Select-Object -First 1) -replace "#Fields: ", "" -split " "
$content | Where-Object { $_ -notlike "#*" -and $_ -ne "" } | ForEach-Object {
$values = $_ -split " "
if ($values.Length -ge $fields.Length) {
$entry = @{}
for ($i = 0; $i -lt $fields.Length; $i++) {
$entry[$fields[$i]] = $values[$i]
}
# Check if this matches our error criteria
foreach ($statusPattern in $StatusCodes) {
if ($entry['sc-status'] -like $statusPattern) {
$errors += [PSCustomObject]@{
Time = "$($entry['date']) $($entry['time'])"
Status = $entry['sc-status']
URL = $entry['cs-uri-stem']
ClientIP = $entry['c-ip']
UserAgent = $entry['cs(User-Agent)']
Referrer = $entry['cs(Referer)']
TimeTaken = $entry['time-taken']
}
break
}
}
}
}
}
Write-Host "LIMS Errors - Last $LastMinutes Minutes" -ForegroundColor Yellow
Write-Host "========================================" -ForegroundColor Yellow
if ($errors) {
Write-Host "Found $($errors.Count) errors:" -ForegroundColor Red
# Group by status code
$errors | Group-Object Status | Sort-Object Count -Descending | ForEach-Object {
Write-Host " HTTP $($_.Name): $($_.Count) occurrences" -ForegroundColor Cyan
}
Write-Host "`nMost recent errors:" -ForegroundColor Cyan
$errors | Sort-Object Time -Descending | Select-Object -First 10 | Format-Table Time, Status, URL, ClientIP -AutoSize
return $errors
} else {
Write-Host "No errors found in the last $LastMinutes minutes" -ForegroundColor Green
}
}
#endregion
Write-Host @"
QUICK START COMMANDS:
====================
1. Start ETW trace for troubleshooting:
Start-LIMSTrace
2. Stop ETW trace and analyze:
Stop-LIMSTrace
Analyze-LIMSTrace
3. Create Oracle health check script:
New-OracleHealthCheckScript
4. Test Oracle connection:
Test-OracleQuick -Server "dbserver" -ServiceName "ORCL" -Username "lims_user" -Password