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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...