All Apps and Add-ons

DBConnect and table lookup

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

Explorer

Maybe a subquery in select will do?

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

0 Karma

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

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

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!