topic Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found in Getting Data In

Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

g038123 — Tue, 29 Sep 2020 10:09:41 GMT

Hello and thank you in advance for any help .

I am creating a dashboard for our customer service team, using a dbquery to return the records they require for troubleshooting purposes.
I have everything working just fine, however, when I try to format the date fields returned from the query using to_char or to_date functions, I get no results found. As an example, here is one the fields I am returning;

max(I.CREATED_DATE) if I update it to to_char(max(I.CREATED_DATE), 'MM/DD/YYY') I go from good results to no results found.

I'd also like to give the customer service team the option of inputting two dates, so they can filter the data by a specified range. If I add;

AND sfu.SCHDLD_FLLW_UP_DATE BETWEEN TO_DATE('07/11/2016','MM/DD/YYYY') AND TO_DATE('08/25/2016', 'MM/DD/YYYY')

to the query I get no results. Everything works like a charm in TOAD, just seems to be something Splunk doesn't like. I've tried several different options with case but nothing seems to work.

What am I missing here? Please help and thank you!

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

woodcock — Fri, 08 Jul 2016 20:37:32 GMT

You are asking an SQL question in the wrong place. The Splunk answer is to do the formatting inside of Splunk with your original working search like this:

... | fieldformat CREATED_DATE = strftime(CREATED_DATE, "%m/%d/%Y")

The same thing for the 2 dates, have them pick the dates from a timepicker tool which will return a time_t (epoch) value which will not need to be converted (or use the same trick above, to convert it in splunk before passing it to SQL).

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

g038123 — Tue, 12 Jul 2016 13:45:14 GMT

I did initially try to use the fieldformat command, however it doesn't seem to be working. I don't get any results for that field when adding the command to my search.

... | fieldformat CREATED_DATE = strftime(CREATED_DATE, "%m/%d/%Y")

That's why, after reading a few other posts, I attempted to use the SQL functions to format the dates but ran into trouble there as well.
Sorry, I'm pretty new to Splunk so if I'm missing the obvious, I apologize.

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

woodcock — Tue, 12 Jul 2016 16:39:05 GMT

Let's fix your original search; it will be way easier. Add a comment with the COMPLETE search for the fieldformat method.

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

g038123 — Thu, 14 Jul 2016 20:54:18 GMT

Ok, here is the complete search:

| dbxquery query="SELECT P.PARTY_ID,P.LAST_NAME || ',' || P.FIRST_NAME,PMST.PATIENT_MNTRNG_STATUS_DESC,cy.ctry,M.BASE_MODEL_NBR || '_' || D.SERIAL_NBR, min(SFU.SCHDLD_FLLW_UP_DATE),max(I.CREATED_DATE)
    FROM   PATIENT_MNTRNG_STATUS pms,PATIENT_MNTRNG_STATUS_TYPE pmst,
           device d,assgnd_device ad,
           patient_clinic pc,ctry cy,
           contact c,REVIEW_SUMMARY rs,
           PATIENT_FLLWNG pf,MODEL m,
           PARTY p,EXT_ACCT ea,APM.INTRGTN i,
           SCHDLD_FLLW_UP sfu
   WHERE   pms.PATIENT_MNTRNG_STATUS_TYPE_ID =pmst.PATIENT_MNTRNG_STATUS_TYPE_ID
           AND PMS.PARTY_ID = PC.PATIENT_PARTY_ID
           AND PC.PATIENT_PARTY_ID = AD.PARTY_ID
           AND AD.PARTY_ID = P.PARTY_ID
           AND AD.PARTY_ID = I.PARTY_ID(+)
           AND PC.CLINIC_PARTY_ID = C.PARTY_ID
           AND C.PARTY_ID = EA.PARTY_ID
           AND PC.PATIENT_CLINIC_ID = RS.PATIENT_CLINIC_ID
           AND RS.PATIENT_FLLWNG_TYPE_CD = 'DVCE'
           AND RS.REVIEW_SUMMARY_ID = PF.REVIEW_SUMMARY_ID
           AND C.CTRY_ID = CY.CTRY_ID
           AND D.MODEL_TYPE_CD = 'PG'
           AND D.MODEL_ID = M.MODEL_ID
           AND D.USED_FLAG = 'Y'
           AND AD.DEVICE_ID = D.DEVICE_ID
           AND EA.EXT_ACCT = 'SAP_ID'
           AND EA.EXT_ACCT_TYPE_CD = 'SAP'
           AND SFU.PATIENT_FLLWNG_ID = PF.PATIENT_FLLWNG_ID
           AND SFU.INTRGTN_ID is NULL
           AND SFU.CANCELLED_DATE is NULL
           AND AD.END_DATE IS NULL
Group by P.PARTY_ID,P.LAST_NAME,P.FIRST_NAME,PMST.PATIENT_MNTRNG_STATUS_DESC,cy.ctry,M.BASE_MODEL_NBR,D.SERIAL_NBR" connection=US_NXT_STDBY shortnames=t
| stats list("P LAST_NAME||','||P   FIRST_NAME") as NAME
        list(PATIENT_MNTRNG_STATUS_DESC) as Monitoring_Status
        list("M BASE_MODEL_NBR||'_'||D  SERIAL_NBR") as PG
        list(MIN(SFU    SCHDLD_FLLW_UP_DATE)) as Next_Remote_Follow_Up
        list(MAX(I  CREATED_DATE)) AS CREATED_DATE by PARTY_ID
| sort NAME
|  fieldformat CREATED_DATE = strftime(CREATED_DATE, "%m/%d/%Y")

This is the returned format for the CREATED_DATE without the fieldformat:

**2016-07-11 08:53:44.588**

When adding:

| fieldformat CREATED_DATE = strftime(CREATED_DATE, "%m/%d/%Y")

Then I get no results in the CREATED_DATE field.

Let me know if you need anything else and thank you for the help!!

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

woodcock — Fri, 15 Jul 2016 22:25:29 GMT

It appears that CREATED_DATE is not a time_t (epoch) but a string. Therefore, instead of this:

| fieldformat CREATED_DATE = strftime(CREATED_DATE, "%m/%d/%Y")

Use this:

| fieldformat CREATED_DATE = strftime(strptime(CREATED_DATE, "%Y-%m-%d %H:%M:%S.%3N"), "%m/%d/%Y")

Re: Splunk DB Connect 1: Using to_char and to_date functions with dbquery results in no results found

g038123 — Mon, 18 Jul 2016 16:14:15 GMT

Yes! Perfect
Thank you for the help. Crazy how I over thought it.