Splunk Search

How to correlate across two lists in a stats

t_splunk_d
Path Finder

 

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 TimeFile_ReceivedElapsed_time
06/10/2023 05:04:12ACMIUY-6500-2345-20230610050412.pdf06/10/2023 05:05:12ACMIUY-6500-2345-20230610050412-success.csv00:01:00
06/10/2023 07:14:22DCCOUB-86895-20230610071422.pdfPendingPending-

 

 

Labels (1)
0 Karma
1 Solution

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

View solution in original post

t_splunk_d
Path Finder

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?

0 Karma

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

You could also add

| fillnull value="Pending" receivedTime fileReceived

ITWhisperer
SplunkTrust
SplunkTrust
| 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")
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...