All Apps and Add-ons

DBConnect and table lookup

ericsix
Explorer

I have dbconnect connecting to an oracle db pulling data from a ticketing system database. Works great. I have multiple db queries running and indexing the data into my indexers. Awesome.

Whats the best way to accomplish the following:

DB query 1- pulls multiple fields (around 90) including a SYSTEMUSER_ID ( data for this field looks like XY-1234567ABC)

DB query 2- pull table that maps SYSTEMUSER_ID to a USER_NAME. Table is just two fields ( XY-1234567ABC,user1)

Building my searches and reports on based on the data in query1, how can I replace the SYSTEMUSER_ID with the USER_NAME.. Aside from a separate SQL query..

/Thanks in advance/

0 Karma

ggiessen
Explorer

Maybe a subquery in select will do?

select ....... , (select USERNAME from table2 where .....) as USER, .... from table1

0 Karma

gregbujak
Path Finder

http://docs.splunk.com/Documentation/Splunk/5.0.3/SearchReference/Join

Your join would be on
events indexed from first table definition | join SYSTEMUSER_ID [search events indexed from mapping table]`

0 Karma

ericsix
Explorer

The join worked great as expected. However this doesnt help me mapping the USER_NAME field from DB query2 to the SYSTEMUSER_ID in query 1.

I guess I am approaching this wrong.. Whats the best way to lookup the SYSTEMUSER_ID field in query1 in query2, and return the matching value key?

Example- query 1 returns XY-12345. Search query2 table for XY-12345 and return USER_NAME back to searches with Query1.

TIA

0 Karma

gregbujak
Path Finder

It sounds to me like you are trying to create a join within Splunk itself and not at the db level? If thats correct, the documentation here: http://docs.splunk.com/Documentation/Splunk/5.0.3/SearchReference/Join
should do the trick nicely.

Your join would be on
events indexed from first table definition | join SYSTEMUSER_ID [search events indexed from mapping table

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...