Help related to sub-query

Greetings

I need some help building a query for the following requirement.

1. I have a file called atlassian-bitbucket-audit.log which contains the data of the following type

10.100.200.211 | RepositoryPushEvent | rchougule | 1597830212850 | SODI/dm-2017 | [{"ref":"refs/heads/SODI/WMOS-2017/PHASE1/DEV","from":"d21f1e950b9fa7165371d68f65795c4b5a88c444","to":"8a11ac9940c8d34427cfc7beb3b70de5712771b8"}] | @I4LE9Jx343x4552271x2 | -

2. I have another file called "atlassian-bitbucket-access-2020-08-19.1.log" which contains the data of the following type

10.100.200.211 | https | i@I4LE9Jx343x4552271x2 | - | 2020-08-19 05:43:32,530 | "POST /scm/sodi/dm-2017.git/git-receive-pack HTTP/1.1" | "" "git/1.8.1.msysgit.1" | - | - | - | - | - | - |

10.100.200.211 | https | o@I4LE9Jx343x4552271x2 | rchougule | 2020-08-19 05:43:32,858 | "POST /scm/sodi/dm-2017.git/git-receive-pack HTTP/1.1" | "" "git/1.8.1.msysgit.1" | 200 | 1472 | 266 | protocol:1, push | 328 | - |

3. Now, the requirement is to pick the identifier from first log which is I4LE9Jx343x4552271x2
4. Search I4LE9Jx343x4552271x2 in second log where it start with o@
5. Display the report which contains the following
a)User name from first log
b)Project SODI/dm-2017 form first log
c)Branch name SODI/WMOS-2017/PHASE1/DEV from first log
d)Second commit id 8a11ac9940c8d34427cfc7beb3b70de5712771b8 from first log
e)Date and Time 2020-08-19 05:43:32 from second log
f) provide report only for projects containing rnddf/df" or rndio/repl" or rndpfio/Toolkit where user name does not contain "cibuild"

Let me know if you need additional details.

Thanks !