doing sum of fields from powershell output

PowerShell Help
1

I am getting below output from powershell script to a variable, now i want to sum the fields “FailedCount=” , “SucceededCount=”, “TotalCount=” from below output and print on the console with a below format, how can i achieve it.

Total count SucceededCount FailedCount

57 50 7

--------------------------------------------below is the output from my powershell script------------------

value

{@{@odata.id=; CompletedDateSK=20210209; FailedCount=2; SucceededCount=14; PartiallySucceededCount=0; TotalCount=16}, @{@odata.id=; CompletedDateSK=20210210; FailedCount=5; SucceededCount=14;
PartiallySucceededCount=0; TotalCount=19}, @{@odata.id=; CompletedDateSK=20210211; FailedCount=1; SucceededCount=11; PartiallySucceededCount=0; TotalCount=12}, @{@odata.id=; CompletedDateSK=20210212;
FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210215; FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=;
CompletedDateSK=20210217; FailedCount=0; SucceededCount=3; PartiallySucceededCount=0; TotalCount=3}, @{@odata.id=; CompletedDateSK=20210218; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0;
TotalCount=2}, @{@odata.id=; CompletedDateSK=20210219; FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210222; FailedCount=0; SucceededCount=4;
PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210223; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=; CompletedDateSK=20210224;
FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210225; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=;
CompletedDateSK=20210226; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}}

2

I would go back to the powershell script that made this and either do the calculations there or provide yourself better structured output to work with. This is awful.

1 Like
3

Hi @rony1 , try my clunky regex:

$data = "{@{@odata.id=; CompletedDateSK=20210209; FailedCount=2; SucceededCount=14; PartiallySucceededCount=0; TotalCount=16}, @{@odata.id=; CompletedDateSK=20210210; FailedCount=5; SucceededCount=14;
PartiallySucceededCount=0; TotalCount=19}, @{@odata.id=; CompletedDateSK=20210211; FailedCount=1; SucceededCount=11; PartiallySucceededCount=0; TotalCount=12}, @{@odata.id=; CompletedDateSK=20210212;
FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210215; FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=;
CompletedDateSK=20210217; FailedCount=0; SucceededCount=3; PartiallySucceededCount=0; TotalCount=3}, @{@odata.id=; CompletedDateSK=20210218; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0;
TotalCount=2}, @{@odata.id=; CompletedDateSK=20210219; FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210222; FailedCount=0; SucceededCount=4;
PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210223; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=; CompletedDateSK=20210224;
FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210225; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=;
CompletedDateSK=20210226; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}}"

[regex]$dataRX = "(?<FailedCount>\bFailedCount=\d{1,2}\b);\s(?<SucceededCount>\bSucceededCount=\d{1,2}\b).*(?<TotalCount>\bTotalCount=\d{1,2}\b)"

$groups = $dataRX.Matches($data)

$output = foreach ($g in $groups) {
    [pscustomobject]@{
        FailedCount         = ($g.groups[1].value -split "=")[1]
        SucceededCount      = ($g.groups[2].value -split "=")[1]
        TotalCount               = ($g.groups[3].value -split "=")[1]
    }
}

$x = [pscustomobject]@{
    TotalFailed     = ($output.FailedCount | Measure-Object -Sum).Sum
    SucceededCount     = ($output.SucceededCount | Measure-Object -Sum).Sum
    TotalCount     = ($output.TotalCount | Measure-Object -Sum).Sum
}

TotalFailed SucceededCount TotalCount
----------- -------------- ----------
          3             38         42
1 Like
4

Hi Tluizzi,

Thanks a lot for the reply. The output of the script when i analyzed it is giving wrong results, if we see the above variable value, the word “TotalCount” is repeating 13 times, so the result of the “$output” variable should also contain 13 rows , but it is throwing back only 8 rows.

Also, in the end of the script we are doing the sum of FailedCount, SucceededCount and TotalCount, now how to include a additional colum named “Module name” with a value called “BESS” in it. The final output should look like below:

Module name TotalFailed SucceededCount TotalCount

BESS 3 38 42

5

Hello @rony1 , I cleaned up the regex:

$data = "{@{@odata.id=; CompletedDateSK=20210209; FailedCount=2; SucceededCount=14; PartiallySucceededCount=0; TotalCount=16}, @{@odata.id=; CompletedDateSK=20210210; FailedCount=5; SucceededCount=14;
    PartiallySucceededCount=0; TotalCount=19}, @{@odata.id=; CompletedDateSK=20210211; FailedCount=1; SucceededCount=11; PartiallySucceededCount=0; TotalCount=12}, @{@odata.id=; CompletedDateSK=20210212;
    FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210215; FailedCount=0; SucceededCount=4; PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=;
    CompletedDateSK=20210217; FailedCount=0; SucceededCount=3; PartiallySucceededCount=0; TotalCount=3}, @{@odata.id=; CompletedDateSK=20210218; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0;
    TotalCount=2}, @{@odata.id=; CompletedDateSK=20210219; FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210222; FailedCount=0; SucceededCount=4;
    PartiallySucceededCount=0; TotalCount=4}, @{@odata.id=; CompletedDateSK=20210223; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=; CompletedDateSK=20210224;
    FailedCount=0; SucceededCount=1; PartiallySucceededCount=0; TotalCount=1}, @{@odata.id=; CompletedDateSK=20210225; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}, @{@odata.id=;
    CompletedDateSK=20210226; FailedCount=0; SucceededCount=2; PartiallySucceededCount=0; TotalCount=2}}"

[regex]$CompletedDateRX             = "(?<CompletedDateSK>(?<=\bCompletedDateSK=\b)\d{8})"
[regex]$FailedCountRX               = "(?<FailedCount>(?<=\bFailedCount=\b)\d{1,2})"
[regex]$SucceededCountRX            = "(?<SucceededCount>(?<=\bSucceededCount=\b)\d{1,2})"
[regex]$TotalCountRX                = "(?<TotalCount>(?<=\bTotalCount=\b)\d{1,2})"

$CompletedDateGroups    = $CompletedDateRX.Matches($data)
$FailedCountGroups      = $FailedCountRX.Matches($data)
$SucceededCountGroups   = $SucceededCountRX.Matches($data)
$TotalCountGroups       = $TotalCountRX.Matches($data)

$output = for ($i = 0; $i -lt $FailedCountGroups.value.Count; $i++) {  
    [pscustomobject]@{
        ModuleName          = "BESS"
        CompletedDateSK     = $CompletedDateGroups.value[$i]
        FailedCount         = $FailedCountGroups.value[$i]
        SucceededCount      = $SucceededCountGroups.value[$i]
        TotalCount          = $TotalCountGroups.value[$i]
    }
}

$output | Format-Table


ModuleName CompletedDateSK FailedCount SucceededCount TotalCount
---------- --------------- ----------- -------------- ----------
BESS       20210209        2           14             16        
BESS       20210210        5           14             19
BESS       20210211        1           11             12        
BESS       20210212        0           4              4
BESS       20210215        0           4              4
BESS       20210217        0           3              3
BESS       20210218        0           2              2
BESS       20210219        0           1              1
BESS       20210222        0           4              4
BESS       20210223        0           2              2
BESS       20210224        0           1              1
BESS       20210225        0           2              2
BESS       20210226        0           2              2

Feel free to experiment with the Group-Object cmdlet or export this data to Excel or R.