topic Re: Splunk DB Connect 1: How to parse a dbquery search string to convert Unix timestamps to a readable format and create a timechart? in Getting Data In

Splunk DB Connect 1: How to parse a dbquery search string to convert Unix timestamps to a readable format and create a timechart?

jtracy — Thu, 09 Jun 2016 16:01:34 GMT

I have a string like this;

| dbquery MYDATABASE "Select trunc(ph.x_rqst_date) bp_date,count(ph.objid) bpcount,ph.x_ics_rcode _code,
 X_AUTH_RESPONSE paymen_code,ph.x_payment_type type
 from x_program_purch_hdr ph    
 where x_rqst_type='CREDITCARD_PURCH'
 AND ph.x_payment_type IN ('ENROLLMENT','RECURRING')
 and ph.x_rqst_date >= Trunc(sysdate)-1
 and ph.x_rqst_date < Trunc(sysdate)
 GROUP BY trunc(ph.x_rqst_date),ph.x_ics_rcode,X_AUTH_RESPONSE,ph.x_payment_type"

But I cannot parse this query with things like |timechart count by code limit=25. Am I missing something? I want to convert all the unreadable unix timestamps to readable, and make a timechart.

Re: Splunk DB Connect 1: How to parse a dbquery search string to convert Unix timestamps to a readable format and create a timechart?

justinatpnnl — Thu, 09 Jun 2016 16:21:35 GMT

Timechart relies on having a _time field for your data. If your date field is already in epoch format, just rename the column with the date to _time:

| rename bp_date as _time

Then try adding your timechart after that. The alternative would be to name the column within your query:

Select trunc(ph.x_rqst_date) _time

Re: Splunk DB Connect 1: How to parse a dbquery search string to convert Unix timestamps to a readable format and create a timechart?

jtracy — Thu, 09 Jun 2016 16:34:36 GMT

This was it! Thank you kind sir.