How can I run a dbxquery to find results between two dates?

[1240062]

From dbx query database ,I want the results that are in between certain case due dates. Can u please help me? Thanks in advance

[Richfez]

This is a SQL question, and I'll explain why (and possibly give some tips on getting it done while doing so.)

When you run a DBX query, you are using Splunk to send your DBMS a query it will run, then return the results to Splunk.

So searching between a particular set of dates in a DBX query is "after the fact" in that you use dbxquery to generate results that you then filter, instead of using a search to build a dbxquery that can filter. Do you see the difference? In the former, you can't pass in information but instead use the query as the end-all and be-all of your data. In the latter, you could (but can't) use a query that involves having substituted variables and things.

In either of those cases, the SQL has to contain the dates you want, not the Splunk side of things. And this is where the problem is.

If instead, though, you are using the dbconnect app with a DBX input, well, then you are ingesting the data. The ingestion side of this has a mechanism for date ranges but they're not interactive like you want. Fortunately, you can just ingest all the data with date/time stamps and use simple splunk mechanisms to select which you want, like "stats last(fieldname) by host" and the such.

If you could provide a bit more information about exactly what it is you are doing, and how specifically you are doing it, this may help us quite a bit.

[1240062]

Hi Rich,
Thanks for your information. I need to get clarification a bit more.

| dbxquery connection=mysql query="SELECT * FROM sakila.city"

Let'say this is a command that used to fetch all the records from sakila.city. In that I have a column called "checkInDate". I need the records that are in between the dates from nov4th nov11th only.
I just changed the checkInDate into epoch time and I compared with the require dates. I am not getting the results. I could not able to filter those.

Can you please help me out from this?

[Richfez]

So, a couple of things.

You are using dbxquery - which is fine, but let's make sure you are clear with the difference between using dbxquery and using a dbx input.

dbxquery runs SQL commands over a connection. It does retrieve those records for storing in Splunk, but instead just run that query. A dbx input will do a similar thing, except at the end it stores the results in Splunk for you to use elsewhere.

There's a reason to mention this that's possibly not obvious but should be important to your needs - a dbxquery uses whatever syntax the SQL side of things use. Period. Nothing special, no "Splunkisms" in that. Just SQL of whatever flavor your SQL box uses.

A dbx input uses almost entirely SQL syntax with a few minor changes to let Splunk "interact" with it for rising column purposes. But most importantly since the output of the dbinput ends up in an index where it's regularly searchable, you have a lot more options on how you can filter by dates when you do it this way; for instance it's trivial to get a date range by just using the time picker (assuming your dates are being ingested properly and whatnot).

All right, that's probably been covered fully enough. 🙂

In your example (which is from the docs, I think), you'll have to use whatever syntax your RDBMS uses to filter by date. This is literally exactly what you would type into DB query utility (MS SSMS, whatever you use to run regular queries against your DB).

In MSSQL, let's suppose we have a CheckInDate that's formatted like '2017-01-09 14:27:13.0'. So when using dbxquery to filter by date against a table stored in MSSQL you may end up with a search string like

| dbxquery connection=mysql query="SELECT * FROM sakila.city WHERE CheckInDate>'2017-06-01'"

to get rows after June of 2017. Likewise, to limit it to a range,

| dbxquery connection=mysql query="SELECT * FROM sakila.city WHERE CheckInDate>='2017-06-01' AND CheckInDate<'2017-01-01'"

That's for the month of June of the year 2017.

The point to remember here is that this syntax is SQL syntax - whatever RDBMS you are using and whatever column definition you have set up on the field you want to return/query/search/filter defines how you have to ask for those dates.

So, in your example the checkInDate is likely stored in some date/time format suitable for your own RDBMS, not in epoch. Well, maybe it is in epoch, you'll have to look up the column definition to confirm. And the syntax for filtering by date comes from your SQL syntax too, so if it's MSSQL it'll do it one way, if it's MySQL it may do it differently. This will be documented in your RDBMS' documentation.

I hope this helps!

Happy Splunking,
Rich

[1240062]

Thank you soo much......

But getting error like "⚠ java.sql.SQLDataException: ORA-01861: literal does not match format string"

[Richfez]

That's java (which runs the SQL layer for Splunk) telling you that something's mismatched - when it sends a date to your RDBMS, the RDBMS returns with "Whoa, what's this? It doesn't match what I expect to see as a date."

If you search for that exception (ORA-01861) you'll find lots of help on what format Oracle expects. For basic usage, date formats are often the hardest to get right and differ the most between competing products. I honestly can't answer how Oracle does it because I don't use Oracle DB products, so you'll have to ask in an Oracle forum if you need specifics, or just look at what that error returns elsewhere on the web and see if you can figure out that part.

For what it's worth, we may be able to help if you can paste in the offending SQL syntax with the corresponding versions of Oracle and what's returned, but it'll be best effort help only - this is an issue with Oracle asking for dates in a particular way. Oracle people will know this better.