Splunk dbxquery与Splunk搜索合并

Question

我正在尝试将Splunk搜索查询与数据库查询结果集合并。基本上，我有一个Splunk dbxquery 1，它从数据库中返回特定用户id的用户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') "

以上查询输出

VALUE                                 EMAIL
xv67383                              [e-mail]

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

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的输出中按userid为所有匹配的行添加一个名为"来自splunk-dbxquery 1的电子邮件"的列。基本上，希望为查询2中返回的每个用户添加电子邮件作为附加字段。

到目前为止，我尝试了这个，但没有给我任何结果。如有任何帮助，我们将不胜感激。

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

在map命令中将$user替换为$user$。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'):""```