Join Three data sources where one data source has a Multivalue field

[lpolo]

I have 3 data sources.

Data source 1 "Request": the Format of this data source is XML. The Fields are: id, MAC, time, request.

Data source 2 "Response": The Format of this data source is XML. The fields are: id, response. Where filed “response” is a multi-value field.

Data source 3 "Orders": the format of this data source is plain text and fields are separated with "|". The fields are: response, MAC, Price.

I have two queries that do the following:

The first query joins the request and response by id including the first value of the response. The result set of this query is stored in a temporal summary index name "test".

The second query joins the result set found in summary index "test" by response and mac. The result set is stored in a final summary index.

The queries are the following:

Query 1:
earliest=09/30/2011:00:00:00 latest= 10/01/2011:00:00:00
(sourcetype="request") OR (sourcetype="response"|
stats first(Time) as Time, first(MAC) as MAC, first(request) as request,
first(response) as response, dc(sourcetype) as dc by id|
where dc=2

Query 2:
earliest=09/30/2011:00:00:00 latest=10/01/2011:00:00:00
(index="test" sourcetype="stash") OR (sourcetype="orders")| stats
first(Time) as Time,
first(id) as id,
dc(sourcetype) as dc by response MAC |where dc=2

Due to the multi-value field "data_source_2.response" I need your help in order to be able to join the response values "second, third, fourth...nth" with data_source_3.response.
I tried to use mvindex as follow:

earliest=09/30/2011:00:00:00 latest= 10/01/2011:00:00:00
(sourcetype="request") OR (sourcetype="response"| eval response=mvindex(response,n)|
stats

first(Time) as Time,
first(MAC) as MAC,
first(request) as request,
first(response) as response,
dc(sourcetype) as dc by id|
where dc=2

The result set of every "response" is stored in the summary index "test". But splunk only allows me to store the first result set. After the second result set, it does not stored any value in the summary index. As a result, I cannot run the second query to obtain the data set I am looking for…..

Your help will be appreciated....

Regards,
Lp

[lpolo]

I was able to solve this issue by using the mvexpand command at the end of the search query. In this was, I was able to create an event for every response value found in a single event. This is an example:

earliest=09/30/2011:00:00:00 latest= 10/01/2011:00:00:00
(sourcetype="request") OR
(sourcetype="response")|
stats
first(Time) as Time,
first(MAC) as MAC,
first(request) as request,
values(response) as response,
dc(sourcetype) as dc by id|
where dc=2| mvexpand response