topic REST API: DBX: Specify time range in Splunk Search

REST API: DBX: Specify time range

mxanareckless — Fri, 05 Feb 2021 20:52:49 GMT

Need to run a dbxquery command via the REST API, and having trouble defining the search's time range in that context. Below I demonstrate how the queries appear in the web UI, versus the commandline with curl.

Either the query is invalid because | dbxquery needs to be at the beginning of the query, or no results are returned when appending | search earliest=-1day latest=now to the end of the query.

How can I correctly specify a time range when using | dbxquery via REST API?

[root@host ~]# curl -u user:password -k https://192.168.xx.xxx:xxxx/services/search/jobs/export --data-urlencode search='seary connection="xxx" query="SELECT (SELECT sum(bytes) FROM dba_data_files)+ (SELECT sum(bytes) FROM dba_temp_files)- (SELECT sum(bytes) FROM dba_ <?xml version='1.0' encoding='UTF-8'?> <response><messages><msg type="FATAL">Error in 'dbxquery' command: This command must be the first command of a search.</msg></messages></response> [root@host ~]# curl -u user:password -k https://192.168.xx.xxx:xxxx/services/search/jobs/export --data-urlencode search='| dT (SELECT sum(bytes) FROM dba_data_files)+ (SELECT sum(bytes) FROM dba_temp_files)- (SELECT sum(bytes) FROM dba_free_space) total_size FROM dual; <?xml version='1.0' encoding='UTF-8'?> <results preview='0'> <meta> <fieldOrder> <field>TOTAL_SIZE</field> </fieldOrder> </meta> <messages> <msg type="DEBUG">Configuration initialization for /opt/splunk/etc took 15ms when dispatching a search (search ID: 1612554968.128577)</msg> <msg type="DEBUG">The 'dbxquery' command is implemented as an external script and may cause the search to be significantly slower.</msg> <msg type="DEBUG">search context: user="reporting", app="search", bs-pathname="/opt/splunk/etc"</msg> </messages> </results>

Re: REST API: DBX: Specify time range

tscroggins — Sun, 21 Feb 2021 05:37:48 GMT

@mxanareckless

I have used constructs similar to this in the past:

You'll want to modify appropriately with TO_DATE, TO_TIMESTAMP, etc. for Oracle.

Re: REST API: DBX: Specify time range

mxanareckless — Mon, 22 Feb 2021 18:22:53 GMT

@tscroggins

Thank you very much for your response. How can I integrate this solution with my current query? I am not well versed in Oracle DB and am only providing Splunk queries for that team.

| dbxquery connection="helps" query="select (select sum(bytes) from dba_data_files)+(select sum(bytes) from dba_temp_files)-(select sum(bytes) from dba_free_space) total_size from dual;"

Re: REST API: DBX: Specify time range

tscroggins — Tue, 23 Feb 2021 03:37:39 GMT

The DBA_DATA_FILES, DBA_TEMP_FILES, and DBA_FREE_SPACE views do not contain date columns.

If your Oracle DBAs want to track changes to those views over time, a more appropriate solution would be e.g. a daily DB Connect input with the timestamp set to the current time in the input configuration.

After the input is in place, you can just search the data in Splunk:

index=foo sourcetype=bar
| timechart span=1d values(total_size)