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 [email protected]
Another query is a Splunk query 2 that provides the user ids as follows:
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
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,
