Convert query results to line chart

[karthi25]

I have an oracle query as follows:

|dbxquery query="select trunc(start_dt) START_DT, count(0) ERR_CNT , 'WORKFLOW' as SOURCE from ***.WORKFLOW_LOG wl where WL.STATUS not in ('COMPLETED', 'INPROGRESS') and start_dt > SYSDATE - 30 group by trunc(start_dt) union select trunc(create_dt) START_DT, count(0) ERR_CNT , 'ESP' as SOURCE from ***.event_error_records eer where create_dt > SYSDATE - 30 group by trunc(create_dt)" connection="ABC_DADPP1"

And, the above query will produces the result below:

Now, I want to format the date and to create the the chart as below:

Can anyone please help to do it.

[mayurr98 · Accepted solution]

you can try something like this

| dbxquery query="select trunc(start_dt) START_DT, count(0) ERR_CNT , 'WORKFLOW' as SOURCE from ***.WORKFLOW_LOG wl where WL.STATUS not in ('COMPLETED', 'INPROGRESS') and start_dt > SYSDATE - 30 group by trunc(start_dt) union select trunc(create_dt) START_DT, count(0) ERR_CNT , 'ESP' as SOURCE from ***.event_error_records eer where create_dt > SYSDATE - 30 group by trunc(create_dt)" connection="ABC_DADPP1" 
| eval newtime=strptime(START_DT,"%Y-%m-%d %H:%M:%S") | eval _time=newtime
| timechart span=2d sum(ERR_CNT) by SOURCE

OR try this which is specific to your requirement

| dbxquery query="select trunc(start_dt) START_DT, count(0) ERR_CNT , 'WORKFLOW' as SOURCE from ***.WORKFLOW_LOG wl where WL.STATUS not in ('COMPLETED', 'INPROGRESS') and start_dt > SYSDATE - 30 group by trunc(start_dt) union select trunc(create_dt) START_DT, count(0) ERR_CNT , 'ESP' as SOURCE from ***.event_error_records eer where create_dt > SYSDATE - 30 group by trunc(create_dt)" connection="ABC_DADPP1" 
| eval newtime=strptime(START_DT,"%Y-%m-%d %H:%M:%S") | eval _time=newtime
| timechart span=2d sum(ERR_CNT) by SOURCE 
| rename _time as time 
| eval time=strftime(time,"%m-%d")

let me know if this helps!

[FrankVl]

Can you show what the output of the query looks like? Without that info it is rather hard to say what commands to use to transform it into something that can be visualized as a line chart.

[karthi25]

I have edited it.. 🙂