Splunk DB Connect 2.1.3: Why am I unable to produce expected results with a database lookup?

[AlexeyNL]

Hi, all!

Using Splunk DB Connect 2, I have created a DB Lookup definition following this steps, but I haven't been successful with a manual lookup. This search:

index=oracle_audit_trail | lookup db_connect_first_lookup PersonnelNumber as PersonnelNumber | table USERNAME,PersonnelNumber

do not work: values of the PersonnalNumber field are empty.
Furthermore, the lookup command doesn't produce the field PersonnelNumber. I see this using following commands:

index=oracle_audit_trail | lookup db_connect_first_lookup PersonnelNumber as PersonnelNumber | table *

There is no field with the name PersonnelNumber.

In dbx2.log and health.log I find "Illegal group reference: group index is missing" errors, but I'm not sure that they point to the cause of the problem. Anyway, I don't know how to resolve them.

04/26/2016 14:37:01 [CRITICAL] [mi_lookup.py] Executing db lookup [mi_lookup://first_lookup] with error = java.lang.IllegalArgumentException: Illegal group reference: group index is missing.
04/26/2016 14:37:01 [CRITICAL] [mi_base.py] Reach 6 maximum failed retries in modular input mi_lookup://first_lookup. Disabling modular input.
2016-04-26 14:34:53 INFO  HealthLogger:193 - DB_JDBC_URL=jdbc:sqlserver://test-sql-server:55818;databaseName=am;selectMethod=cursor FREE_MEMORY=60513976 FUNCTION=loadLookups LABEL=JP MAX_MEMORY=1065025536 MESSAGE="Illegal group reference: group index is missing" PROTOCOL=HTTP SQL="SELECT PersonnelNumber,UserID FROM dbo.TestLookupTable" STATE=error TOTAL_MEMORY=150433792 TRANS_OBJECT_ID=43273cbb-0861-4acb-baf5-07e42e016aec UPTIME=19724533 task_id=84a2f8e0-5965-4992-b73c-5bb69785578d
2016-04-26 14:34:53 INFO  HealthLogger:193 - DB_JDBC_URL=jdbc:sqlserver://test-sql-server:55818;databaseName=am;selectMethod=cursor FREE_MEMORY=60513976 FUNCTION=loadLookups LABEL=JP MAX_MEMORY=1065025536 MESSAGE="Illegal group reference: group index is missing" PROTOCOL=HTTP SQL="SELECT PersonnelNumber,UserID FROM dbo.TestLookupTable" STATE=error TOTAL_MEMORY=150433792 TRANS_OBJECT_ID=43273cbb-0861-4acb-baf5-07e42e016aec UPTIME=19724532 task_id=84a2f8e0-5965-4992-b73c-5bb69785578d

Any help will be appreciated. Thanks in advance.

[AlexeyNL · Accepted solution]

So i have finally found the reasons of described problems.

First.

My lookup command was incorrect

index=oracle_audit_trail | lookup db_connect_first_lookup PersonnelNumber as PersonnelNumber | table USERNAME,PersonnelNumber

I had to specify input fields in that place where output fields was specified. In my case input db field is UserID and input event field is USERNAME So the correct query is:

index=oracle_audit_trail | lookup db_connect_first_lookup UserID as USERNAME | table USERNAME,PersonnelNumber

Second.

"Script for lookup table 'first_lookup' returned error code 1. Results may be incorrect." search running error

and

"java.lang.IllegalArgumentException: Illegal group reference: group index is missing." exception in the dbx2.log are related to bug in Splunk DB Connect 2 application.

If input event field contains dollar sign it throws the exception and terminates lookup script.

The folowing discusions of the exception help me much:

https://jira.atlassian.com/browse/JRA-39676
http://stackoverflow.com/questions/11913709/why-does-replaceall-fail-with-illegal-group-reference

The workaroud is exclude spectial characters from value (for instance with regex [a-zA-Z\.]).

Hope this helps someone else.

[muebel]

Hi AlexeyNL, this looks like a java related error message. Could you add the [mi_lookup://first_lookup] stanza for the splunk_app_db_connect local inputs.conf? My first guess is that there is something related to the query that is throwing off the jdbc.

[AlexeyNL]

Thank you for quick response.

Here it is:

[mi_lookup://first_lookup]
connection = ms-sql-connection
input_fields = UserID
interval = 1800
lookupSQL = SELECT PersonnelNumber,UserID FROM dbo.TestLookupTable
output_fields = PersonnelNumber
ui_column_output_map = [{"removable":false,"label":"PersonnelNumber","value":"PersonnelNumber","name":"PersonnelNumber"}]
ui_field_column_map = [{"name":"USERNAME","selected":true,"removable":false,"label":"USERNAME","value":"USERNAME","alias":"UserID"}]
ui_input_spl_search = index=oracle_audit_trail
ui_is_auto_lookup = 0
ui_query_catalog = AM
ui_query_mode = advanced
ui_query_result_columns = [{"name":"UserID"},{"name":"PersonnelNumber"}]
ui_query_schema = dbo
ui_query_table = TestSplunkOutput
ui_use_saved_search = 0
ui_auto_lookup_conditions = [{"type":"host","value":"TEST-SQL-SERVER","removable":true,"stanza":"host::TEST-SQL-SERVER"}]