awk to search a pattern and print lines with pattern and one more condition

I have a input file as shown below:

SR  policy name    state             error code
1   backup01       successful         0
2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

I want to have an output where if any line for a particular policy (example backup01 has a line with "fail" state, it will only show "fail" state lines and will not show the "success" lines. Similarly where all lines of a policy (example backup02) have a state "successful" it will print all the "successful" state lines.

2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

I have tried using awk with little success, but not able to get ahead with a final solution.

awk '{if ($4 == 0) {print $0} else if($4 !=0 && $4 == 0) {print $0}}' input_file.txt

any other way using sed or other tool is also fine. ALso header from input file can also be ignored.

I would harness GNU AWK following way, let file.txt content be

SR  policy name    state             error code
1   backup01       successful         0
2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

then

awk 'NR>1{arr[$2][$3]=arr[$2][$3] RS $0}END{for(i in arr){print substr(arr[i]["fail" in arr[i]?"fail":"successful"],2)}}' file.txt

gives output

2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

Explanation: for lines beyond 1st I populate 2D array, with first key being policy name and second state, I append whole line using row separator so values are lines separated by newlines. When file is processed I go through said array and print what is under fail if it is is available for given policy name else what is under successful. I start printing at 2nd character due to leading newline which appear due to way I populate array. Disclaimer: this solution assumes you are accepting any order of lines in output, if this does not hold do not use it.

(tested in GNU Awk 5.1.0)

One awk idea:

awk '
NR>1 { # keep track of "fail" and "successful" entries in different arrays

       if ($3 == "fail"       ) fail[$2]    = fail[$2]    (fail[$2]    == "" ? "" : ORS) $0
       if ($3 == "successful" ) success[$2] = success[$2] (success[$2] == "" ? "" : ORS) $0
     }
END  { for (state in fail) {                # loop through fail[] array indices ...
           print fail[state]                # print the fail[] entry and ...
           delete success[state]            # delete any success[] entry for the same state
       }
       for (state in success)               # loop through remaining success[] array indices ...
           print success[state]             # print the success[] entry
     }
' input_file.txt

This generates:

2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

NOTES:

• while all lines for a given state will be successive, this solution does not guarantee the output will be ordered by state
• if OP needs the output ordered by state one approach would be to pipe the output to sort (eg, awk '<see_script_above>' input_file.txt | sort -k2,2)

NOTE: original answer before OP provided additional details ...

Assumptions:

• file is already sorted by the policy name (2nd) column
• if the state (2nd) column contains any values other than fail or successful then the associated line is ignored

One awk idea requiring a single pass through the data file:

awk '
function print_lines() {
         if ( fail    != "" ) print fail
    else if ( success != "" ) print success
    fail = success = ""
}

NR>1 { if (prev != $2) print_lines()          # print previous policy
       prev = $2
       if ($3 == "fail"       ) fail    = fail    (fail    == "" ? "" : ORS) $0
       if ($3 == "successful" ) success = success (success == "" ? "" : ORS) $0
     }
END  { print_lines() }                        # print last policy
' input_file.txt

This generates:

2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

It can be done in two-stage processing of the same file. First pass, build up the status of the policy if it is going to be a "fail" or a "successful" and then exclude the contradicting lines in the next pass

awk '
  FNR == NR && NR > 1 { 
    t = key[$2]
    if ( $3 == "fail" || t == "fail" ) { 
      key[$2] = "fail" 
    } else {
      key[$2] = $3
    }
    next 
  } 
  key[$2] == $3
' file file

Here is a single pass awk to solve the problem:

awk '
NR == 1 {
   print
   next
}
!fail {
   fail = ($3 == "fail")
}
fail {
   gsub(/[^\n]+ successful [^\n]+\n/, "", s)
}
p != $2 {
   printf "%s", s
   s = fail = ""
}
{
   s = s $0 ORS
   p = $2
}
END {
   printf "%s", s
}' file

SR  policy name    state             error code
2   backup01       fail               13
3   backup01       fail               58
4   backup02       successful         0
5   backup02       successful         0
6   backup02       successful         0

This might work for you (GNU sed):

sed -E '1d;:a;$!{N;/^(\S+\s+){2}.*[^\n]+$/ba};$!{h;s/\n[^\n]+$//}
        /fail/{s/.*successful.*//mg;s/(^\n|(\n))\n*//gp}
        /successful/p;$d;x;s/^.*\n//;ba' file

Ditch the header.

Continue to fetch lines until the end-of-file or a change in the second field.

If it is not the end-of-file, make a copy and remove the last line (the change in the second field).

If the lines contain fail, then remove any lines (and their linefeeds) that contain the word successful and print the result.

Otherwise, print all the lines as they are successful

If it end-of-file, no further processing is needed.

Otherwise, swap to the copy and remove all the lines other that the last (lines already processed) and repeat.

N.B. This expects the second field i.e. the key, to be in sorted order.