Merge Splunk dbxquery with search

kubeshabby — Thu, 16 Dec 2021 03:19:58 GMT

I am trying to merge Splunk search query with a database query result set. Basically I have a Splunk dbxquery 1 which returns userid and email from database as follows for a particualr user id:

| dbxquery connection="CMDB009" query="SELECT dra.value, z.email FROM DRES_PRINTABLE z, DRES.CREDENTIAL bc, DRES.CRATTR dra WHERE z.userid = bc.drid AND z.drid = dra.dredid AND dra.value in ('xy67383') "

Above query outputs

VALUE EMAIL

xv67383 xyz@test.com

Another query is a Splunk query 2 that provides the user ids as follows:

This above query 2 returns a column called user but not email.

What I want to do is add a column called email from splunk dbxquery 1 for all matching rows by userid in output of query 1. Basically want to add email as additional field for each user returned in query 2.

What I tried so far is this but it does not give me any results. Any help would be appreciated.

index=index1 (host=xyz OR host=ABC) earliest=-20m@m | rex field=_raw "samlToken\=(?>user>.+?):" | join type=outer usetime=true earlier=true username,host,user [search index=index1 source="/logs/occurences.log" SERVER_SERVER_CONNECT NOT AMP earliest=@w0 | rex field=_raw "Origusername$(?>username>.+?)$" | rex field=username"^(?<user>,+?)\:" | rename _time as epoch1] | "stats count by user | sort -count | table user | map search="| | dbxquery connection=\"CMDB009\" query=\"SELECT dra.value, z.email FROM DRES_PRINTABLE z, DRES.CREDENTIAL bc, DRES.CRATTR dra WHERE z.userid = bc.drid AND z.drid = dra.dredid AND dra.value in ('$user'):\""

Thanks,

topic Merge Splunk dbxquery with search in Splunk Search

Merge Splunk dbxquery with search