topic Re: Iterate through dbxquery (SQL) results in Splunk Search

Iterate through dbxquery (SQL) results?

lukenorthern — Thu, 18 Aug 2022 14:07:39 GMT

Hello

I have a search which is gathering 8 columns from a table. (below)

I want to make col1 available to query against later in the SPL. I tried to access via "rename query.col1 as col1" for example but the data does not seem to appear, almost as if query.col1 is not valid? Can't find any info on how to remedy this elsewhere on the site, apologies if this has been asked before. The query returns 1 row.

| dbxquery query="Select col1,col2,col3,col4,col5,col6,col7,col8 from JuiceTable where col1 = 'special value'" connection="Juice-Prod" | stats count as total | eval Status=case(total=0,"Healthy",total > 0, "Critical")

Re: Iterate through dbxquery (SQL) results

ITWhisperer — Thu, 18 Aug 2022 13:43:21 GMT

The stats command will reduce the event pipeline to a single event with one field (total). If you want more fields, you will have to modify the stats command

| stats count as total by col1

However, this will give you an event (row) for each value of col1, not the total number of events.

Alternatively, you could filter the events before the stats command

| where col1="some value" | stats count as total

Re: Iterate through dbxquery (SQL) results

lukenorthern — Thu, 18 Aug 2022 13:50:42 GMT

Thanks for taking the time to respond @ITWhisperer !

Great, and if I wanted to take that col1 value and say, include it in an evaluated field called description, how would I compile that?

e.g. below just produces col1 as plain text, rather than the actual value from the query

| eval Description="col1"." - "."This alert monitors Juice"

Re: Iterate through dbxquery (SQL) results

ITWhisperer — Thu, 18 Aug 2022 13:53:59 GMT

| eval Description=col1." - This alert monitors Juice"

If you real col1 contains special characters, then wrap it in single (not double) quotes

| eval Description='col1'." - This alert monitors Juice"

Re: Iterate through dbxquery (SQL) results

richgalloway — Thu, 18 Aug 2022 13:54:24 GMT

The field names are the same as the column names. No rename is needed. In this example, it's just "col1".

"query" is the name of an argument to the dbxquery command. It's not an object.