Trying to use Powershell to import information from a csv but unfortunately some of the results come out wrong

Question

$AuditSuccess = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Success" } | Measure-Object | Select-Object count 

$AuditFailure = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Failure" } | Measure-Object | Select-Object count

$AuditTotal = $AuditSuccess + $AuditFailure

$EventID1 = Import-Csv -Path G:\LabLog.csv | sort | group Keywords | sort $_.EventID | select EventID -last 1

$EventID2 = Import-Csv -Path G:\LabLog.csv | sort | group Keywords | sort $_.EventID | select EventID -last 1


Write-Host "Number of Audit Failures:" $AuditFailure "failures of" $AuditTotal "entries"
Write-Host "Most Common Event ID:" $EventID1
Write-Host "Number of Audit Successes:" $AuditSuccess "successes of" $AuditTotal "entries"
Write-Host "Most Common Event ID:" $EventID2 

I'm fairly new to Powershell and attempting to use it for an assignment I need to import a csv log and then draw out specific information from it in this case the number of failures and successes out of all the logs and the most common event ID from the failures and from the successes.

The AuditFailure and AuditSuccess sections of the of the code are working somewhat although the results come out as {count = ##} as opposed to just numbers. The real issue is with the AuditTotal and the EventID which either aren't producing any result in the case of the total or giving a result that's blank in the case of the EventID.

I don't know if these are the best commands to use for this and am open to any help in figuring this out.

Method invocation failed because [System.Management.Automation.PSObject] does 
not contain a method named 'op_Addition'.
At line:5 char:1
+ $AuditTotal = $AuditSuccess + $AuditFailure
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (op_Addition:String) [], 
RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

Number of Audit Failures: @{Count=13} failures of  entries
Most Common Event ID: @{EventID=}
Number of Audit Successes: @{Count=6480} successes of  entries
Most Common Event ID: @{EventID=}

Sorry this is the error output

Number of Audit Failures: 2469 failures of 19247 entries                               
  Most common Event ID: 5038     
Number of Audit Successes: 16778 successes of 19247 entries                               
  Most common Event ID: 4624

This is what it should look like although the numbers are meant to be different

Answer 1

here's another way that avoids re-reading the CSV file so many times. it also avoids sending things thru the pipeline quite so often. [grin]

# fake reading in a CSV file
#    in real life, use Import-CSV
$InStuff = @'
EventID, Keywords
1001, Audit Success; SomeOtherWord
1001, Audit Success
2002, NothingRightNow
3003, Audit Failure
4004, Audit Success
5005, IgnoreThisOne
6006, Audit Success
7007, Audit Failure
7007, Audit Failure
'@ | ConvertFrom-Csv

$SuccessList = $InStuff.Where({$_.Keywords -match 'success'})
$SuccessCount = $SuccessList.Count
$SL_MostFrequentEventID = ($SuccessList |
    Group-Object -Property EventID |
    Sort-Object -Property Count)[-1].Name


$FailureList = $InStuff.Where({$_.Keywords -match 'failure'})
$FailureCount = $FailureList.Count
$FL_MostFrequentEventID = ($FailureList |
    Group-Object -Property EventID |
    Sort-Object -Property Count)[-1].Name

$FS_TotalCount = $FailureCount + $SuccessCount

Write-Host ''
Write-Host ('Number of Audit Failures {0} out of {1} entries.' -f $FailureCount, $FS_TotalCount)
Write-Host ('    Most Common Failure Event ID = {0}' -f $FL_MostFrequentEventID)
Write-Host ('Number of Audit Successes {0} out of {1} entries.' -f $SuccessCount, $FS_TotalCount)
Write-Host ('    Most Common Success Event ID = {0}' -f $SL_MostFrequentEventID)

output ...

Number of Audit Failures 3 out of 7 entries.
    Most Common Failure Event ID = 7007
Number of Audit Successes 4 out of 7 entries.
    Most Common Success Event ID = 1001

Answer 2

There are ultimately several problems; let's start with the primary one:

$AuditTotal = $AuditSuccess + $AuditFailure

caused the[System.Management.Automation.PSObject] does not contain a method named 'op_Addition' error, because $AuditSuccess and $AuditFailure, whose values were assigned with Select-Object count, are custom objects, not numbers - and the + operator (which translates to the op_Addition method) isn't defined for custom-object operands (operands of type [pscustomobject]).

To use Select-Object to extract a single property value, you must use -ExpandProperty; e.g.:

$AuditSuccess = ... | Measure-Object | Select-Object -ExpandProperty Count

Without -ExpandProperty, with the (implied) -Property parameter, you get a [pscustomobject] instance that has a single property, .Count.

---

The next problem is that sort $_.EventID won't work as intended, because automatic variable $_ only has a (meaningful) value in script blocks; without a predefined $_ value (there shouldn't be any), $_.EventId evaluates to $null and is effectively ignored.

While Sort-Object EventId would normally be the correct solution (just providing the property name), in this case the input objects do not have an .EventId property, because what group (Group-Object) outputs are [Microsoft.PowerShell.Commands.GroupInfo] instances each representing a group of original input objects.

Based on your intent to find the most frequently occurring event ID among the success and failures, the commands must be restructured as follows, using the successes as an example:

$EventID1 = Import-Csv -Path G:\LabLog.csv | 
  Where-Object { $_.Keywords -like "Audit Success" } | 
    Group-Object EventID |
      Sort-Object Count -Descending |
        Select-Object -ExpandProperty Values -First 1

That is, the input objects are first filtered by keyword, then grouped by event ID, then sorted by the count of objects in each event-ID group, with the event ID of the group with the most entries getting returned.

Finally:

• There's a lot of duplicated effort in your code, most notably calling Import-Csv on the same input file repeatedly, which slows it down.

• Write-Host is generally the wrong tool to use, unless the intent is explicitly to write to the display only, bypassing PowerShell's output streams.

Answer 3

$AuditSuccess = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Success" } | Measure-Object | Select-Object -ExpandProperty count 

$AuditFailure = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Failure" } | Measure-Object | Select-Object -ExpandProperty count

$AuditTotal = $AuditSuccess + $AuditFailure

$EventID1 = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Success" } | sort $_.EventID | Select-Object -ExpandProperty EventID -last 1

$EventID2 = Import-Csv -Path G:\LabLog.csv | Where-Object { $_.Keywords -like "Audit Failure" } | sort $_.EventID | Select-Object -ExpandProperty EventID -last 1


Write-Host "Number of Audit Failures:" $AuditFailure "failures of" $AuditTotal "entries"
Write-Host "Most Common Event ID:" $EventID1
Write-Host "Number of Audit Successes:" $AuditSuccess "successes of" $AuditTotal "entries"
Write-Host "Most Common Event ID:" $EventID2 

Thanks for the help here's the answer.