topic Re: Use db results in splunk search in Splunk Search

Use db results in splunk search

dfredell — Wed, 16 Apr 2014 15:28:57 GMT

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.

Re: Use db results in splunk search

martin_mueller — Wed, 16 Apr 2014 17:00:31 GMT

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.

Re: Use db results in splunk search

dfredell — Wed, 16 Apr 2014 18:10:17 GMT

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

Re: Use db results in splunk search

martin_mueller — Wed, 16 Apr 2014 18:22:37 GMT

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.