CODEWITHSUNDEEP
powershellcsvpowershell-remoting
MattP
MattP

Reputation: 13

How to search a text file for string and perform a lookup using results and the contents of a CSV file?

I've been using a PowerShell script that reads a file and extracts error codes. It quick, simple and does the job that I want it to but I've now been asked to share it with a wide audience so I need to make it a bit more robust.

The problem I've got is that I need to take the output from my script and use it to lookup against a CSV file so that I get a user friendly table at the end that lists:

  1. A count of the how many time each error occurred (in descending order)
  2. The Error code
  3. The corresponding error message that it displays to the end user

This is the line format in the source file, there's normally upwards on 2000 lines

17-12-2016,10:17:44:487{String=ERROR->(12345678)<inData:{device=printer, formName=blah.frm, eject=FALSE, operation=readForm}><outData:{fields=0, CODE=, field1=}> <outError:{CODE=Error103102, extendedErrorCode=-1, VARS=0}>}

This is my current script:

$WS = Read-Host "Enter computer name"
$date = Read-host "Enter Date"

# Search pattern for select-string (date always at the beginning of the line and the error code somewhere further in)
$Pattern = $date+".*<outError:{CODE="

# This find the lines in the files that contain the search pattern
$lines = select-string -path "\\$WS\c$\folder\folder\file.dat" -pattern $Pattern

# This is the specific Error code pattern that I'm looking for in each line
$regex = [regex] 'Error\d{1,6}'
$Var = @()

# Loops through each line and extracts Error code
foreach ($line in $lines) { $a = $line -match $regex 
# Adds each match to variable
$Var += $matches.Values

 }

# Groups and sorts results in to easy to read format 
$Var | group | Sort-Object -Property count -descending

And this is the result it gives me:

Count Name                      Group
----- ----                      -----
   24 Error106013                {Error106013, Error106013, Error106013, Error106013...}
   14 Error106109                {Error106109, Error106109, Error106109, Error106109...}
   12 Error203002                {Error203002, Error203002, Error203002, Error203002...}

The CSV that I need to lookup against is as simple as it gets, with just 2 values per line in the format:

Code,Error message

What I need to get to is something like this:

Count Name                      Error Message
----- ----                      -----
   24 Error106013                Error:blah
   14 Error106109                Error:blah,blah
   12 Error203002                Error:blah,blah,balh

Google has failed me so I'm hoping that there is someone out there that can at the least point me in the right direction.

Upvotes: 1

Views: 1412

Answers (1)

Martin Brandl
Martin Brandl

Reputation: 58931

Not tested but it should work with a simple calculated property - just replace the last line with:

$errorMap = Import-Csv 'your_errorcode.csv'
$Var | Group-Object | Sort-Object -Property count -descending | 
    Select-Object Count, Name, @{l='Error Message'; e={($errorMap | Where-Object Code -eq $_.Name)."Error message"}}

Note: You also have to replace path to your CSV.

Upvotes: 1

Related Questions