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!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...