Iterate through dbxquery (SQL) results?

[lukenorthern]

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

[richgalloway]

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.

[ITWhisperer]

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

[lukenorthern]

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"

[ITWhisperer · Accepted solution]

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