Splunk DB Connect: How to use a macro with dbxquery?

[burwell]

I am trying to use a macro with dbxquery.

1) I have one macro, called macro1 that returns something like the following using strftime. It's an eval macro. That part works fine.

"Job='201611251010' OR job='201611251020'"

2) I want to use macro1 in another macro, macro2, for use with dbxquery

"dbxquery connection=mydb query=\"select * from mytable where field1 like 'foo%' AND (".`macro1`.") limit 1\""

And then use by

|`macro2`

I always get that macro2 is expected to return a string.

[vasanthmss · Accepted solution]

Use sub query to build your query string, there you can use any macros.

something like this...

|dbxquery connection=mydb [|stats c | eval query="<build your query with macro / anything..>" | return query] 

Hope this will helps you!!!!

[burwell]

Hello. I got this to work. Thanks!

The trick for me was that I could NOT put together a string using "string1".macro1."string"
Instead I made a string with a dummy value (xxxx) and used replace.

Here's what I did:

1) macro1 IS eval based (because I use strftime )

definition="eval job=\""."<my random string etc>"

2) macro2 is NOT eval based

definition="`macro1` | eval query="select * from table WHERE xxxx" | eval query=replace(query,"xxxx",job) "

Then to use:

|dbxquery connection=mydb  [| stats c| `macro2` |return query] | stats count