Trying to JOIN data, or augment results.

[howyagoin]

Hi,

I'm likely going about my search in the wrong way, but I'm trying to create a table of data which draws upon a subsearch and a join in order to more completely represent the various values a given piece of data can have.

In one source of data, host "fubar" I have serial numbers for products purchased. I want to create a table which looks for particular serial numbers, and then finds the corresponding IP addresses that they were purchased from in another source.

To my thinking, a subsearch and join should have accomplished this, and it appears to, but only for a subset of the data, and I'm not sure why.

My search is something like this:

index=main source="iplog.2012.txt" successful recharge from [ search index=main host=fubar 77512345 OR 77512377   | fields MOO | rename MOO as query ] | rex field=_raw "IP: .(?\d+\.\d+\.\d+\.\d+)." | rex field=_raw moo: .(?\d+)." | join max=0 overwrite=false MOO [ search index=main source="oinkdata.txt" | fields MOO,OINK ] | table ip,MOO,OINK

In human terms, "For the purchases with the two provided ID numbers, find the MOO fields (which represent the part number in question) and look back through the IP logs for those part numbers and join with the OINK field (sales associate name) to create a table of which products were bought from what IP address and associated with which sales associate.

I've tried changing the join parameters a few times and in a few ways, but I'm missing something about the logic of what I'm doing, so I'm obviously going about it in the wrong way.

I just want to find all of the MOOs, then look for them in the IP log data, and create a nice table of information...

I need more coffee.

[lguinn2]

I think you have made this too hard. First, here is the search that will identify your MOO values

index=main source="iplog.2012.txt" successful recharge from  host=fubar 77512345 OR 77512377  | fields MOO

Second, here is the search that will identify your OINK values - given a fixed set of MOO values

index=main source="oinkdata.txt" MOO=a OR MOO=b or... | fields MOO,OINK  | table ip,MOO,OINK

SO the second search is the one that defines your output. Since the first search specifies part of the criteria for the second search, it is the first search that needs to become the subsearch. (In other words, "turn it around.")

Try it like this

index=main source="oinkdata.txt" 
 [search index=main source="iplog.2012.txt" successful recharge from  host=fubar 77512345 OR 77512377  
  earliest=-1d
  | fields MOO ]
| fields ip,MOO,OINK  
| table ip,MOO,OINK

Notice that the results of your subsearch simply become additional criteria for the "outer" search. In some ways, Splunk seems like SQL, but it is really different.

Finally, notice that I added "earliest=-1d" to your subsearch; otherwise there is no time boundary on the subsearch and it will search "all time". This will be slow and probably not what you want.

You might also find this useful, if you haven't seen it: Splunk for SQL Users

[fabiocaldas]

Thanks the anwser helped a lot !!

[howyagoin]

Thanks for the thoughts - unfortunately, part of the problem I (think) I have is that the fields are not named the same things in the various sources. In the iplog I have IP addresses, and the MOO field, but it's not called MOO, thus the rex. In the oinkdata, I have MOO, called MOO, and OINK, called OINK. I've been given a list of OINKs and want to get the OINK, MOO and ip - thus the fun. I'll poke a bit more!