topic How to correlate across two lists in a stats in Splunk Search

How to correlate across two lists in a stats

t_splunk_d — Sat, 10 Jun 2023 18:59:38 GMT

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	-

Re: How to correlate across two lists in a stats

ITWhisperer — Sat, 10 Jun 2023 20:17:17 GMT

| 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")

Re: How to correlate across two lists in a stats

t_splunk_d — Sun, 11 Jun 2023 01:02:45 GMT

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?

Re: How to correlate across two lists in a stats

ITWhisperer — Sun, 11 Jun 2023 07:49:02 GMT

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

You could also add

| fillnull value="Pending" receivedTime fileReceived