Extracting Excel Free Text Using PowerShell

Copper Contributor

Greetings,

We are trying to extract server names from our event logs that we exported to Excel. The following is an example of a cell which contains free text  

"Domain Controller Blocked Audit: Audit NTLM authentication to this domain controller.
Secure Channel name: SERVERNAME
User name: email address removed for privacy reasons
Domain name: NULL
Workstation name: Contosodesktop
Secure Channel type: 2"

 

We are trying to extract the values (server names) after the "Secure Channel Name:". I tried the following powershell script and it does not extract anything. Does anyone have thoughts? 

 

# Import the ImportExcel module
Import-Module ImportExcel

# Path to your Excel file
$excelFilePath = "C:\temp\contoso.csv"

# Output text file path
$outputFilePath = "C:\temp\extracted_server_names.txt"

# Regular expression pattern to match the server name
$pattern = "Secure Channel name:\s*(.*)"

# Initialize an empty array to store extracted server names
$serverNames = @()

# Read the Excel file
$data = Import-Excel -Path $excelFilePath

# Iterate through each cell in the Excel file
foreach ($row in $data) {
foreach ($cell in $row.Values) {
Write-Host "Processing cell: $cell"
# Check if the cell is a string and contains the desired text
if ($cell -is [string] -and $cell -match $pattern) {
# Extract the server name using regular expression
$serverName = $Matches[1].Trim()
# Append the extracted server name to the list
$serverNames += $serverName
Write-Host "Extracted server name: $serverName"
}
}
}

# Write the extracted server names to a text file
$serverNames | Out-File -FilePath $outputFilePath -Encoding utf8

# Display message indicating where the results are saved
Write-Host "Extracted server names saved to: $outputFilePath"

2 Replies

@FigureItOut 

 

Can you verify on $pattern = "Secure Channel name:\s*(.*)"?

Hi,
Thank you for your response. Can you clarify what you mean?