DBQUERY correlation -- How to join results from DB

[shangshin]

Hi,
I get the user_id info from web log and would like to enrich data from the connected DB in Splunk. I tried the search query below but it didn't work. Can anyone provide the right syntax?

index=web_access_log | table user_id | RENAME user_id AS web_user_id [ search dbquery USER_SQL_DB "select web_user_id, user_type, user_email, user_phone from user_profile_view_all where web_user_id=$web_user_id$ "]

[martin_mueller · Accepted solution]

Define a database lookup: http://docs.splunk.com/Documentation/DBX/1.1.4/DeployDBX/Setupadatabaselookuptable

[somesoni2]

Try this (using map command)

index=web_access_log | table user_id | map maxsearches=100 search="| dbquery USER_SQL_DB \"select web_user_id, user_type, user_email, user_phone from user_profile_view_all where web_user_id=$user_id$ \""

[martin_mueller]

You can use any number of field names you like.

Did you look at database lookups? It seems to me as if you're reimplementing those.

[shangshin]

It's working after adding the single quote for the variable. Is it possible to get more than one field value from the first query in the final result e.g. url?

e.g.

index=web_access_log | table user_id, url | map maxsearches=100 search="| dbquery USER_SQL_DB \"select web_user_id, user_type, user_email, user_phone from user_profile_view_all where web_user_id='$user_id$' \""

[shangshin]

Thanks for the response. I gave it a try and I can see s few events returned from the first query but no result is returned in the end.
I suspect the syntax needs to be polished up.....