DB Connect 2 - unable to run 'ORDER BY' because of automatic wrapping

[ehudb]

Hello

When I try to run query with DB Connect 2 with MSSQL, I cannot run ORDER BY in the function.

This works well:
| dbxquery connection="LDPR.UK" maxrows=10 query="select * from users" output=csv shortnames=true

However on this query I get an error:
| dbxquery connection="LDPR.UK" maxrows=10 query="select * from users order by id" output=csv shortnames=true

External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (select * from users order by id) t", caused by: AvroRemoteException(u'java.sql.SQLException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.',). "*

It happends because DB connect 2 is wapping the query with "SELECT * FROM ([my query])", which makes any ORDER BY to be invalid.
Is there any way to override this besides using |sort in splunk itself?

[ehudb · Accepted solution]

OK another RTFM to the long list:

http://docs.splunk.com/Documentation/DBX/2.2.0/DeployDBX/Troubleshooting#Database_inputs_or_lookups_...

To enable or disable query wrapping for all connections:

Add the following [default] stanza to local/db_connections.conf, setting enable_query_wrapping to 0 to disable, or to 1 to enable.
[default]
enable_query_wrapping = 0

[jcoates_splunk]

2.3.0 gives you more granular control over wrapping.

• http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/SQLtipsandtricks#Use_inline_views_.28query_...
• http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Createandmanagedatabaseinputs
• http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Troubleshooting#Database_inputs_or_lookups_...
• http://docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/Commands#dbxquery