DBConnect and table lookup

[ericsix]

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/

[ggiessen]

Maybe a subquery in select will do?

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

[gregbujak]

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]`

[ericsix]

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

[gregbujak]

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