Get fields from different events in the same table

[cybernnal]

Hi,

I use Splunk to monitor ftp logs, but it passes through 2 server which has a different system of logs:
xml example (first logs):

<filename value="/ABC_00000_2000_01_01.zip" />
<destination value="C:\User\ABC_00000_2000_01_01.zip" />
<result success="true" />

text exemple (second logs):

2000-01-01 00:00:00,00 - Moving file: 'ABC_00000_2000_01_01.zip' to \\192.168.1.1\toto\titi 
move return code : 0

I want to follow-up the file from the original source to the final destination in a single table.
table example: source_origin tmp_destination final_destination

I have written 2 separate query that do what I want but I can't find how to run them in a single query and correlate event according to a field (the filename) to get complete tracking of a file on a single line.

part of my query:
xml query:

sourcetype=xml |
rex field=_raw "(?:filename value=\"(?<source_origin>[^\"]+)| destination value=\"(?<tmp_destination>[^\"]+))" | 
rex field=source_origin  "(?P<file_name>[^\/b\\\]*?)$" | 
table   file_name source_origin tmp_destination

text logs query:

sourcetype=log_try2 | 
rex field=_raw "(?:Moving file: \'(?<file_name>[^\']+))" | 
rex field=_raw "(?:to \\\\\\\(?<final_destination>[^ ]+))"  |  
table file_name final_destination

Thank you in advance for your answer, if something is not very clear do not hesitate to let me know 🙂 .

[DalJeanis · Accepted solution]

Not knowing which is which, but guessing that everything but the slash in the XML filename value= parameter is matched with the value in the log_try2 Moving file: parameter...

 index=foo sourcetype=xml OR sourcetype=log_try2 
| rex field=_raw "(?:filename value=\"(?<fieldX1>[^\"]+))" 
| rex field=src  "(?P<fieldX2>[^\/b\\\]*?)$" 
| rex field=_raw "(?:Moving file: \'(?<fieldY3>[^\']+))" 
| rex field=_raw "(?:to \\\\\\\(?<fieldY4>[^ ]+))"  
| table fieldX1 fieldX2 fieldY3 fieldY4
| rename COMMENT as "The above pulls and formats any data that is there, leaving missing data as NULL"

| rename COMMENT as "Now we build a log_try2 key for the xml record and roll together the two kinds of information."
| eval fieldY3=coalesce(fieldY3,substr(fieldX1,2,len(fieldX1)-1))
| stats values(*) as * by fieldY3

| rename COMMENT as "Now we guess what the desired names are..."
| rename fieldX1 as source_origin
| rename fieldX2 as tmp_destination
| rename fieldY3 as src_temp
| rename fieldY4 as dest_final

[cybernnal]

I don't understand how but that work perfectly.
Thanks you very much!!!
The coalesce will do all works, no?

[DalJeanis]

Yes, the coalesce pulls data off the XML record to link it to the equivalent log_try2 data.

Okay, if you don't understand it but really WANT to, then do this and compare the results.

  index=foo sourcetype=xml 
 | head 1
 | rex field=_raw "(?:filename value=\"(?<fieldX1>[^\"]+))" 
 | rex field=src  "(?P<fieldX2>[^\/b\\\]*?)$" 
 | rex field=_raw "(?:Moving file: \'(?<fieldY3>[^\']+))" 
 | rex field=_raw "(?:to \\\\\\\(?<fieldY4>[^ ]+))"  
 | table fieldX1 fieldX2 fieldY3 fieldY4

  index=foo sourcetype=log_try2 
 | head 1
 | rex field=_raw "(?:filename value=\"(?<fieldX1>[^\"]+))" 
 | rex field=src  "(?P<fieldX2>[^\/b\\\]*?)$" 
 | rex field=_raw "(?:Moving file: \'(?<fieldY3>[^\']+))" 
 | rex field=_raw "(?:to \\\\\\\(?<fieldY4>[^ ]+))"  
 | table fieldX1 fieldX2 fieldY3 fieldY4

Then take the fieldY3 value you got and do this and see the two results...

  index=foo sourcetype=xml OR sourcetype=log_try2  "ABC_00000_2000_01_01.zip" 
 | rex field=_raw "(?:filename value=\"(?<fieldX1>[^\"]+))" 
 | rex field=src  "(?P<fieldX2>[^\/b\\\]*?)$" 
 | rex field=_raw "(?:Moving file: \'(?<fieldY3>[^\']+))" 
 | rex field=_raw "(?:to \\\\\\\(?<fieldY4>[^ ]+))"  
 | table fieldX1 fieldX2 fieldY3 fieldY4
 | rename COMMENT as "The above pulls and formats any data that is there, leaving missing data as NULL"

Then add one line at a time for the next two lines and see how each transforms the data.

[cybernnal]

Ho really thx, for your time and your explication, that's now more clear for me!

[cybernnal]

Sorry for the mistakes in the post I believe it's now good.
Thank you For your answer, I do not have the opportunity to test now but I will do it tomorrow 🙂 .

[DalJeanis]

1) Your code has dropped the actual pull names .
2) If you include in your examples the values of file and source_origin, src_tmp and dest_final, and if you use the same names in the table command at the end of each example that you use in your request, then it will be clearer.

[cybernnal]

thx, i didn't see, it seems to be better