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.
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.
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.
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.
What do you want the final output to look like?
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.