All Apps and Add-ons

I need to pivot on specific fields.

ragedsparrow
Contributor

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.

0 Karma
1 Solution

ragedsparrow
Contributor

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.

View solution in original post

0 Karma

ragedsparrow
Contributor

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

What do you want the final output to look like?

---
If this reply helps you, Karma would be appreciated.
0 Karma

ragedsparrow
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...