Splunk Search

REST API: DBX: Specify time range

mxanareckless
Path Finder

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>

 

Labels (3)
Tags (3)
0 Karma
1 Solution

tscroggins
Influencer

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)

View solution in original post

tscroggins
Influencer

@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
Path Finder

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

tscroggins
Influencer

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)

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...