topic Re: DB Query with app Splunk DB Connect inside of Eval Expression in Splunk Search

DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Wed, 18 Jun 2014 15:10:36 GMT

So, I'm trying to run DB queries with the Splunk DB Connect app inside of the eval "case" function. So, something like this:

index=eph | rex "(?P<Type>PaymentInstruction)"| rex "(?P<Type>OPIC)" | rex "(?P<EPHID>EPH\d+)"
| eval EPHID = case( isnull(Type), EPHID, Type == "PaymentInstruction", dbquery "mysql" "SELECT DISPLAYTRANSACTIONID FROM XXXX WHERE paymentinstructionkey = 'EntityKey'", Type == "OPIC", dbquery "mysql" "SELECT DISPLAYTRANSACTIONID FROM XXXX WHERE opickey='EntityKey'") 
| table EPHID Type _raw

So if the event has a Type, I want to query the database to get its EPHID and plug it into the table to completely fill the table. However, I keep getting the following issue:

"Error in 'eval' command: The expression is malformed. Expected )."

Any ideas?? Thanks!!!

EDIT:

Note, this search works properly:

index=eph sourcetype=websphere_trlog_sysout AND ("*EPH14*" OR "*Entitykey*") AND (`EPH-HPOV-Keyword` OR EPH-alerts) | rex 
"EDT] (?P<TaskID>\w{8})" | where TaskID != "" | rex "Entity Key: (?P<EntityKey>\d+)" | rex "entityKey=(?P<EntityKey>\d+.\d+.\d+)" 
| rex "EntityKey:(?P<EntityKey>\d+)" | rex "(?P<EPHID>EPH\d+)" | rex "(?P<Type>PaymentInstruction)"| rex "(?P<Type>OPIC)" |convert ctime(_time) as timestamp 
| eval EPHID = case( isnull(Type), EPHID, Type == "PaymentInstruction", "PI", Type = "OPIC", "OP")
| table EntityKey EPHID TaskID Type timestamp _raw

So I don't think that passing fields is the issue. It must be something with the DB query clause.

Re: DB Query with app Splunk DB Connect inside of Eval Expression

somesoni2 — Wed, 18 Jun 2014 15:57:10 GMT

Try this

index=eph | rex "(?P<Type>PaymentInstruction)"| rex "(?P<Type>OPIC)" | rex "(?P<EPHID>EPH\d+)"
| eval EPHID = case( isnull(Type), EPHID, Type == "PaymentInstruction", [|dbquery "mysql" "SELECT DISPLAYTRANSACTIONID FROM XXXX WHERE paymentinstructionkey = 'EntityKey'" | eval DISPLAYTRANSACTIONID="\"".DISPLAYTRANSACTIONID."\"" | return $DISPLAYTRANSACTIONID ], Type == "OPIC", [| dbquery "mysql" "SELECT DISPLAYTRANSACTIONID FROM XXXX WHERE opickey='EntityKey'" | eval DISPLAYTRANSACTIONID="\"".DISPLAYTRANSACTIONID."\"" | return $DISPLAYTRANSACTIONID ]) 
| table EPHID Type _raw

One question though, the term 'EntityKey' is a literal string or its a field in the main result?

Re: DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Wed, 18 Jun 2014 16:00:50 GMT

Entity key is a field. I have a regex: " rex "Entity Key: (?P\d+)"" but took it out to make the question shorter

Re: DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Wed, 18 Jun 2014 17:01:33 GMT

I did the updated method with the macro and still am getting the issue:
"Error in 'eval' command: The expression is malformed. An unexpected character is reached at ', "Type"== "OPIC", )'."

Re: DB Query with app Splunk DB Connect inside of Eval Expression

somesoni2 — Wed, 18 Jun 2014 17:48:39 GMT

I guess its not possible. The macro workaround is not working as I expected.

Re: DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Wed, 18 Jun 2014 18:20:58 GMT

Is there possibly a way to do it with a script?

Re: DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Thu, 19 Jun 2014 12:17:06 GMT

Maybe a python script?

Re: DB Query with app Splunk DB Connect inside of Eval Expression

edschembor — Wed, 25 Jun 2014 18:46:24 GMT

Solved by just using a DB Lookup instead