Archive

DB Query with app Splunk DB Connect inside of Eval Expression

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

Path Finder

Solved by just using a DB Lookup instead

0 Karma

Path Finder

Maybe a python script?

0 Karma

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

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

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

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