How to imbed dbxquery in a splunk macro?

[bwyn]

I have a significant number of dashboards that use dbxquery to pull data from a significant number of servers running many nosql databases (>20) with standardized collection names(>20). I have database connections defined for each server/database combination: 

I'm currently using a simple dbxquery in search to pull data from these collections:

|dbxquery connection=$server_name$_database_name query ="SELECT * FROM collection_name"
|(numerous transformations)

This works fine. Unfortunately, there's a lot of field transformations, json processing, etc. that needs to happen after the query, and its always the standard 8-10 lines. I'd like to standardize these queries and imbed them in a macro. I'd like to bundle all of this in a macro like this:

`collection_name(server_name)`

The problem is that |dbxquery doesn't appear to like being the first command in a macro. 

Error in 'dbxquery' command: This command must be the first command of a search.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

Any ideas how to implement this macro in a clean way? 

[gcusello]

Hi @bwyn

let meunderstand: you are using dbxquety in your searches?

dbx query is usually used for test, the best approach to use dbxqueries ir to extract data from one or more databases, store them in Splunk and use there extracted data for searches.

If you use dbxquery in searches you have very low performances.

In addition if you store data extracted with dbxquery in an index or ia lookup, you can use them in each search, extractig fields and eventually grouping data before last search execution.

Ciao.

Giuseppe

[richgalloway]

You should be able to make it work by putting the leading pipe in the macro definition.  I've seen that done before.