All Apps and Add-ons

Splunk DB Connect: How to use TO_DATE(w.END_DATE,'MM/DD/YYYY') in where condition of ORACLE query while using it in SPLUNK?

anirudhgl
New Member

I am using WHERE TO_DATE(w.END_DATE,'MM/DD/YYYY') < TRUNC (SYSDATE) in Oracle query.
It is working fine in the TOAD, but I am using the same in SPLUNK and it is resulting in 0 events.

0 Karma

niketnilay
Legend

Are you getting an Error or result count is zero? Please check date format in your table column END_DATE. Please also check if you are getting any error while running the query or by querying splunkd logs for DBX entries for checking query execution.

PS: I was able to pull the same using Oracle dummy HR table and your WHERE clause. Please find screenshots from db connection validation query and dbxquery.

| dbxquery connection=OracleConnection query="SELECT * FROM HR.EMPLOYEES E WHERE TO_DATE(E.HIRE_DATE,'DD/MM/YYYY') < TRUNC (SYSDATE)" maxrows=10  shortnames=t

alt text

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma