REST API: DBX: Specify time range

[mxanareckless]

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?

spk-dbx-ui1.PNGspk-dbx-ui2.PNGspk-dbx-ui3.PNG

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

[tscroggins]

@mxanareckless 

I have used constructs similar to this in the past:

| dbxquery connection="my_connection" query="SELET * FROM tbl WHERE t BETWEEN ".
[| makeresults
| addinfo
| eval t="\"'".strftime(info_min_time, "%F %T")."' AND '".strftime(info_max_time, "%F %T")."'\""
| return $t]

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

[mxanareckless]

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

[tscroggins · Accepted solution]

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)