Use db results in splunk search

[dfredell]

Hello, I want to use the results of my dbquery as search strings in the log files.

Currently I am running two different Splunk searches then copy find/replace and pasting the result from 1 to 2.
1:
|dbquery JiraDB "SELECT TOP 1000 [ID] FROM [jiraDB].[JIRA].[searchrequest] where [USERNAME] = 'dfredell'"

2:
sourcetype=access_log "14196" OR "17516" OR "17525" OR .....

The output from 1 has 300 items and looks like:

ID

---

14196

17516

17525

...

The data in access_log is a standard Apache Web Log and I am searching for the url to contain any of the ID values.

[martin_mueller · Accepted solution]

Try this:

sourcetype=access_log [dbquery JiraDB "SELECT TOP 1000 [ID] FROM [jiraDB].[JIRA].[searchrequest] where [USERNAME] = 'dfredell'" | return 1000 $ID]

That will turn the results from the dbquery into an OR'd list of your ID values and use it to filter the access_log search.

[martin_mueller]

Without that $ it'll generate a filter like this:

( ( ID=value1 ) OR ( ID=value2 ) OR ... )

...which fails because you're looking for strings appearing somewhere in your events rather than values of a field called ID.

[dfredell]

Wow that was perfect thank you! I had tried that before, I guess all I was missing was the '$' on ID.