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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...