CODEWITHSUNDEEP
powershellcsv
psnetserv
psnetserv

Reputation: 1

How to filter for specific words using power shell

I am trying to figure out who has been printing from a 2008 print server. I generated a log file from server manager and now have this information in a csv file. My goal is to parse through this info and export it to a new csv to find out the only the associates user ID, computer host name and printer name, all included in the csv log file so I can determine who is printing from this server and make sure I can map them to our new print server. The csv consists of one column of data which has a pattern.

Each row in the csv follows the below pattern, but the wording is different for each row since the job name is longer/shorter or other bits of information that I don't want.

The overall pattern is: 

Document #, job name owned by **user ID** on **computer host name** was printed on **printer name** through port ********

More information I don't want

My problem is that I can't hard code something like ignore the first 5 words of each row then the 6th word would be the user ID etc, since the format is different on each row.

What is the best way for me to ignore all words up until either the phrase "owned by", or even better, user ID, save that to a new csv in, then do the same thing for the computer host name and printer name?

Upvotes: 0

Views: 888

Answers (3)

Lance U. Matthews
Lance U. Matthews

Reputation: 16612

This is not an answer for how to extract information from the message text but rather how to avoid having to deal with formatted text in the first place. It appears you are trying to parse the message for Event Log events with ID 307. This code is adapted from PowerShell One-Liner to Audit Print Jobs on a Windows based Print Server.

Using the Get-WinEvent cmdlet you can query a specific log (Microsoft-Windows-PrintService/Operational) for specific events (307), and then it's just a matter of retrieving and adding a meaningful name to each property...

$eventFilter = @{
    LogName = 'Microsoft-Windows-PrintService/Operational';
    ID = 307;
}

Get-WinEvent -FilterHashTable $eventFilter `
    | Select-Object -Property `
        'TimeCreated', `
        @{ Name = 'JobId';       Expression = { $_.Properties[0].Value }; }, `
        @{ Name = 'JobName';     Expression = { $_.Properties[1].Value }; }, `
        @{ Name = 'UserName';    Expression = { $_.Properties[2].Value }; }, `
        @{ Name = 'MachineName'; Expression = { $_.Properties[3].Value }; }, `
        @{ Name = 'PrinterName'; Expression = { $_.Properties[4].Value }; }, `
        @{ Name = 'PortName';    Expression = { $_.Properties[5].Value }; }, `
        @{ Name = 'ByteCount';   Expression = { $_.Properties[6].Value }; }, `
        @{ Name = 'PageCount';   Expression = { $_.Properties[7].Value }; }

For an event with a message like this...

Document 1, Print Document owned by UserName on \\MachineName was printed on Microsoft Print to PDF through port X:\Directory\File.ext. Size in bytes: 12345. Pages printed: 1. No user action is required.

...the above code will output an object like this...

TimeCreated : 3/28/2019 5:36:41 PM
JobId       : 1
JobName     : Print Document
UserName    : UserName
MachineName : \\MachineName
PrinterName : Microsoft Print to PDF
PortName    : X:\Directory\File.ext
ByteCount   : 12345
PageCount   : 1

You could pipe the above command into Export-CSV to create your CSV file, or even just use Out-GridView to view and filter the data directly in PowerShell. Either way, no parsing necessary.

Upvotes: 0

AdminOfThings
AdminOfThings

Reputation: 25031

TheMadTechnician's Answer already covers a majority of this.

$a = Get-Content original.csv
$a[0] += ",Data"
$a | Set-Content updated.csv
$csv = Import-Csv updated.csv

$data = $csv.where({$_."Event ID" -eq 307}) | Select-String -pattern "(?<=owned by )(?<user>[^ ]+)(?: on )(?<host>.*?)(?: was printed on )(?<printer>.*?)(?= through )"

$(ForEach ($m in $data.matches) {
    [pscustomobject][ordered]@{"User"=$m.groups["user"].value
                "Host"=$m.groups["host"].value
                "Printer"=$m.groups["printer"].value
    }
}) | Export-Csv Output.csv -notypeinformation

There are some issues with the CSV that is exported from the Windows print server. If the numbered issues below do not matter in this case, then I can just remove this answer.

  1. The CSV column that contains the data you care about has no name. The other columns have headers, but for some reason this one does not. Without that header, your Import-Csv command will be useless. The first four lines of the code cover adding the Data header to that file. So you can either use code to fix that or just open the file, add the column name, and save.
  2. The event ID you care about is 307. There's a lot of other noise in that event log unless you pre-filtered it before saving it as a CSV, and that could impact the regex matching.

My method here is really no different than the other posted answer. I'm just matching less strings and access those matches with named indexes.

Upvotes: 1

TheMadTechnician
TheMadTechnician

Reputation: 36332

This could be done easily enough using Regular Expression matching. Regular Expressions use pattern matching, so you could do something like:

Get-Content LogFile.csv | Where{$_ -match "^(.*?),.+? owned by (.+?) on (.+?) was printed on (.+?) through port (.+)"} | ForEach{
    [PSCustomObject]@{
        JobNumber=$Matches[1]
        UserId=$Matches[2]
        ComputerName=$Matches[3]
        PrinterName=$Matches[4]
        Port=$Matches[5]
    }
}|Export-Csv NewLogFile.csv -NoTypeInfo

That would give you a CSV you could open in Excel or something with just the job number, user ID, the computer they used, the printer it went to, and the port it went on.

Upvotes: 1

Related Questions