check if column matches any line in file with awk-CodePudding

say I have some output from the command openstack security group list:

 -------------------------------------- --------- ------------------------ ---------------------------------- ------ 
| ID                                   | Name    | Description            | Project                          | Tags |
 -------------------------------------- --------- ------------------------ ---------------------------------- ------ 
| 1dda8a57-fff4-4832-9bac-4e806992f19a | default | Default security group | 0ce266c801ae4611bb5744a642a01eda | []   |
| 2379d595-0fdc-479f-a211-68c83caa9d42 | default | Default security group | 602ad29db6304ec39dc253bcbba408a7 | []   |
| 431df666-a9ba-4643-a3a0-9a70c89e1c05 | tempest |      tempest test      | b320a32508a74829a0563078da3cba2e | []   |
| 5b54e63c-f2e5-4eda-b2b9-a7061d19695f | default | Default security group | 57e745b9612941709f664c58d93e4188 | []   |
| 6381ebaf-79fb-4a31-bc32-49e2fecb7651 | default | Default security group | f5c30c42f3d74b8989c0c806603611da | []   |
| 6cce5c94-c607-4224-9401-c2f920c986ef | default | Default security group | e3190b309f314ebb84dffe249009d9e9 | []   |
| 7402fdd3-0f1e-4eb1-a9cd-6896f1457567 | default | Default security group | d390b68f95c34cefb0fc942d4e0742f9 | []   |
| 76978603-545b-401d-9959-9574e907ec57 | default | Default security group | 3a7b5361e79f4914b09b022bcae7b44a | []   |
| 7705da1e-d01e-483d-ab82-c99fdb9eba9c | default | Default security group | 1da03b5e7ce24be38102bd9c8f99e914 | []   |
| 7fd52305-850c-4d9a-a5e9-0abfb267f773 | default | Default security group | 5b20d6b7dfab4bfbac0a1dd3eb6bf460 | []   |
| 82a38caa-8e7f-468f-a4bc-e60a8d4589a6 | default | Default security group | d544d2243caa4e1fa027cfdc38a4f43e | []   |
| a4a5eaba-5fc9-463a-8e09-6e28e5b42f80 | default | Default security group | 08efe6ec9b404119a76996907abc606b | []   |
| e7c531e3-cdc3-4b7c-bf32-934a2f2de3f1 | default | Default security group | 539c238bf0e84463b8639d0cb0278699 | []   |
| f96bf2e8-35fe-4612-8988-f489fd4c04e3 | default | Default security group | 2de96a1342ee42a7bcece37163b8dfa0 | []   |
 -------------------------------------- --------- ------------------------ ---------------------------------- ------

And I have a list of Project IDs:

0ce266c801ae4611bb5744a642a01eda
b320a32508a74829a0563078da3cba2e
57e745b9612941709f664c58d93e4188
f5c30c42f3d74b8989c0c806603611da
e3190b309f314ebb84dffe249009d9e9
d390b68f95c34cefb0fc942d4e0742f9
3a7b5361e79f4914b09b022bcae7b44a
5b20d6b7dfab4bfbac0a1dd3eb6bf460
d544d2243caa4e1fa027cfdc38a4f43e
08efe6ec9b404119a76996907abc606b
539c238bf0e84463b8639d0cb0278699
2de96a1342ee42a7bcece37163b8dfa0

which is the intersection of two files I get from runnning fgrep -x -f projects secgrup

how can I extract the rows from the ID column for which the Project column IDs match this list that I have?

It would be something like: openstack security group list | awk '$2 && $2!="ID" && $10 in $(fgrep -x -f projects secgrup) {print $2}'

which should yield:

1dda8a57-fff4-4832-9bac-4e806992f19a
431df666-a9ba-4643-a3a0-9a70c89e1c05
5b54e63c-f2e5-4eda-b2b9-a7061d19695f
6381ebaf-79fb-4a31-bc32-49e2fecb7651
6cce5c94-c607-4224-9401-c2f920c986ef
7402fdd3-0f1e-4eb1-a9cd-6896f1457567
76978603-545b-401d-9959-9574e907ec57
7fd52305-850c-4d9a-a5e9-0abfb267f773
82a38caa-8e7f-468f-a4bc-e60a8d4589a6
a4a5eaba-5fc9-463a-8e09-6e28e5b42f80
e7c531e3-cdc3-4b7c-bf32-934a2f2de3f1
f96bf2e8-35fe-4612-8988-f489fd4c04e3

but obviously this doesn't work.

CodePudding user response：

You can use this awk for this:

awk -F ' *\\| *' 'FNR == NR {arr[$1]; next}
$5 in arr {print $2}' projects secgrup

1dda8a57-fff4-4832-9bac-4e806992f19a
431df666-a9ba-4643-a3a0-9a70c89e1c05
5b54e63c-f2e5-4eda-b2b9-a7061d19695f
6381ebaf-79fb-4a31-bc32-49e2fecb7651
6cce5c94-c607-4224-9401-c2f920c986ef
7402fdd3-0f1e-4eb1-a9cd-6896f1457567
76978603-545b-401d-9959-9574e907ec57
7fd52305-850c-4d9a-a5e9-0abfb267f773
82a38caa-8e7f-468f-a4bc-e60a8d4589a6
a4a5eaba-5fc9-463a-8e09-6e28e5b42f80
e7c531e3-cdc3-4b7c-bf32-934a2f2de3f1
f96bf2e8-35fe-4612-8988-f489fd4c04e3

Here:

-F ' *\\| *' sets input field separator to | surrounded with 0 or more spaces on both sides.

CodePudding user response：

With your shown samples only, please try following awk code. Written and tested in GNU awk.

awk '
FNR==NR{
  arr1[$0]
  next
}
match($0,/.*default \| Default security group \| (\S )/,arr2) && (arr2[1] in arr1){
  print arr2[1]
}
' ids Input_file

Explanation:

Checking FNR==NR condition which will be TRUE when first Input_file named ids(where your ids are stored) is being read.
Then creating an array named arr1 is being created with index of current line.
next keyword will skip all further statements from here.
Then using match function with regex .*default \| Default security group \| (\S ) which will create 1 capturing group and share its value to array named arr2.
Then checking condition if arr2 value is present inside arr1 then print its value else do nothing.