Splunk dbxquery与splunk搜索合并

Question

我正在尝试将Splunk搜索查询与数据库查询结果集合并。基本上，我有一个Splunk 1，它从数据库中返回userid和email，如下所示：

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

以上查询输出

VALUE                                 EMAIL

xv67383                              xyz@test.com

另一个查询是Splunk查询2，它提供用户ids，如下所示：

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

上面的查询2返回一个名为user的列，但不返回电子邮件。

我想要做的是在查询1的输出中为所有匹配的行添加一个名为1的列，基本上是要为查询2中返回的每个用户添加email作为附加字段。

到目前为止，我尝试的是这一点，但它并没有给我任何结果。任何帮助都将不胜感激。

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'):\""

Answer 1

在$user命令中用$user$替换map。Splunk在令牌的两端都使用$。

username字段在查询结束时不可用，因为stats命令去掉了它。stats之后唯一可用的字段是命令中提到的字段(在本例中为user和count )。要使username字段可用，请将其添加到stats命令中。然而，这可能会改变你的结果。

| 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, username | sort -count 
| table user, username 
| 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'):\""```