JOIN should return multiple rows. How make final results show that data?

[pgifford]

My driver file has one row per key. The subsearch file can contain multiple rows for each key. I need my result set rows to contain the driver file data and the subsearch data. Currently, I am getting only one row per driver file key. I need for those to be able to be more (one input row to be able to produce a row for each subsearch hit). I cannot swap around the driver for the subsearch.

I don't know if the subsearch is stopping looking after the first hit, or the output processing is dropping all but the first match. (I hope it is the latter.)

Example (Simplified -- I could have 300+ matching rows from the subsearch):

Driver file:                                  Subsearch file:   
   key     fielda   fieldb                       key     fieldc fieldd
   Key-1     A1       B1                         Key-1    C1      D1
                                                 Key-1    C2      D2
   Key-2     A2       B2                         Key-2    C3      D3

Result is:

   key      fielda  fieldb      fieldc   fieldd
   Key-1     A1       B1          C1       D1
   Key-2     A2       B2          C3       D3

Result should be:

   key      fielda  fieldb      fieldc   fieldd
   Key-1     A1       B1          C1       D1
   Key-1     A1       B1          C2       D2  
   Key-2     A2       B2          C3       D3

The JOIN is very basic.
{btw, outer JOINS do not exist in SPLUNK -- they are left joins per the documentation and my experience -- a real headache}:

| join A_Issue_Key type=left 
    [ | inputlookup Reference_Table.csv 
        | fields A_Issue_Type, A_Issue_Key, B_Test_Count, B_Test, B_Test_Execution] 
| eval Total_TC=6, Deferred=2, Passed=2, Failed=2  ]

thanks!

http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Join

[cmerriman]

I'm going to expand on @DalJeanis 's answer, as I believe it will get you close to what you're looking for. You'll want to start with your reference table lookup, since it likely has more data in it. Then use append to join the driver lookup file to the bottom of the results with any common fields. tie them all together with a stats command and the fields you want to see. you can use a nomv or mvexpand afterwards if you want to split the multi-valued fields.

 | inputlookup Reference_Table.csv 
  | fields A_Issue_Type, A_Issue_Key, B_Test_Count, B_Test, B_Test_Execution, fieldc, fieldd
  | append 
        [| inputlookup Driver_Table.csv 
         | table A_Issue_Key, fielda, fieldb ]
  | stats values(fielda) as fielda values(fieldb) as fieldb values(fieldc) as fieldc values(fieldd) as fieldd by A_Issue_Key

[DalJeanis]

@pgifford - We just marked your code and data for you. The code button (101 010), putting at least four spaces before a line, or marking text with the grave accent before and after ("`") will keep the layout.

[DalJeanis]

Here's another way to do it...

 | inputlookup Reference_Table.csv 
 | fields A_Issue_Type, A_Issue_Key, B_Test_Count, B_Test, B_Test_Execution

 | rename COMMENT as "Add the driver data with append, but mark it for deletion before mixing it in"
 | append 
       [| inputlookup mydriverfile.csv ... or some other search to get your driver...
        | table key, fielda, fieldb 
        | rename key as A_Issue_Key 
        | eval killme="driver"]

 | rename COMMENT as "Roll the data from the driver file onto the other records, then kill the driver data"
 | eventstats value(fielda) as fielda values(fieldb) as fieldb by A_Issue_Key
 | where isnull(killme)

[pgifford]

That eval statement in the JOIN example should not be there -- cut n paste error. Sorry it make it more confusing.

So pretend this is not there: | eval Total_TC=6, Deferred=2, Passed=2, Failed=2 ]

[pgifford]

Well, I just added max=0 may fix it...sigh