Extracting Data from Free text

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"

1 Reply

@FigureItOut 

If those are line breaks in the data, you could use this:

 

=LET(
    delim, "Secure Channel name: ",
    line_break, CHAR(10),
    TEXTBEFORE(TEXTAFTER(A1, delim), line_break)
)