How to correlate across two lists in a stats

[t_splunk_d]

I want to correlate across two lists and display the results.

Log data:

06/10/2023 05:04:12  ACMIUY-6500-2345-20230610050412.pdf
06/10/2023 05:05:12  ACMIUY-6500-2345-20230610050412-success.csv

06/10/2023 07:14:22 DCCOUB-86895-20230610071422.pdf

Note: The data is in the same index and I don't want to use join

mysearch | stats list(file_sent) list(file_received) list(sent_time) list(received_time)

Sent Time	File_Sent	Received Time	File_Received	Elapsed_time
06/10/2023 05:04:12	ACMIUY-6500-2345-20230610050412.pdf	06/10/2023 05:05:12	ACMIUY-6500-2345-20230610050412-success.csv	00:01:00
06/10/2023 07:14:22	DCCOUB-86895-20230610071422.pdf	Pending	Pending	-

[t_splunk_d]

I also see for some file received the file name is:
PD0018MM-220385-20230609211505.20230609211740-success.csv 

How to modify so that the filename is extracted as PD0018MM-220385-20230609211505

| rex field=fullfilename "(?<filename>\S+)(?<event>\.pdf|\-success\.csv)$"

Also how display "Pending" if no corresponding file_received for the file_sent?

[ITWhisperer]

| rex field=fullfilename "(?<filename>[^\.\s]+)(?<event>\.pdf|\-success\.csv)$"

You could also add

| fillnull value="Pending" receivedTime fileReceived

[ITWhisperer · Accepted solution]

| rex "(?<fullfilename>\S+)$"
| rex field=fullfilename "(?<filename>\S+)(?<event>\.pdf|\-success\.csv)$"
| eval sentTime=if(event=".pdf",_time,null())
| eval receivedTime=if(event=".pdf",null(),_time)
| eval fileSent=if(event=".pdf",fullfilename,null())
| eval fileReceived=if(event=".pdf",null(),fullfilename)
| stats values(sentTime) as sentTime values(fileSent) as fileSent values(receivedTime) as receivedTime values(fileReceived) as fileReceived by filename
| eval elapseTime=tostring(receivedTime-sentTime,"duration")