I need to pivot on specific fields.

[ragedsparrow]

I have data coming in via DB Connect 2. In my query, I need to query 3 tables in an Oracle database, using a primary key to inner and outer join them. Here is an example of the query:

SELECT F.MC_ARRIVAL_TIME as "Time", D.*, F.PARAMETER_NAME, F.PARAMETER_VALUE, E.SLOT_NAME, E.SLOT_VALUE
FROM "SCHEMA1"."EVENT_D" D
INNER JOIN "SCHEMA1"."EVENT_INFO_F" F ON D.MC_UEID = F.MC_UEID
LEFT JOIN "SCHEMA1"."EVENT_EXTENDED_SLOTS_O" E ON D.MC_UEID = E.MC_UEID
WHERE D.UPDATE_TS > (select ((sysdate-2)- to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*360*10 from dual)
ORDER BY D.UPDATE_TS ASC

The issue comes with the Event_Extended_Slots_O. It has several SlotNames assigned to a unique MC_UEID. So the table looks like:

MC_UEID  |  SLOT_NAME  | SLOT_VALUE

1vs2          | Email                | ThisEmail@server.com
1vs2          | Phone              | 1234567897
2vs1          | Email                | ThatEmail@server.com
2vs1          | Phone               |  8974637829

This is resulting in 2 separate events for each MC_UEID. One for the Email Slot Name and one for the Phone Slot Name. Now, what I was previously doing was using a pivot in the Oracle query:

WITH qry AS (SELECT F.MC_ARRIVAL_TIME as "Time", D.*, F.PARAMETER_NAME, F.PARAMETER_VALUE, E.SLOT_NAME, E.SLOT_VALUE
 FROM "SCHEMA1"."EVENT_D" D
 INNER JOIN "SCHEMA1"."EVENT_INFO_F" F ON D.MC_UEID = F.MC_UEID
 LEFT JOIN "SCHEMA1"."EVENT_EXTENDED_SLOTS_O" E ON D.MC_UEID = E.MC_UEID
 WHERE D.UPDATE_TS > (select ((sysdate-2)- to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS'))*24*360*10 from dual)
 ORDER BY D.UPDATE_TS ASC)

 SELECT * FROM qry
pivot 
(
   MIN(SLOT_VALUE)
   for SLOT_NAME in ('Email' AS "EmailDestinations" ,'Phone' AS "Phone")
)

This was causing quite the overhead, so I was trying to figure out how this might be done in Splunk, but I'm kind of stumped. When I run the pivot, it turns the records in SLOT_NAME into a column and the SLOT_VALUE into the record inside that column for the row. When it is indexed, it creates a field for Email and a field for Phone. If I don't run the Pivot, that doesn't happen. What I'm wanting is a field for Email and a field for Phone in the same event, just as if the pivot query was running.

[ragedsparrow · Accepted solution]

I was able to work with my DBAs to get the performance of our Oracle DB to the point I could run my original query. I did however try the transaction command and while it did give me the slots in the same event, it also grouped everything else, which wasn't quite what I was hoping for. I have gotten to the ideal solution with the query above. Thank you all for your time in helping me sort through this.

[richgalloway]

Just a guess, but perhaps you could use the transaction command to combine events by MC_UEID. Then you'd have all slot names together.

[richgalloway]

What do you want the final output to look like?

[ragedsparrow]

I apologize, I have no clue why I didn't state it in the original question. When I run the pivot, it turns the values in SLOT_NAME into a column and the SLOT_VALUE into the record inside that column for the row. When it is indexed, it creates a field for Email and a field for Phone. If I don't run the Pivot, that doesn't happen. What I'm wanting is a field for Email and a field for Phone in the same event, just as if the pivot query was running.

Also, I've updated the original question with that additional information.