CODEWITHSUNDEEP
powershellpowershell-3.0
GURU SINGH
GURU SINGH

Reputation: 149

Match and filter Column in CSV for Desired output

I have a backup report in which I have multiple columns. I want to get only those clients who have never been successful in a particular group. If the same client with the same save set gets successful in other groups, then also I should get the client as failed for that particular group in which it is failing.

Get-Content E:\Report\Daily_Failed.csv |
    ConvertFrom-Csv |
    Select-Object -Unique * |
    Group-Object -Property 'Client Name','Save Set Name','Group' |
    Where-Object { $_.Count -eq 1 -and $_.Group.Status -eq 'Failed' } |
    Select-Object -Expand Group |
    Select-Object "Client Name", "Save Set Name", "status", "Group" |
    Export-Csv -NoTypeInformation E:\Report\Final_Failed.csv

Input

Client Name,Save Set Name,Group,Status
SQL01.abc.com,MSSQL, D_CWT_SQL_FULL,Failed
SQL01.abc.com,MSSQL:master,D_CWT_SQL_FULL, Success
SQL01.abc.com,MSSQL:model,D_CWT_SQL_FULL,Failed
SQL01.abc.com,MSSQL:msdb,D_CWT_SQL_FULL,Failed
SQL01.abc.com,MSSQL,SQL_T-Log_15Mins,Success
SQL01.abc.com,MSSQL:master,SQL_T-Log_15Mins,Failed
SQL01.abc.com,MSSQL:model,SQL_T-Log_15Mins,Success
SQL01.abc.com,MSSQL:msdb,SQL_T-Log_15Mins,Success
5001.abc.com,G:\System,CWT_FS,Failed
5001.abc.com,G:\System,CWT_FS,Success
5001.abc.com,H:\System,CWT_FS,Failed

Desired Output

Client Name Save Set Name   Group            Status
SQL01.abc.com   MSSQL       D_CWT_SQL_FULL    Failed
SQL01.abc.com   MSSQL:model D_CWT_SQL_FULL    Failed
SQL01.abc.com   MSSQL:msdb  D_CWT_SQL_FULL    Failed
SQL01.abc.com   MSSQL:masterSQL_T-Log_15Mins  Failed
5001.abc.com    H:\System   CWT_FS            Failed

I want to output for all clients to a particular group which are failing. I tried filtering out the column with status and save set but in that, I didn't get the clients which are failed in one group and are successful in other groups.

Upvotes: 0

Views: 62

Answers (1)

user6811411
user6811411

Reputation:

Your logic is faulty, if the sum of Status=Success per group is zero you want to keep it.

Get-Content .\Daily_Failed.csv |
    ConvertFrom-Csv |
    Select-Object -Unique * |
    Group-Object -Property 'Client Name','Save Set Name','Group' | 
    Where-Object {0 -eq ($_.Group|Where-Object Status -eq 'Success').Count} |
    Select-Object -Expand Group |
    Select-Object "Client Name", "Save Set Name", "status", "Group"

Just without the export this yields:

Client Name   Save Set Name Status Group
-----------   ------------- ------ -----
SQL01.abc.com MSSQL         Failed D_CWT_SQL_FULL
SQL01.abc.com MSSQL:model   Failed D_CWT_SQL_FULL
SQL01.abc.com MSSQL:msdb    Failed D_CWT_SQL_FULL
SQL01.abc.com MSSQL:master  Failed SQL_T-Log_15Mins
5001.abc.com  H:\System     Failed CWT_FS

Upvotes: 1

Related Questions