Splunk Search

DB Query with app Splunk DB Connect inside of Eval Expression

edschembor
Path Finder

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.

0 Karma

edschembor
Path Finder

Solved by just using a DB Lookup instead

0 Karma

edschembor
Path Finder

Maybe a python script?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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?

0 Karma

edschembor
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

edschembor
Path Finder

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", )'."

0 Karma

edschembor
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...