Archive

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

Explorer

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 tochar(max(I.CREATEDDATE), '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.SCHDLDFLLWUPDATE BETWEEN TODATE('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!

0 Karma
1 Solution

Esteemed Legend

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")

View solution in original post

0 Karma

Esteemed Legend

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")

View solution in original post

0 Karma

Explorer

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

0 Karma

Esteemed Legend

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).

0 Karma

Explorer

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!!

0 Karma

Explorer

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.

0 Karma

Esteemed Legend

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

0 Karma