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!
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")
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")
Yes! Perfect
Thank you for the help. Crazy how I over thought it.
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).
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!!
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.
Let's fix your original search; it will be way easier. Add a comment with the COMPLETE search for the fieldformat
method.